Forum Discussion

kczyzyk's avatar
kczyzyk
Copper Contributor
Nov 16, 2021
Solved

Reference a cell to grab the sheet name for another cell reference?

I want to use a cell in the current workbook to reference a cell in another workbook.  Essentially I have a lot of copies of the same formatted data and I'm trying to compile a summary. Is there a way to reference a cell instead of typing in something like "=SheetName!$L11", "SheetName" would be found from the value in another cell (ie see screenshot where Reach1 would be the sheet name).  I'm not sure how exactly to ask this question.  I'm sure I could do it in VBA but ideally want to keep it not macro-enabled. Thanks in advance!

 

  • kczyzyk 

    If you mean that you want to reference a cell in another sheet in the same workbook, you can use the INDIRECT function:

     

    =INDIRECT("'"&H2&"'!L11")

     

    This can be filled to the right.

    • HansVogelaar's avatar
      HansVogelaar
      MVP

      MattWaters 

      You can use INDIRECT to refer to a cell in another workbook if that workbook is open in Excel.

      INDIRECT does not work with closed workbooks - it will return #REF!

       

      Let's say you want to refer to cell L11 on a sheet whose name is in H2 in a workbook whose name is in G2. As mentioned above, that workbook should be open.

      =INDIRECT("'["&G2&"]"&H2&"'!L11")

      • MattWaters's avatar
        MattWaters
        Copper Contributor

        Awesome thanks! kczyzyk 
        Is it possible to hard code the workbook name, but reference the sheet name via indirect possible?

        I've tried it along the lines of:
        =INDIRECT('[workbook.xls]"&H2&"'!L11)
        But can only make it work if I reference both the sheet and the workbook withing the indirect function. Trying to get around it not working if the workbook is closed, as the workbook name will always remain constant.

        Hope that makes sense

  • kczyzyk 

    If you mean that you want to reference a cell in another sheet in the same workbook, you can use the INDIRECT function:

     

    =INDIRECT("'"&H2&"'!L11")

     

    This can be filled to the right.

Resources