| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with nested if/or statements
Hi,
I'm trying to construct a formula that does the following =IF(AND(OR(ISTEXT(Sheet3!$A$28),ISNUMBER(Sheet3!$A $28))),OR(ISTEXT(Sheet3!A$30),ISNUMBER(Sheet3!A$30 ),Sheet3!C30,Sheet3!C28),Sheet3!C25) In plain talk, if there is text or numbers in A28, check if there is text or numbers in A30, if no in the first IF use c25, if no in the second arguement use c28, if yes for both use c30 Any ideas? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with nested if/or statements
What is your definition of not text and not numbers?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nick" wrote in message ... Hi, I'm trying to construct a formula that does the following =IF(AND(OR(ISTEXT(Sheet3!$A$28),ISNUMBER(Sheet3!$A $28))),OR(ISTEXT(Sheet3!A$30),ISNUMBER(Sheet3!A$30 ),Sheet3!C30,Sheet3!C28),Sheet3!C25) In plain talk, if there is text or numbers in A28, check if there is text or numbers in A30, if no in the first IF use c25, if no in the second arguement use c28, if yes for both use c30 Any ideas? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with nested if/or statements
Bob,
Thanks for replying, if it's not text and not numbers, the cell is blank "Bob Phillips" wrote: What is your definition of not text and not numbers? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nick" wrote in message ... Hi, I'm trying to construct a formula that does the following =IF(AND(OR(ISTEXT(Sheet3!$A$28),ISNUMBER(Sheet3!$A $28))),OR(ISTEXT(Sheet3!A$30),ISNUMBER(Sheet3!A$30 ),Sheet3!C30,Sheet3!C28),Sheet3!C25) In plain talk, if there is text or numbers in A28, check if there is text or numbers in A30, if no in the first IF use c25, if no in the second arguement use c28, if yes for both use c30 Any ideas? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with nested if/or statements
Maybe you could try:
=CHOOSE(ISBLANK(Sheet3!A28)+ISBLANK(Sheet3!A30)*2+ 1,Sheet3!C30,Sheet3!C25,Sheet3!C28,"") Since you didn't say, I used "" if both A28 and A30 are empty. And I assume when you said "if the cell does not have text or numbers, it is empty" you meant it is truly empty and not the null string "". "Nick" wrote: Hi, I'm trying to construct a formula that does the following =IF(AND(OR(ISTEXT(Sheet3!$A$28),ISNUMBER(Sheet3!$A $28))),OR(ISTEXT(Sheet3!A$30),ISNUMBER(Sheet3!A$30 ),Sheet3!C30,Sheet3!C28),Sheet3!C25) In plain talk, if there is text or numbers in A28, check if there is text or numbers in A30, if no in the first IF use c25, if no in the second arguement use c28, if yes for both use c30 Any ideas? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with nested if/or statements
So test for blank
=IF(AND(A28<"",A30<""),C30,IF(A28<"",C25,C28)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nick" wrote in message ... Bob, Thanks for replying, if it's not text and not numbers, the cell is blank "Bob Phillips" wrote: What is your definition of not text and not numbers? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nick" wrote in message ... Hi, I'm trying to construct a formula that does the following =IF(AND(OR(ISTEXT(Sheet3!$A$28),ISNUMBER(Sheet3!$A $28))),OR(ISTEXT(Sheet3!A$30),ISNUMBER(Sheet3!A$30 ),Sheet3!C30,Sheet3!C28),Sheet3!C25) In plain talk, if there is text or numbers in A28, check if there is text or numbers in A30, if no in the first IF use c25, if no in the second arguement use c28, if yes for both use c30 Any ideas? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with nested if/or statements
Bob,
Thanks for replying. That formula returned zero, even though information was in C28 in sheet3!. I altered the formula to include the link to the other sheet =IF(AND(Sheet3!A28<"",Sheet3!A30<""),Sheet3!C30, IF(Sheet3!A28<"",Sheet3!C25,Sheet3!C28)) Maybe I should rephrase what I'm looking for. I am copying information from a website into sheet 3, Paste Special - Text. 90% of the time it fits one format (2 capture lines), but the 10% of the time is split between 1 capture line and 3 capture lines. I chose to look at Sheet3!A30 because it is blank (nothing in the cell) for the 90% of the time, but it is filled with 3 capture lines. Both A30 and A28 are blank when there is only 1 capture line. So I need this formula to check A30, if that's blank check A28, if that's blank pull information from C25. If A30 isn't blank, pull information from C30, If A30 is blank but A28 is not blank, pull information from C28. Thanks in advance! "Bob Phillips" wrote: So test for blank =IF(AND(A28<"",A30<""),C30,IF(A28<"",C25,C28)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nick" wrote in message ... Bob, Thanks for replying, if it's not text and not numbers, the cell is blank "Bob Phillips" wrote: What is your definition of not text and not numbers? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nick" wrote in message ... Hi, I'm trying to construct a formula that does the following =IF(AND(OR(ISTEXT(Sheet3!$A$28),ISNUMBER(Sheet3!$A $28))),OR(ISTEXT(Sheet3!A$30),ISNUMBER(Sheet3!A$30 ),Sheet3!C30,Sheet3!C28),Sheet3!C25) In plain talk, if there is text or numbers in A28, check if there is text or numbers in A30, if no in the first IF use c25, if no in the second arguement use c28, if yes for both use c30 Any ideas? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with nested if/or statements
JMB,
Thanks for your reply. Your formula didn't work exactly as intended. I plugged it in, and when the information on Sheet3! changed formats (1,2 or 3 capture lines.. I explain exactly what I mean in this morning's reply to Bob, above) the reference cell (Sheet3!A28, Sheet3!A30) kept changing! I put $ in front of the A and 28, but it kept changing where to look for blanks! Again, thank you for your advice, I look forward to hearing more! For a detailed rephrasing of my situation, look at this morning's reply to Bob in this thread. Regards, "JMB" wrote: Maybe you could try: =CHOOSE(ISBLANK(Sheet3!A28)+ISBLANK(Sheet3!A30)*2+ 1,Sheet3!C30,Sheet3!C25,Sheet3!C28,"") Since you didn't say, I used "" if both A28 and A30 are empty. And I assume when you said "if the cell does not have text or numbers, it is empty" you meant it is truly empty and not the null string "". "Nick" wrote: Hi, I'm trying to construct a formula that does the following =IF(AND(OR(ISTEXT(Sheet3!$A$28),ISNUMBER(Sheet3!$A $28))),OR(ISTEXT(Sheet3!A$30),ISNUMBER(Sheet3!A$30 ),Sheet3!C30,Sheet3!C28),Sheet3!C25) In plain talk, if there is text or numbers in A28, check if there is text or numbers in A30, if no in the first IF use c25, if no in the second arguement use c28, if yes for both use c30 Any ideas? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with nested if/or statements
We can also look at a different set of reference cells.
With 3 capture lines: C10 has data, C8 has data and C6 has data With 2 capture lines: C10 no data, C8 has data and C6 has data With 1 capture line: C10 no data, C8 no data, C6 has data We're changing the reference cells because A30 was full if I had more than one datum somewhere else (yay, mission creep). To re-iterate the result cells desired: With 3 cap lines, pull info from C30, with 2 cap lines pull info from C28, with one cap line pull info from C26. Thanks in advance! Nick "Bob Phillips" wrote: So test for blank =IF(AND(A28<"",A30<""),C30,IF(A28<"",C25,C28)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nick" wrote in message ... Bob, Thanks for replying, if it's not text and not numbers, the cell is blank "Bob Phillips" wrote: What is your definition of not text and not numbers? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nick" wrote in message ... Hi, I'm trying to construct a formula that does the following =IF(AND(OR(ISTEXT(Sheet3!$A$28),ISNUMBER(Sheet3!$A $28))),OR(ISTEXT(Sheet3!A$30),ISNUMBER(Sheet3!A$30 ),Sheet3!C30,Sheet3!C28),Sheet3!C25) In plain talk, if there is text or numbers in A28, check if there is text or numbers in A30, if no in the first IF use c25, if no in the second arguement use c28, if yes for both use c30 Any ideas? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with nested if/or statements
We can also look at a different set of reference cells.
With 3 capture lines: C10 has data, C8 has data and C6 has data With 2 capture lines: C10 no data, C8 has data and C6 has data With 1 capture line: C10 no data, C8 no data, C6 has data We're changing the reference cells because A30 was full if I had more than one datum somewhere else (yay, mission creep). To re-iterate the result cells desired: With 3 cap lines, pull info from C30, with 2 cap lines pull info from C28, with one cap line pull info from C26. Thanks in advance! Nick "JMB" wrote: Maybe you could try: =CHOOSE(ISBLANK(Sheet3!A28)+ISBLANK(Sheet3!A30)*2+ 1,Sheet3!C30,Sheet3!C25,Sheet3!C28,"") Since you didn't say, I used "" if both A28 and A30 are empty. And I assume when you said "if the cell does not have text or numbers, it is empty" you meant it is truly empty and not the null string "". "Nick" wrote: Hi, I'm trying to construct a formula that does the following =IF(AND(OR(ISTEXT(Sheet3!$A$28),ISNUMBER(Sheet3!$A $28))),OR(ISTEXT(Sheet3!A$30),ISNUMBER(Sheet3!A$30 ),Sheet3!C30,Sheet3!C28),Sheet3!C25) In plain talk, if there is text or numbers in A28, check if there is text or numbers in A30, if no in the first IF use c25, if no in the second arguement use c28, if yes for both use c30 Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested IF statements | New Users to Excel | |||
| Nested IF statements | Excel Worksheet Functions | |||
| I want to use more than 7 nested if then statements | Excel Worksheet Functions | |||
| Nested IF statements | Excel Worksheet Functions | |||
| Do I need nested IF statements? | Excel Worksheet Functions | |||
Linear Mode
