|
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 |
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 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 |
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? |
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 | |
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 |
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? |
| All times are GMT +1. The time now is 12:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com