vlookup with dynamic table_array
Biff:
Worked like a charm. Thanks.
--
Jim T
"Biff" wrote:
Try it like this:
N1 = Beds101-200 (or whatever sheet name: Beds201-300)
=VLOOKUP(DataCollection!B8,INDIRECT("'"&N1&"'!A1:I 68"),7)
Biff
"Jim Toohey" wrote in message
...
I'm trying to use vlookup with multiple worksheets defined by a dynamic
table_array value. I can store the appropriate value in a cell with the
correct format, but the vlookup function returns a REF error.
Example:
=VLOOKUP(DataCollection!B8,'Beds101-200'!A1:I68,7) works fine if I type
the
specific table_array name in.
If I build the table_array name in cell o1, like this
=TRIM("'"&TRIM(N1)&"'!B1:I68"), which displays like this:
'Beds101-200'!A1:I68, I can't get the function to work.
Here is the final cell format =vlookup(DataCollection!B8,$o$1,7)
I've also tried =vlookup(DataCollection!B8,"$o$1",7)
Is there a way to get this to work?
--
Jim T
|