| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
7+ Nested if Statement using indirect function
Hi,
I have a nested if statement that doesn't seem to be working. I have two questions. First, how do I equate the first instance of $E12 to be either "", "(Show All)", or "(All)" in order to be true? And how do I get past this If-Then statement limit with such a complex formula? Here is the formula below: =IF(($E12)=""="(Show All)"="(All)",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j7"))),"",INDIRECT("'"&F12&"'!j7")),IF(( $E12)="Base",IF(ISERROR(CELL("address",INDIRECT("' "&F12&"'!j8"))),"",INDIRECT("'"&F12&"'!j8")),IF(($ E12)="Investment Projects",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j9"))),"",INDIRECT("'"&F12&"'!j9")),IF(($E12 )="Corporate",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j10"))),"",INDIRECT("'"&F12&"'!j10")),IF (($E12)="Inter-Branch Charging",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j11"))),"",INDIRECT("'"&F12&"'!j11")),IF(($E 12)="CBSA",IF(ISERROR(CELL("address",INDIRECT("'"& F12&"'!j12"))),"",INDIRECT("'"&F12&"'!j12")),INDIR ECT("'"&F12&"'!j13"))))))) Thanks! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7+ Nested if Statement using indirect function
=IF($E12={"","(Show All)","(All)"},continue your lengthy formula,"")
"klysell" wrote: Hi, I have a nested if statement that doesn't seem to be working. I have two questions. First, how do I equate the first instance of $E12 to be either "", "(Show All)", or "(All)" in order to be true? And how do I get past this If-Then statement limit with such a complex formula? Here is the formula below: =IF(($E12)=""="(Show All)"="(All)",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j7"))),"",INDIRECT("'"&F12&"'!j7")),IF(( $E12)="Base",IF(ISERROR(CELL("address",INDIRECT("' "&F12&"'!j8"))),"",INDIRECT("'"&F12&"'!j8")),IF(($ E12)="Investment Projects",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j9"))),"",INDIRECT("'"&F12&"'!j9")),IF(($E12 )="Corporate",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j10"))),"",INDIRECT("'"&F12&"'!j10")),IF (($E12)="Inter-Branch Charging",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j11"))),"",INDIRECT("'"&F12&"'!j11")),IF(($E 12)="CBSA",IF(ISERROR(CELL("address",INDIRECT("'"& F12&"'!j12"))),"",INDIRECT("'"&F12&"'!j12")),INDIR ECT("'"&F12&"'!j13"))))))) Thanks! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7+ Nested if Statement using indirect function
Thanks Teethless Mama! I'll give it a shot. Cheers!
-- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Teethless mama" wrote: =IF($E12={"","(Show All)","(All)"},continue your lengthy formula,"") "klysell" wrote: Hi, I have a nested if statement that doesn't seem to be working. I have two questions. First, how do I equate the first instance of $E12 to be either "", "(Show All)", or "(All)" in order to be true? And how do I get past this If-Then statement limit with such a complex formula? Here is the formula below: =IF(($E12)=""="(Show All)"="(All)",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j7"))),"",INDIRECT("'"&F12&"'!j7")),IF(( $E12)="Base",IF(ISERROR(CELL("address",INDIRECT("' "&F12&"'!j8"))),"",INDIRECT("'"&F12&"'!j8")),IF(($ E12)="Investment Projects",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j9"))),"",INDIRECT("'"&F12&"'!j9")),IF(($E12 )="Corporate",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j10"))),"",INDIRECT("'"&F12&"'!j10")),IF (($E12)="Inter-Branch Charging",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j11"))),"",INDIRECT("'"&F12&"'!j11")),IF(($E 12)="CBSA",IF(ISERROR(CELL("address",INDIRECT("'"& F12&"'!j12"))),"",INDIRECT("'"&F12&"'!j12")),INDIR ECT("'"&F12&"'!j13"))))))) Thanks! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7+ Nested if Statement using indirect function
Correction:
=IF(OR($E12={"","(Show All)","(All)"}),continue your lengthy formula,"") "Teethless mama" wrote: =IF($E12={"","(Show All)","(All)"},continue your lengthy formula,"") "klysell" wrote: Hi, I have a nested if statement that doesn't seem to be working. I have two questions. First, how do I equate the first instance of $E12 to be either "", "(Show All)", or "(All)" in order to be true? And how do I get past this If-Then statement limit with such a complex formula? Here is the formula below: =IF(($E12)=""="(Show All)"="(All)",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j7"))),"",INDIRECT("'"&F12&"'!j7")),IF(( $E12)="Base",IF(ISERROR(CELL("address",INDIRECT("' "&F12&"'!j8"))),"",INDIRECT("'"&F12&"'!j8")),IF(($ E12)="Investment Projects",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j9"))),"",INDIRECT("'"&F12&"'!j9")),IF(($E12 )="Corporate",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j10"))),"",INDIRECT("'"&F12&"'!j10")),IF (($E12)="Inter-Branch Charging",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j11"))),"",INDIRECT("'"&F12&"'!j11")),IF(($E 12)="CBSA",IF(ISERROR(CELL("address",INDIRECT("'"& F12&"'!j12"))),"",INDIRECT("'"&F12&"'!j12")),INDIR ECT("'"&F12&"'!j13"))))))) Thanks! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7+ Nested if Statement using indirect function
This might be a lot easier if set up in a table and then use a VLOOKUP to
return the correct value. But, without knowing more about how your data is setup, I can't offer further help on that. But, here's one way to rewrite your formula to get around the 7-level limit. It's still a behemoth, but I think this will work. =IF(OR($E12={"","(Show All)","(All)"}),IF(ISERROR(CELL("address",INDIRECT ("'"&F12&"'!j7"))),"",INDIRECT("'"&F12&"'!j7")),"" )&IF($E12="Base",IF(ISERROR(CELL("address",INDIREC T("'"&F12&"'!j8"))),"",INDIRECT("'"&F12&"'!j8"))," ")&IF($E12="Investment Projects",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j9"))),"",INDIRECT("'"&F12&"'!j9")),"")&IF($ E12="Corporate",IF(ISERROR(CELL("address",INDIRECT ("'"&F12&"'!j10"))),"",INDIRECT("'"&F12&"'!j10")), "")&IF($E12="Inter-Branch Charging",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j11"))),"",INDIRECT("'"&F12&"'!j11")),"")&IF ($E12="CBSA",IF(ISERROR(CELL("address",INDIRECT("' "&F12&"'!j12"))),"",INDIRECT("'"&F12&"'!j12")),"") &IF(AND($E12<{"","(Show All)","(All)","Base","Investment Projects","Corporate","Inter-Branch Charging","CBSA"}),IF(ISERROR(CELL("address",INDIR ECT("'"&F12&"'!j13"))),"",INDIRECT("'"&F12&"'!j13" )),"") Instead of nesting the formulas inside one another, I just concatenated several IFs together. Since its impossible for more than one of them to be TRUE at the same time, this method works. HTH, Elkar "klysell" wrote: Hi, I have a nested if statement that doesn't seem to be working. I have two questions. First, how do I equate the first instance of $E12 to be either "", "(Show All)", or "(All)" in order to be true? And how do I get past this If-Then statement limit with such a complex formula? Here is the formula below: =IF(($E12)=""="(Show All)"="(All)",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j7"))),"",INDIRECT("'"&F12&"'!j7")),IF(( $E12)="Base",IF(ISERROR(CELL("address",INDIRECT("' "&F12&"'!j8"))),"",INDIRECT("'"&F12&"'!j8")),IF(($ E12)="Investment Projects",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j9"))),"",INDIRECT("'"&F12&"'!j9")),IF(($E12 )="Corporate",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j10"))),"",INDIRECT("'"&F12&"'!j10")),IF (($E12)="Inter-Branch Charging",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j11"))),"",INDIRECT("'"&F12&"'!j11")),IF(($E 12)="CBSA",IF(ISERROR(CELL("address",INDIRECT("'"& F12&"'!j12"))),"",INDIRECT("'"&F12&"'!j12")),INDIR ECT("'"&F12&"'!j13"))))))) Thanks! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7+ Nested if Statement using indirect function
Thanks very much Elkar. Your help is much appreciated.
-- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Elkar" wrote: This might be a lot easier if set up in a table and then use a VLOOKUP to return the correct value. But, without knowing more about how your data is setup, I can't offer further help on that. But, here's one way to rewrite your formula to get around the 7-level limit. It's still a behemoth, but I think this will work. =IF(OR($E12={"","(Show All)","(All)"}),IF(ISERROR(CELL("address",INDIRECT ("'"&F12&"'!j7"))),"",INDIRECT("'"&F12&"'!j7")),"" )&IF($E12="Base",IF(ISERROR(CELL("address",INDIREC T("'"&F12&"'!j8"))),"",INDIRECT("'"&F12&"'!j8"))," ")&IF($E12="Investment Projects",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j9"))),"",INDIRECT("'"&F12&"'!j9")),"")&IF($ E12="Corporate",IF(ISERROR(CELL("address",INDIRECT ("'"&F12&"'!j10"))),"",INDIRECT("'"&F12&"'!j10")), "")&IF($E12="Inter-Branch Charging",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j11"))),"",INDIRECT("'"&F12&"'!j11")),"")&IF ($E12="CBSA",IF(ISERROR(CELL("address",INDIRECT("' "&F12&"'!j12"))),"",INDIRECT("'"&F12&"'!j12")),"") &IF(AND($E12<{"","(Show All)","(All)","Base","Investment Projects","Corporate","Inter-Branch Charging","CBSA"}),IF(ISERROR(CELL("address",INDIR ECT("'"&F12&"'!j13"))),"",INDIRECT("'"&F12&"'!j13" )),"") Instead of nesting the formulas inside one another, I just concatenated several IFs together. Since its impossible for more than one of them to be TRUE at the same time, this method works. HTH, Elkar "klysell" wrote: Hi, I have a nested if statement that doesn't seem to be working. I have two questions. First, how do I equate the first instance of $E12 to be either "", "(Show All)", or "(All)" in order to be true? And how do I get past this If-Then statement limit with such a complex formula? Here is the formula below: =IF(($E12)=""="(Show All)"="(All)",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j7"))),"",INDIRECT("'"&F12&"'!j7")),IF(( $E12)="Base",IF(ISERROR(CELL("address",INDIRECT("' "&F12&"'!j8"))),"",INDIRECT("'"&F12&"'!j8")),IF(($ E12)="Investment Projects",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j9"))),"",INDIRECT("'"&F12&"'!j9")),IF(($E12 )="Corporate",IF(ISERROR(CELL("address",INDIRECT(" '"&F12&"'!j10"))),"",INDIRECT("'"&F12&"'!j10")),IF (($E12)="Inter-Branch Charging",IF(ISERROR(CELL("address",INDIRECT("'"&F 12&"'!j11"))),"",INDIRECT("'"&F12&"'!j11")),IF(($E 12)="CBSA",IF(ISERROR(CELL("address",INDIRECT("'"& F12&"'!j12"))),"",INDIRECT("'"&F12&"'!j12")),INDIR ECT("'"&F12&"'!j13"))))))) Thanks! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7+ Nested if Statement using indirect function
"Elkar" wrote...
.... =IF(OR($E12={"","(Show All)","(All)"}), IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j7" ))),"", INDIRECT("'"&F12&"'!j7")),"")&IF($E12="Base", IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j8" ))),"", INDIRECT("'"&F12&"'!j8")),"")&IF($E12="Investme nt Projects", IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j9" ))),"", INDIRECT("'"&F12&"'!j9")),"")&IF($E12="Corporate" , IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j10 "))),"", INDIRECT("'"&F12&"'!j10")),"")&IF($E12="Inter-Branch Charging", IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j11 "))),"", INDIRECT("'"&F12&"'!j11")),"")&IF($E12="CBSA", IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j12 "))),"", INDIRECT("'"&F12&"'!j12")),"")&IF(AND($E12<{""," (Show All)","(All)", "Base","Investment Projects","Corporate","Inter-Branch Charging","CBSA"}), IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j13 "))),"", INDIRECT("'"&F12&"'!j13")),"") Instead of nesting the formulas inside one another, I just concatenated several IFs together. Since its impossible for more than one of them to be TRUE at the same time, this method works. Better to use some lookups. And it's not necessary to check the precise cell reference, just check that F12 points to a worksheet in some open workbook. =IF(ISERROR(CELL("Address",INDIRECT("'"&F12&"'!J1" ))),"", IF(OR(COUNTIF($E12,{"","(All)","(Show All)","Base","CBSA","Corporate", "Inter-Branch Charging","Investment Projects"})),INDIRECT("'"&F12&"'!J"& LOOKUP($E12,{"","(All)","(Show All)","Base","CBSA","Corporate", "Inter-Branch Charging","Investment Projects"},7,7,7,8,12,10,11,9})), INDIRECT("'"&F12&"'!J13"))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF STATEMENT - NESTED | Excel Worksheet Functions | |||
| Why doesn't the INDIRECT function work when nested in FREQUENCY? | Excel Worksheet Functions | |||
| Fill an Indirect Statement | Excel Discussion (Misc queries) | |||
| nested Indirect to other WS | Excel Worksheet Functions | |||
| Combining SUM Function with Nested If Statement | Excel Discussion (Misc queries) | |||
Linear Mode
