Using a cell reference of a sheet in Vlookup

[Guest]

I have a spreadsheet that performs vlookups from several other sheets. From
time to time new sheets are added and I would like to be able to change one
cell and have all the vlookups change to that sheet.

For example,

On sheet1 I have the following vlookups:

A10 =vlookup(D10,'My First Sheet'!A,4,false)
A11 =vlookup(D11,'My First Sheet'!A,4,false)
A12 =vlookup(D12,'My First Sheet'!A,4,false)
and so on...

I would like to have a cell on sheet1 that I can enter the name of the tab I
want to use. In the above case it would be "My First Sheet". If I changed
this cell to "My Second Sheet" I would want the above references lines to
become:

A10 =vlookup(D10,'My Second Sheet'!A,4,false)
A11 =vlookup(D11,'My Second Sheet'!A,4,false)
A12 =vlookup(D12,'My Second Sheet'!A,4,false)
and so on...

I have done this before using VBA, but I would like to do it without coding.

What I've tried is using the following:

A10 =vlookup(D10,"'" & $A$1 & "'!A",4,false)
A11 =vlookup(D11,,"'" & $A$1 & "'!A",4,false)
A12 =vlookup(D12,,"'" & $A$1 & "'!A",4,false)
and so on...

Where A1 would be where I type in a tab name.

This results in an error. Using the "Show Calculation Steps" tool I find
that the range reference in the vlookup still has quotation marks around it.
So instead of evaluating =vlookup(D10,'My Second Sheet'!A,4,false) it is
trying to evaluate =vlookup(D10,"'My Second Sheet'!A",4,false), which of
course doesn't work. Is there any way to remove the quotation marks, or any
other way of using a cell reference to a tab name in the vlookup function?

All help is much appreciated.

Mike

 

[John Michl]

You can use the INDIRECT function to piece together your formula. Try
the following:

A10 = vlookup(D10,indirect(A1)&"!A",4,false)

- John Michl

 

[Domenic]

You can use INDIRECT...

=VLOOKUP(D10,INDIRECT("'"&$A$1&"'!A"),4,FALSE)

Hope this helps!

 

[Guest]

Awesome!

You know, I had looked at the INDIRECT function in help and didn't think it
applied. I guess I should have tried it..

Thanks to both of you for the speedy response.

Mike