large formula question - Max nested functions

  • Thread starter Thread starter PCLIVE
  • Start date Start date
P

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
 
PCLIVE wrote...
Does anyone know if this formula exceeds the maximum number of nested
functions?
Click to expand...

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")
Click to expand...
....

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

I'm getting closer.

Paul

Harlan Grove said:
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")
Click to expand...
 
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
 
Back
Top