Home / Formulas / Refer to Sheet Name in Cell Formula

Refer to Sheet Name in Cell Formula


The ExcelHelpDesk Support team received the following request for help

Problem – Inlcude Sheet Name to Cell Formula

ANTWERPEN PASWOORD
11001 =IF($A4>0;’11001’!$I$6;0)
=A4+1 =IF($A5>0;’11002’!$I$6;0)
‘11001’ refers to another sheet in the workbook which is indicated in column A.
I would like to insert the cell content from column A 11001 between ‘ ‘ automatically in the formule in column B.

For this problem we need to provide a formula that will automatically include the Sheet Name to each successive row of the worksheet. This will make extending the worksheet much simpler and will remove the need to manually including the worksheet name each time a reference is required for a formula.


Use INDIRECT to create a reference to another Worksheet

A reference to another sheet always includes the sheet name (Sheet1), an exclamation (!), and the cell reference (I6). In this case we want the sheet name to be a reference from another cell in the worksheet and that whole reference to then be calculated.

In each case the end of the reference is to the same cell “$I$6″ which is the location of the Password in each worksheet. Then in front of that reference we want to insert the worksheet name. We need to get a combined string with the worksheet name and the cell reference, to acheive this we can use concatenation.

So we have a concatenated text string using ampersand to result in
=A4&”!$I$6″

This tells Excel to take what’s in A4 (the text “11001”), and concatenate it to the string “!$I$6″. We then end up with the string:
=”11001!$I$6″

Now this is merely a text string to Excel, and we want to treat it as a real reference, we need to add on the INDIRECT function:
=INDIRECT(A4&”!$I$6″)

This returns the contents of I6 on Worksheet 11001. This formula can now be dragged down to the other cells in the column to automatically include the Worksheet name as it changes in each row.

Here is the worksheet screenshot demonstrating the calculation that is required.

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment

About Excel Help Desk

Check Also

INDIRECT to extract a value from another worksheet

You can see that we have the Worksheet name in Column A and the value that is needed to be extracted in Column D. The supplied worksheet included a manually entered formula to pull through the value from the other worksheet. We will now demonstrate how to create a formula using the INDIRECT function that will automatically pull through values from other worksheets without manually entering each worksheet name and cell reference.

8 comments

  1. Many thanks. Exactly what I was looking for.

  2. Hi,
    I am trying to use the indirect function as follows:
    INDIRECT($B9&”!U44″) . its OK that it refers to the sheet and cell U44. But if I drag or copy it to the right cell, it still refers to cell U44 while I expected it to refer to cell V44.
    Can you help?

  3. Hi! I want to use cells to reference worksheets in another workbook. I’ve tried
    =INDIRECT(“‘” & N4 & N5 & “‘” & “!$D$35″)
    and a few variations of that, where:

    N4 = workbook name (already in [ ]): [Nuwe Hino.xls]

    N5 = worksheet name: Okt ’12

    but i keep getting #REF.
    I’ve also tried to concatenate the workbook and worksheet names and refer to that result, but that didn’t work either.
    Ideally I’d also like to have the cell address as a cell reference (eg. N6), but that seems to cause worse problems?

    • Leandri,
      Can you send (support@excelhelpdesk.com) us a copy of the workbook that you are trying to develop this formula in.
      We will then investigate and provide a solution
      Regards
      ExcelHelpDesk

  4. Hi Can we use the same logic INDIRECT function in Vlookup to automate the sheet name and fetch the data.

    • Krishna,
      Please give us the formula you would suggest and some details on how you would implement this

  5. Many thanks for your assistance
    Johan