+ Reply to Thread
Results 1 to 11 of 11

Can i get vlookup to change which sheet it is working from

  1. #1
    Registered User
    Join Date
    10-16-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    9

    Can i get vlookup to change which sheet it is working from

    Hi all,

    I am making good progress on a pay increase sheet I am doing. The last thing I need to do is make it usable for 20 different shops. So I have a branch number box that needs to be input manually each time but I would like that answer to affect which cells are being used for the vlookup. Please see attached as it will make more sense
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Can i get vlookup to change which sheet it is working from

    It doesn't make sense to me. Perhaps an explanation with examples.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    10-16-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Can i get vlookup to change which sheet it is working from

    hi alan,

    So what I am trying to achieve is I have tabs along the bottom of the workbook that will have different pay ranges for different branches. When someone fills out the branch number on sheet 1 I would like the formula to then relate to which pay range. Does that clear it up at all?

    At the moment I have tried to incorporate 'm3' in the formula instead of tying the branch number

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Can i get vlookup to change which sheet it is working from

    I suggest that you incorporate the Indirect function into your Vlookup to identify which sheet you want the Vlookup to search.

    It would look something like this. =vlookup(CellHoldingValueToLookup,Indirect(CellHoldingSheetName)RangeToLookup,columntoSearch,False)

    Without seeing actual sample data that looks like what you have in real life, this is the best I can offer you.

  5. #5
    Registered User
    Join Date
    10-16-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Can i get vlookup to change which sheet it is working from

    can you not see the data on the attached?

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Can i get vlookup to change which sheet it is working from

    There is no data in any of the tabs(sheets). Maybe I am not understanding, but you refer to tabs and that implies worksheets to me. Are you working in the table and the headings refer to the branches. Nomenclature can sometimes be confusing. If in a table then you would need the indirect function to be in the column position and not the sheet position of your vlookup formula.

  7. #7
    Registered User
    Join Date
    10-16-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Can i get vlookup to change which sheet it is working from

    At present all the tabs hold the same table of pay - which I will change when I get the correct figures so they will all be different. All the tabs along the bottom are named by the branch number at the moment if I type =VLOOKUP(C3,'216'!A1:B251,2) that would use the table on the 216 page (i.e. 216 pay scale).
    I want to try and get the branch number that is filled out in m3 on sheet one to be what changes the formula to which branch pay scale it uses.

    I am very sorry if that still makes no sense I really appreciate the effort you have already put in.

  8. #8
    Registered User
    Join Date
    10-16-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Can i get vlookup to change which sheet it is working from

    hmm - maybe I can get round this with a macro and a button.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Can i get vlookup to change which sheet it is working from

    Then use the indirect as I specified using the cell address of M3 in the function.

    Look at this explanation of Indirect http://www.techonthenet.com/excel/formulas/indirect.php

  10. #10
    Registered User
    Join Date
    10-16-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Can i get vlookup to change which sheet it is working from

    so I now have this in the cell =VLOOKUP(C3,INDIRECT(N3),A1:B251,2) and that comes back with #ref!

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Can i get vlookup to change which sheet it is working from

    =VLOOKUP(C3,INDIRECT("'"&N3&"'!A1:B251"),2,FALSE)

    The indirect identitifies the sheet, so you still have to keep the syntax for the vlookup the same as if you were hardcoding the name of the sheet.

    Alan
    Last edited by alansidman; 10-16-2013 at 09:04 PM.

+ 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. Replies: 1
    Last Post: 10-04-2013, 05:54 PM
  2. using vlookup with sheet.change and multiple macros
    By phpolicylady in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-30-2013, 10:14 AM
  3. [SOLVED] How to stop Private sub Sheet change (located at Thisworkbook) working in worksheet 3
    By alexnkc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2013, 09:34 PM
  4. macro not working on sheet change
    By David777 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-12-2011, 03:40 PM
  5. vlookup formula not working with data on separate sheet
    By akee in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-16-2006, 11:18 AM

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