large formula question - Max nested functions

[PCLIVE]

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,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")


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,Sheet1!H2:L50,5),"
","+")),D110,(SUBSTITUTE(VLOOKUP(EN11,Sheet1!H2:L50,2),"
","+")),D112,SUBSTITUTE(LEFT(VLOOKUP(EN11,Sheet1!H2: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

 

[Harlan Grove]

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")

 

[PCLIVE]

Thanks Harlan,

I'm getting closer.

Paul

PCLIVE wrote...

You could check. Excel simply won't allow you to enter formulas that
exceed this limit.

...

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")

 

[PCLIVE]

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