vlookup with dynamic table_array
 

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

vlookup with dynamic table_array
 

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

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

vlookup with dynamic table_array
 

You're welcome. Thanks for the feedback!

Biff

"Jim Toohey" wrote in message
...
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