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

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

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

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

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

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+ 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")))