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