G

#### Guest

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