Vlookup

[Rolohound]

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

 

[Stephen]

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).

 

[Pete_UK]

Try it like this:

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

Hope this helps.

Pete

 

[Rolohound]

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]

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