How do I reference an Excel sheet name in an Excel formula?

1

5 Answers

How do I reference an excel sheet name in an excel formula?

Use the following syntax:

SheetName!CellAddress 

Notes:

• The worksheet name comes before the cell address, followed by an exclamation mark !.
• If the worksheet name includes spaces, enclose it in single quotation marks '.

Example:

'Sheet Name with spaces'!CellAddress 

---

Further reading

• Referencing cells outside the worksheet

1

If your sheetname includes spaces, you must use single quotes:

='My Sheet'!B3

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))

2

You simply use

=Sheet2!C25

In this case, I'm referencing Sheet2, cell C25

If I had named the sheet something like DataSheet, I would use

=DataSheet!A1 

The above would access the A1 cell of DataSheet

So, if my DataSheet A1 had the value 5, and my Sheet1 A1 had the value 15 , then on my ResultsWorkSheet I could have

=DataSheet!A1 + Sheet1!A1 

or

=SUM(DataSheet!A1,Sheet1!A1)

The INDIRECT function is very helpful here as well.

Here's one I just used:

=RIGHT(INDIRECT("'"&PROPER(MID(A5,SEARCH(":",A5)+2,LEN(A5)-10))&"'"&"!$A$3"),12)

I needed to pull a phone number off the end of a string in a cell that was in a set location on a large number of different worksheets in a workbook.

INDIRECT basically allows you to use formulas to define dynamic workbook and/or worksheet references. The best rule of thumb to keep in mind is that you want your INDIRECT function to output precisely, character for character, the syntax:

='[workbook.xlsx]WORKSHEET'!CellAddress

In other words

=INDIRECT("'["&workbook&"]"&sheet&"'!"&ref)

Replacing any of those three references with formulas, but keeping the surrounding characters.

4