Refer to Sheet Name in Cell Formula

8
690

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