Vlookup
 

I have 10 sheets with different tables on each.
Using If and Vlookup I've exceded my 7 references.
What I would like is to be able to use the text data from a cell
to point to one of the 10 sheets. i.e. so that the cell data is named
the same as one of the sheets and Vlookup uses this in Table_array.

So if the value of cell A2="Wire"
Vlookup(C15,WireA1:C50,2) but if cell A3="Line"
then Vlookup(C15,LineA1:C50,2) etc
Many Thanks

Vlookup
 

If none of your sheet names contains any space character:
=VLOOKUP(C15,INDIRECT(A2&"!A1:C50"),2)
Or more generally:
=VLOOKUP(C15,INDIRECT("'"&A2&"'!A1:C50"),2)
(Note the apostrophe between the double quotes and another one before the
exclamation mark).

"Rolohound" wrote in message
...
I have 10 sheets with different tables on each.
Using If and Vlookup I've exceded my 7 references.
What I would like is to be able to use the text data from a cell
to point to one of the 10 sheets. i.e. so that the cell data is named
the same as one of the sheets and Vlookup uses this in Table_array.

So if the value of cell A2="Wire"
Vlookup(C15,WireA1:C50,2) but if cell A3="Line"
then Vlookup(C15,LineA1:C50,2) etc
Many Thanks

Vlookup
 

Try it like this:

=VLOOKUP(C15,INDIRECT("'"&A2&"'!A1:C50",2,0)

Hope this helps.

Pete

On Dec 20, 1:40 pm, Rolohound
wrote:
I have 10 sheets with different tables on each.
Using If and Vlookup I've exceded my 7 references.
What I would like is to be able to use the text data from a cell
to point to one of the 10 sheets. i.e. so that the cell data is named
the same as one of the sheets and Vlookup uses this in Table_array.

So if the value of cell A2="Wire"
Vlookup(C15,WireA1:C50,2) but if cell A3="Line"
then Vlookup(C15,LineA1:C50,2) etc
Many Thanks

Vlookup
 

Thankyou that worked exactly and should shorten the code considerably.
I've never used indirect before, but will look this function up as it seems
to be a very useful tool within excel.
Thanks once again.
Rolohound

"Stephen" wrote:

If none of your sheet names contains any space character:
=VLOOKUP(C15,INDIRECT(A2&"!A1:C50"),2)
Or more generally:
=VLOOKUP(C15,INDIRECT("'"&A2&"'!A1:C50"),2)
(Note the apostrophe between the double quotes and another one before the
exclamation mark).

"Rolohound" wrote in message
...
I have 10 sheets with different tables on each.
Using If and Vlookup I've exceded my 7 references.
What I would like is to be able to use the text data from a cell
to point to one of the 10 sheets. i.e. so that the cell data is named
the same as one of the sheets and Vlookup uses this in Table_array.

So if the value of cell A2="Wire"
Vlookup(C15,WireA1:C50,2) but if cell A3="Line"
then Vlookup(C15,LineA1:C50,2) etc
Many Thanks

Vlookup
 

You may wish to look here for a good description of how to use INDIRECT:
http://www.cpearson.com/excel/indirect.htm

"Rolohound" wrote in message
...
Thankyou that worked exactly and should shorten the code considerably.
I've never used indirect before, but will look this function up as it
seems
to be a very useful tool within excel.
Thanks once again.
Rolohound

"Stephen" wrote:

If none of your sheet names contains any space character:
=VLOOKUP(C15,INDIRECT(A2&"!A1:C50"),2)
Or more generally:
=VLOOKUP(C15,INDIRECT("'"&A2&"'!A1:C50"),2)
(Note the apostrophe between the double quotes and another one before the
exclamation mark).

"Rolohound" wrote in message
...
I have 10 sheets with different tables on each.
Using If and Vlookup I've exceded my 7 references.
What I would like is to be able to use the text data from a cell
to point to one of the 10 sheets. i.e. so that the cell data is named
the same as one of the sheets and Vlookup uses this in Table_array.

So if the value of cell A2="Wire"
Vlookup(C15,WireA1:C50,2) but if cell A3="Line"
then Vlookup(C15,LineA1:C50,2) etc
Many Thanks