creating a range reference from cell values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am try to do a vlookup where the range is built from cell values ex.

=VLOOKUP($K$16,TRIM(B17&"!"&"A1:L32"),3,FALSE)

b17 contains the sheet name

but gives me a #value! error

but works if the range is hard coded

But I really want to build the range dynamiclly

Can anyone help ???
thanks
 
You'll need to use the INDIRECT function. You could basically just replace
TRIM with INDIRECT in your formula and it should work.

=VLOOKUP($K$16,INDIRECT(B17&"!A1:L32"),3,FALSE)

Not sure why you're using the TRIM function in the first place, unless B17
may contain extra spaces? If so, just place B17 in the TRIM function.
....INDIRECT(TRIM(B17)&"!...

HTH,
Elkar
 
=VLOOKUP($K$16,indirect("'" & TRIM(B17) &"'!"&"A1:L32"),3,FALSE)

Are you sure you need the =trim() portion?
 
Back
Top