G
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
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