[Robin Krupp]

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

[Bob Umlas]

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

"Robin Krupp" wrote in message
...
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

[Elkar]

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


"Robin Krupp" wrote:

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

[Dave Peterson]

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

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

Robin Krupp wrote:

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

--

Dave Peterson

[T. Valko]

Try it like this:

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

--
Biff
Microsoft Excel MVP


"Robin Krupp" wrote in message
...
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