+ Reply to Thread
Results 1 to 5 of 5

Using a cell value to look at a specific tab for data

  1. #1
    Registered User
    Join Date
    05-28-2012
    Location
    Shanghai, China
    MS-Off Ver
    Excel 2007
    Posts
    2

    Using a cell value to look at a specific tab for data

    I have a spread sheet with many tabs - linked to project number. For example tab 9999 will contain data about project 9999.

    I have a list of project numbers.

    I want to know how to get excel to read the value in the cell, and go to that tab to retrieve the relevant information:

    for example in the project number list cell A1 is the written project number: 9999

    The data I want put in cell B1 would be data from tab 9999.eg ='9999'!$L$2

    Instead of manually inputting 9999 I would hope that ='=A1'!$L$2 would work.

    It doesn't, is there a way to do this? I have hundreds of projects and references related to the project number and project tab,

    I need excel to just read what the text in the cell is, and go to that tab.

    I'm getting confused just trying to explain what I need!

    Any help would be great!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: Using a cell value to look at a specific tab for data

    Try:

    =INDIRECT("'"&A1&"'!$L$2")


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    05-28-2012
    Location
    Shanghai, China
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Using a cell value to look at a specific tab for data

    Solved

    I knew it must be possible, thank you.

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Using a cell value to look at a specific tab for data

    hi ben.harris, thought u were looking for a hyperlink to go to the particular tab too? so i tried to work this out

    I need excel to just read what the text in the cell is, and go to that tab.
    =HYPERLINK(MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1)),FIND("]",CELL("filename",$A$1))-FIND("[",CELL("filename",$A$1))+1)&A1&"!"&"L2","Go")

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: Using a cell value to look at a specific tab for data

    You're welcome. Thanks for the rep.

+ 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