Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3 nested IFs causes error
I have the following formula:
=IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLoo kup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(C8,'Activity Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity Desc.'!$A$2:$A$43,0),1)),"Info"))) As soon as I add one more IF statement to the front of the formula: =IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(V LOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISE RROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(C8,'Activity Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity Desc.'!$A$2:$A$43,0),1)),"Info")))) I get an error (and the FIND function is highlighted)! Can anyone tell me why? I thought Excel allows up to 7 nested IF statements. My formula has only 3. Thanks for the help, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3 nested IFs causes error
Hi Bob,
<Excel allows up to 7 nested IF statements A common misunderstanding. Excel allows 7 nested functions, no matter what sort. You reached that limit in your first formula version. There usually are many possible workarounds. Start he http://www.j-walk.com/ss/excel/usertips/tip080.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Bob" wrote in message ... |I have the following formula: | | =IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLoo kup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity | Desc.'!$E$2:$E$43,MATCH(C8,'Activity | Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity | Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity | Desc.'!$A$2:$A$43,0),1)),"Info"))) | | As soon as I add one more IF statement to the front of the formula: | | =IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(V LOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISE RROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity | Desc.'!$E$2:$E$43,MATCH(C8,'Activity | Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity | Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity | Desc.'!$A$2:$A$43,0),1)),"Info")))) | | I get an error (and the FIND function is highlighted)! Can anyone tell me | why? I thought Excel allows up to 7 nested IF statements. My formula has | only 3. | Thanks for the help, | Bob | |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3 nested IFs causes error
It's not 7 nested IF's. It's 7 levels of nesting--no matter what the function.
IF( ISERROR( LEFT(C8, FIND("--",C8,10)-2)), HYPERLINK("#"& CELL("address", INDEX('Activity Bob wrote: I have the following formula: =IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLoo kup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(C8,'Activity Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity Desc.'!$A$2:$A$43,0),1)),"Info"))) As soon as I add one more IF statement to the front of the formula: =IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(V LOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISE RROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(C8,'Activity Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity Desc.'!$A$2:$A$43,0),1)),"Info")))) I get an error (and the FIND function is highlighted)! Can anyone tell me why? I thought Excel allows up to 7 nested IF statements. My formula has only 3. Thanks for the help, Bob -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3 nested IFs causes error
Bob wrote...
.... As soon as I add one more IF statement to the front of the formula: =IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8, INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0) ),"", IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)), HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43, MATCH(C8,'Activity Desc.'!$A$2:$A$43,0),1)),"Info"), HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43, MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity Desc.'!$A$2:$A$43,0),1)),"Info")))) I get an error (and the FIND function is highlighted)! Can anyone tell me why? I thought Excel allows up to 7 nested IF statements. My formula has only 3. Others have already mentioned that the limit is 7 nested function calls - ANY function calls. As for your particular formula, you could try rewriting it, perhaps as =IF(OR(ISBLANK($B8),ISNA(VLOOKUP($C8, INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)) ),"", HYPERLINK("#"&CELL("Address",INDEX('Activity Desc.'!$E$2:$E$43, MATCH(IF(COUNTIF(C8,"??????????*--*"),LEFT(C8,FIND("--",C8,10)-2),C8), 'Activity Desc.'!$A$2:$A$43,0),1)),"Info")) Note that only your first reference to cell C8 is column-absolute, $C8, while all the others are fully relative, C8. Is that really what you want? Also, FIND("--",$C8,10)-2 looks like a bug because it ends the substring *2* chars before the first hyphen. For example, if C8 were 1234567890--xyz FIND("--",$C8,10)-2 would return 9, and LEFT($C8,FIND("--",$C8,10)-2) would return "123456789" rather than "1234567890". Is that really what you want? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3 nested IFs causes error
Niek,
Thanks for the clarification. Now I understand why I received the error. Bob "Niek Otten" wrote: Hi Bob, <Excel allows up to 7 nested IF statements A common misunderstanding. Excel allows 7 nested functions, no matter what sort. You reached that limit in your first formula version. There usually are many possible workarounds. Start he http://www.j-walk.com/ss/excel/usertips/tip080.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Bob" wrote in message ... |I have the following formula: | | =IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLoo kup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity | Desc.'!$E$2:$E$43,MATCH(C8,'Activity | Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity | Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity | Desc.'!$A$2:$A$43,0),1)),"Info"))) | | As soon as I add one more IF statement to the front of the formula: | | =IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(V LOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISE RROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity | Desc.'!$E$2:$E$43,MATCH(C8,'Activity | Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity | Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity | Desc.'!$A$2:$A$43,0),1)),"Info")))) | | I get an error (and the FIND function is highlighted)! Can anyone tell me | why? I thought Excel allows up to 7 nested IF statements. My formula has | only 3. | Thanks for the help, | Bob | |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3 nested IFs causes error
Dave,
Thanks for the clarification. Now I understand why I received the error message. Bob "Dave Peterson" wrote: It's not 7 nested IF's. It's 7 levels of nesting--no matter what the function. IF( ISERROR( LEFT(C8, FIND("--",C8,10)-2)), HYPERLINK("#"& CELL("address", INDEX('Activity Bob wrote: I have the following formula: =IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLoo kup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(C8,'Activity Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity Desc.'!$A$2:$A$43,0),1)),"Info"))) As soon as I add one more IF statement to the front of the formula: =IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(V LOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISE RROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(C8,'Activity Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity Desc.'!$A$2:$A$43,0),1)),"Info")))) I get an error (and the FIND function is highlighted)! Can anyone tell me why? I thought Excel allows up to 7 nested IF statements. My formula has only 3. Thanks for the help, Bob -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3 nested IFs causes error
Harlan,
Thanks for suggesting using the OR function. Also, the string I'm searching for the "--" in looks like this: C8 = 1234567890 -- xyz So LEFT(C8,FIND("--",C8,10)-2) would return 1234567890, which is what I want. Thanks again, Bob "Harlan Grove" wrote: Bob wrote... .... As soon as I add one more IF statement to the front of the formula: =IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8, INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0) ),"", IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)), HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43, MATCH(C8,'Activity Desc.'!$A$2:$A$43,0),1)),"Info"), HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43, MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity Desc.'!$A$2:$A$43,0),1)),"Info")))) I get an error (and the FIND function is highlighted)! Can anyone tell me why? I thought Excel allows up to 7 nested IF statements. My formula has only 3. Others have already mentioned that the limit is 7 nested function calls - ANY function calls. As for your particular formula, you could try rewriting it, perhaps as =IF(OR(ISBLANK($B8),ISNA(VLOOKUP($C8, INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)) ),"", HYPERLINK("#"&CELL("Address",INDEX('Activity Desc.'!$E$2:$E$43, MATCH(IF(COUNTIF(C8,"??????????*--*"),LEFT(C8,FIND("--",C8,10)-2),C8), 'Activity Desc.'!$A$2:$A$43,0),1)),"Info")) Note that only your first reference to cell C8 is column-absolute, $C8, while all the others are fully relative, C8. Is that really what you want? Also, FIND("--",$C8,10)-2 looks like a bug because it ends the substring *2* chars before the first hyphen. For example, if C8 were 1234567890--xyz FIND("--",$C8,10)-2 would return 9, and LEFT($C8,FIND("--",$C8,10)-2) would return "123456789" rather than "1234567890". Is that really what you want? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error bars display | Charts and Charting in Excel | |||
Nested subtotal display error | Excel Discussion (Misc queries) | |||
error message when opening excel 2000 | Excel Discussion (Misc queries) | |||
nested if statement returns #value error | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions |