| Home |
| Search |
| Today's Posts |
|
|
|
#1
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 |
|
#2
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 |
|
#3
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 |
| 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) | |||
Hybrid Mode
