Home |
Search |
Today's Posts |
#1
|
|||
|
|||
large formula question - Max nested functions
Does anyone know if this formula exceeds the maximum number of nested
functions? =HYPERLINK(CONCATENATE(D102,(SUBSTITUTE(EL3," ","+")),D104,(SUBSTITUTE(LEFT(EL4,(LEN(EL4)-4))," ","+")),D106,"VA",D108,(SUBSTITUTE(VLOOKUP(EN11,Sh eet1!H2:L50,5)," ","+")),D110,(SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L5 0,2)," ","+")),D112,SUBSTITUTE(LEFT(VLOOKUP(EN11,Sheet1!H 2:L50,5,LEN(VLOOKUP(EN11,Sheet1!H2:L50,5)-4))," ","+"),D114& "VA"& D116),"Map") This one seems to work without error, but I have a need to add an extra VLOOKUP function to make the result correct. =HYPERLINK(CONCATENATE(D102,(SUBSTITUTE(EL3," ","+")),D104,(SUBSTITUTE(LEFT(EL4,(LEN(EL4)-4))," ","+")),D106,"VA",D108,(SUBSTITUTE(VLOOKUP(EN11,Sh eet1!H2:L50,5)," ","+")),D110,(SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L5 0,2)," ","+")),D112,SUBSTITUTE(LEFT(VLOOKUP(EN11,Sheet1!H 2:L50,5,LEN(EL4)-4))," ","+"),D114& "VA"& D116),"Map") If there's a better (cleaner) way to do this, I'm open to ideas. Thanks, Paul |
#2
|
|||
|
|||
PCLIVE wrote...
Does anyone know if this formula exceeds the maximum number of nested functions? You could check. Excel simply won't allow you to enter formulas that exceed this limit. =HYPERLINK(CONCATENATE(D102,(SUBSTITUTE(EL3," ","+")),D104, (SUBSTITUTE(LEFT(EL4,(LEN(EL4)-4))," ","+")),D106,"VA",D108, (SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,5)," ","+")),D110, (SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,2)," ","+")),D112, SUBSTITUTE(LEFT(VLOOKUP(EN11,Sheet1!H2:L50,5, LEN(VLOOKUP(EN11,Sheet1!H2:L50,5)-4))," ","+"),D114&"VA"&D116), "Map") .... Don't use CONCATENATE. Use the & operator. So replace CONCATENATE(a,b) with a&b. Sorter *and* it doesn't waste a nested function call level. I believe you also have a bug in this formula. I figure you meant =HYPERLINK(D102&SUBSTITUTE(EL3," ","+")&D104& SUBSTITUTE(LEFT(EL4,(LEN(EL4)-4))," ","+")&D106&"VA"&D108& SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,5)," ","+")&D110& SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,2)," ","+")&D112& SUBSTITUTE(LEFT(VLOOKUP(EN11,Sheet1!H2:L50,5), LEN(VLOOKUP(EN11,Sheet1!H2:L50,5)-4))," ","+")&D114&"VA"&D116)& "Map") |
#3
|
|||
|
|||
Thanks Harlan,
I'm getting closer. Paul "Harlan Grove" wrote in message oups.com... PCLIVE wrote... Does anyone know if this formula exceeds the maximum number of nested functions? You could check. Excel simply won't allow you to enter formulas that exceed this limit. =HYPERLINK(CONCATENATE(D102,(SUBSTITUTE(EL3," ","+")),D104, (SUBSTITUTE(LEFT(EL4,(LEN(EL4)-4))," ","+")),D106,"VA",D108, (SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,5)," ","+")),D110, (SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,2)," ","+")),D112, SUBSTITUTE(LEFT(VLOOKUP(EN11,Sheet1!H2:L50,5, LEN(VLOOKUP(EN11,Sheet1!H2:L50,5)-4))," ","+"),D114&"VA"&D116), "Map") ... Don't use CONCATENATE. Use the & operator. So replace CONCATENATE(a,b) with a&b. Sorter *and* it doesn't waste a nested function call level. I believe you also have a bug in this formula. I figure you meant =HYPERLINK(D102&SUBSTITUTE(EL3," ","+")&D104& SUBSTITUTE(LEFT(EL4,(LEN(EL4)-4))," ","+")&D106&"VA"&D108& SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,5)," ","+")&D110& SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,2)," ","+")&D112& SUBSTITUTE(LEFT(VLOOKUP(EN11,Sheet1!H2:L50,5), LEN(VLOOKUP(EN11,Sheet1!H2:L50,5)-4))," ","+")&D114&"VA"&D116)& "Map") |
#4
|
|||
|
|||
large formula question - Max nested functions
Harlan,
Thanks for your help and guidance. Because of your suggestions, I was able to tweek my formula and get it working. In the end, this is what worked for me. =HYPERLINK($D$102&SUBSTITUTE(EL$3," ","+")&$D$104& SUBSTITUTE(LEFT(EL$4,(LEN(EL$4)-4))," ","+") & $D$106 & "VA" & $D$108 & EL$5 &$D$110& SUBSTITUTE(VLOOKUP(EN11,Sheet1!$H$2:$L$50,2)," ","+")&$D$112& SUBSTITUTE(LEFT(VLOOKUP(EN11,Sheet1!$H$2:$L$50,4), LEN(VLOOKUP(EN11,Sheet1!$H$2:$L$50,4))-4)," ","+")& $D$114 &"VA"&$D$116, "Map") Thanks again, Paul "Harlan Grove" wrote in message oups.com... PCLIVE wrote... Does anyone know if this formula exceeds the maximum number of nested functions? You could check. Excel simply won't allow you to enter formulas that exceed this limit. =HYPERLINK(CONCATENATE(D102,(SUBSTITUTE(EL3," ","+")),D104, (SUBSTITUTE(LEFT(EL4,(LEN(EL4)-4))," ","+")),D106,"VA",D108, (SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,5)," ","+")),D110, (SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,2)," ","+")),D112, SUBSTITUTE(LEFT(VLOOKUP(EN11,Sheet1!H2:L50,5, LEN(VLOOKUP(EN11,Sheet1!H2:L50,5)-4))," ","+"),D114&"VA"&D116), "Map") ... Don't use CONCATENATE. Use the & operator. So replace CONCATENATE(a,b) with a&b. Sorter *and* it doesn't waste a nested function call level. I believe you also have a bug in this formula. I figure you meant =HYPERLINK(D102&SUBSTITUTE(EL3," ","+")&D104& SUBSTITUTE(LEFT(EL4,(LEN(EL4)-4))," ","+")&D106&"VA"&D108& SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,5)," ","+")&D110& SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,2)," ","+")&D112& SUBSTITUTE(LEFT(VLOOKUP(EN11,Sheet1!H2:L50,5), LEN(VLOOKUP(EN11,Sheet1!H2:L50,5)-4))," ","+")&D114&"VA"&D116)& "Map") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Question | Excel Discussion (Misc queries) | |||
Nested if question | Excel Discussion (Misc queries) | |||
formula question | Excel Discussion (Misc queries) | |||
Formula Question | Excel Discussion (Misc queries) | |||
Formula question... | Excel Discussion (Misc queries) |