7+ Nested if Statement using indirect function

[Guest]

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("'"&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")),INDIRECT("'"&F12&"'!j13")))))))

Thanks!

 

[Guest]

=IF($E12={"","(Show All)","(All)"},continue your lengthy formula,"")

 

[Guest]

Thanks Teethless Mama! I'll give it a shot. Cheers!

 

[Guest]

Correction:

=IF(OR($E12={"","(Show All)","(All)"}),continue your lengthy formula,"")

 

[Guest]

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",INDIRECT("'"&F12&"'!j8"))),"",INDIRECT("'"&F12&"'!j8")),"")&IF($E12="Investment
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.

HTH,
Elkar

 

[Guest]

Thanks very much Elkar. Your help is much appreciated.

 

[Harlan Grove]

=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="Investment 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")))