+ Reply to Thread
Results 1 to 10 of 10

VLOOKUP with sheet name as cell reference

  1. #1
    Registered User
    Join Date
    03-18-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2007
    Posts
    10

    VLOOKUP with sheet name as cell reference

    Hello everyone,

    I am working on an excel workbook that has numerous worksheets. I have an inventory list that I am separating by different vendors. Anyway, I have to link up a buch of rows on the main list using a VLOOKUP formula. however there are more that 10,000 items. so here is what I want. the current formula looks like this:

    =VLOOKUP(A8,OHFULTO!$A$7:$V$9,15,FALSE)

    I want to know if there is a way that I can substitute the letters in green with a reference that will link to the appropriate page so that I dont have to go an link the items page by page. I have a colum already with the vendor names which matches the worksheet titles. Is there a way or function to keep the VLOOKUP that i need but substitute the green part with the title that I can get from a reference of the "vendor name colunm"

    Please help me out guys. thank you!
    Last edited by boogyman33; 03-19-2013 at 09:23 AM. Reason: Admin told me to

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with a Formula Question

    Try this...

    If cell X1 contains the sheet name OHFULTO...

    =VLOOKUP(A8,INDIRECT("'"&X1&"'!A7:V9"),15,0)


    EDIT...

    On a side note...

    You're probably going to get "yelled" at for using an ambiguous thread title. I suggest you change it to something like:

    VLOOKUP with sheet name as cell reference
    Last edited by Tony Valko; 03-18-2013 at 04:30 PM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    12-17-2012
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    80

    Re: Help with a Formula Question

    Do you have a small example to show us?

    Perhaps take the workbook you are working on, and delete all but the top 7 rows of each sheet.

  4. #4
    Registered User
    Join Date
    03-18-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Help with a Formula Question

    Hey. Thank you so much for the help. I tried doing what you told me but I was not able to do it. It said the function had too many variables. and keeps giving me an error. I will post the sheet soon

  5. #5
    Registered User
    Join Date
    03-18-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Help with a Formula Question

    Here is the sample requested. Please let me know if you need anything else. Thank you again for all of your help!
    Attached Files Attached Files

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with a Formula Question

    Enter this formula On the MAIN SHEET cell I2 and copy down as needed:

    =VLOOKUP(A2,INDIRECT(G2&"!A:V"),15,0)

  7. #7
    Registered User
    Join Date
    03-18-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2007
    Posts
    10

    Thumbs up Re: VLOOKUP with sheet name as cell reference

    THANK YOU!!! That worked perfectly! it is exactly what I needed. Thank you so much

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: VLOOKUP with sheet name as cell reference

    You're welcome. Thanks for the feedback!

  9. #9
    Registered User
    Join Date
    02-23-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2013
    Posts
    31

    Re: VLOOKUP with sheet name as cell reference

    Hi there, im looking for something like this, is it possible for the vlookup to automatically input a value based on the tab name, ive seen the formula for the one in the sample sheet and that is specific to the sheet name, can it be such where its sheet name A or B or C in one vlooup formula?

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: VLOOKUP with sheet name as cell reference

    Quote Originally Posted by merQrey View Post
    Hi there, im looking for something like this, is it possible for the vlookup to automatically input a value based on the tab name, ive seen the formula for the one in the sample sheet and that is specific to the sheet name, can it be such where its sheet name A or B or C in one vlooup formula?
    HI, welcome to the forum

    To use a variable sheet name (if I understand, you would need to use INDIRECT.

    However, Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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