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

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

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

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