+ 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
    37,276

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

    Try:

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


    Regards, TMS

  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,142

    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")

  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
    37,276

    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