+ Reply to Thread
Results 1 to 4 of 4

Using a cell reference of a sheet in Vlookup

  1. #1
    crazybass2
    Guest

    Using a cell reference of a sheet in Vlookup

    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:D,4,false)
    A11 =vlookup(D11,'My First Sheet'!A:D,4,false)
    A12 =vlookup(D12,'My First Sheet'!A:D,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:D,4,false)
    A11 =vlookup(D11,'My Second Sheet'!A:D,4,false)
    A12 =vlookup(D12,'My Second Sheet'!A:D,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:D",4,false)
    A11 =vlookup(D11,,"'" & $A$1 & "'!A:D",4,false)
    A12 =vlookup(D12,,"'" & $A$1 & "'!A:D",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:D,4,false) it is
    trying to evaluate =vlookup(D10,"'My Second Sheet'!A:D",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

  2. #2
    John Michl
    Guest

    Re: Using a cell reference of a sheet in Vlookup

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

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

    - John Michl


  3. #3
    Domenic
    Guest

    Re: Using a cell reference of a sheet in Vlookup

    You can use INDIRECT...

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

    Hope this helps!

    In article <[email protected]>,
    "crazybass2" <[email protected]> wrote:

    > 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:D,4,false)
    > A11 =vlookup(D11,'My First Sheet'!A:D,4,false)
    > A12 =vlookup(D12,'My First Sheet'!A:D,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:D,4,false)
    > A11 =vlookup(D11,'My Second Sheet'!A:D,4,false)
    > A12 =vlookup(D12,'My Second Sheet'!A:D,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:D",4,false)
    > A11 =vlookup(D11,,"'" & $A$1 & "'!A:D",4,false)
    > A12 =vlookup(D12,,"'" & $A$1 & "'!A:D",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:D,4,false) it is
    > trying to evaluate =vlookup(D10,"'My Second Sheet'!A:D",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


  4. #4
    crazybass2
    Guest

    Re: Using a cell reference of a sheet in Vlookup

    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

    "Domenic" wrote:

    > You can use INDIRECT...
    >
    > =VLOOKUP(D10,INDIRECT("'"&$A$1&"'!A:D"),4,FALSE)
    >
    > Hope this helps!
    >
    > In article <[email protected]>,
    > "crazybass2" <[email protected]> wrote:
    >
    > > 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:D,4,false)
    > > A11 =vlookup(D11,'My First Sheet'!A:D,4,false)
    > > A12 =vlookup(D12,'My First Sheet'!A:D,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:D,4,false)
    > > A11 =vlookup(D11,'My Second Sheet'!A:D,4,false)
    > > A12 =vlookup(D12,'My Second Sheet'!A:D,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:D",4,false)
    > > A11 =vlookup(D11,,"'" & $A$1 & "'!A:D",4,false)
    > > A12 =vlookup(D12,,"'" & $A$1 & "'!A:D",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:D,4,false) it is
    > > trying to evaluate =vlookup(D10,"'My Second Sheet'!A:D",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

    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1