Share via

Dynamically refernce a worksheet name based on a cell value to retrieve data from the worksheet.

Anonymous
2011-11-28T17:44:06+00:00

I am trying to dynamically reference a worksheet name based on a value in a cell. For example I have a workbook of 25 worksheets which are company names Gap, Nordstrom, Best Buy, Staples, etc. I am aggregating data on a worksheet within the workbook using vlookup. My formula is =vlookup(b3,Gap!$a$3:$at$6, etc, etc). Within the worksheet I have the list of company names in column A. Gap is in cell A5. What I would like to do is have the spreadsheet name reference a cell so for the previous Gap example something like  =vlookup(b3,A5!$a$3:$at$6, etc, etc), where A5! reprsents the cell that Gap is typed in but references (retrives data) from the Gap worksheet within the workbook.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-11-28T18:57:48+00:00

    Use the INDIRECT function to refer to the cell with sheetname.

    Assumes Gap is in A5

    =VLOOKUP(B$3,INDIRECT("'"&A5&"'!$A$3:$AT$6"),9,FALSE)

    I'm only guessing what etc, etc means.  Adjust to suit.

    Gord

    5 people found this answer helpful.
    0 comments
  2. Anonymous
    2011-11-28T18:39:44+00:00

    I think this does what you are asking for...

    =VLOOKUP(B3,INDIRECT(A5&"!a3:at6"),etc,etc.)

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2011-12-07T22:37:16+00:00

    I am trying to expand on the above formula now to incorporate other workbooks as well. So from the above example lets stick with Gap but now assume that I've moved the Gap spreadsheet to a different workbook named Retail.  In my current workbook in cell A5 I have typed Gap and want it to reference the workbook Retail without losing the functionality of the indirect function in the above formula.  Does this make sense?   Thank you.

    0 comments
  4. Anonymous
    2011-11-28T19:16:39+00:00

    Thank you very much, worked like a charm.

    0 comments