|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 21st, 2007, 09:12 PM
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
August 21st, 2007, 09:48 PM
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
August 21st, 2007, 09:52 PM
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
August 21st, 2007, 10:22 PM
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
August 21st, 2007, 10:38 PM
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
August 22nd, 2007, 09:46 PM
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
August 23rd, 2007, 08:47 PM
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")))
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
