+ Reply to Thread
Results 1 to 4 of 4

VLookup to another sheet based on cell value having the sheet name - impossible!

  1. #1
    Registered User
    Join Date
    03-25-2020
    Location
    US
    MS-Off Ver
    16
    Posts
    2

    VLookup to another sheet based on cell value having the sheet name - impossible!

    Hi Everyone,

    I am trying to use Vlookup to automate a file based on the selected month. So for instance I have setup cell B3 as a drop-down list. The list contains the abbreviated months Jan to Dec, and my other sheets are named the same as these options. So now I want to use cell B3 as the sheet that VLoopup will select the array from.

    So my test is setup to select "Jan" by clicking on that tab, which is ok, but if I want to look at Feb next, it doesn't change because it isn't linked to the input cell with the different months.

    =VLOOKUP("Test",Jan!A1:N35,2,1)

    I cannot get any indirect function to work, below is an example of selecting the cell B3 as the sheet for the array (which is obviously wrong). Can anyone help me figure out what I need to put in here to select the sheet based on the cell input?

    =VLOOKUP("TEST",'B3'!A1:N35,2,1)
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: VLookup to another sheet based on cell value having the sheet name - impossible!

    How about
    =VLOOKUP("Test",INDIRECT("'"&B3&"'!A1:N35"),2,1)

  3. #3
    Registered User
    Join Date
    03-25-2020
    Location
    US
    MS-Off Ver
    16
    Posts
    2

    Re: VLookup to another sheet based on cell value having the sheet name - impossible!

    Ahh that worked thank you! I don't get the quotation marks around the B3 part I guess.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: VLookup to another sheet based on cell value having the sheet name - impossible!

    You're welcome & thanks for the feedback.
    There is a good explanation on the indirect function here
    https://www.ablebits.com/office-addi...rect-function/

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extracting row data from sheet A to sheet B, based on cell value on sheet B.
    By JohnTrader in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-09-2019, 04:44 AM
  2. [SOLVED] Save sheet to new sheet and name it based on date on first sheet cell
    By dwheggen1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-28-2015, 12:25 PM
  3. [SOLVED] Locating a cell in sheet 2, based on 2 variables in sheet 1. Result into sheet 1.
    By Gezza24 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 11-05-2013, 12:50 AM
  4. Replies: 2
    Last Post: 04-04-2013, 10:32 AM
  5. Replies: 7
    Last Post: 12-16-2012, 04:24 PM
  6. [SOLVED] Macro to Copy Data from one Sheet A to Sheet B based on value in cell on sheet A
    By scass in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-11-2012, 07:21 PM
  7. Are data query and sheet protection impossible on the same sheet?
    By skysurfer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-06-2010, 07:11 PM

Tags for this Thread

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