How to Reference Worksheet Name in Formula in Excel (3 Easy Ways)

Reference Worksheet Name in Formula in Excel Quick View

Today I will be showing how to reference a worksheet name in formula in Excel.

We may know how to work with more than one worksheet in Excel. But sometimes while working with more than one worksheet, we have to reference one worksheet name in a formula of another worksheet.

Today I will be showing how you can execute this.


Reference Worksheet Name in Formula (Quick View)

Reference Worksheet Name in Formula in Excel Quick View


Download Practice Workbook


How to Reference Worksheet Name in Formula in Excel


1. Reference Worksheet Name without Spaces or Punctuation Characters

If there is no space or punctuation mark in the worksheet name, you can reference it using only its name along with an Exclamatory Sign (!)

Or you can reference it using its name enclosed within an Apostrophe (‘’) along with an Exclamatory sign (!).

The Syntax is:

=Worksheet_Name!

Or

='Worksheet_Name'!

Here we’ve got a workbook with two worksheets, namely “January” and “February”.

They do not have any space or punctuation marks in their names.

Data Set to Reference Worksheet Name in Excel

Data Set to Reference Worksheet Name in Excel

Now we want to make a total sales report by making a total of sales of each product from both worksheets.

We have created a new worksheet called “Total” for this purpose.

As there is no space or punctuation mark in the worksheet name “January”, so to access the sales of Desktop in January, we have to use:

=January!C4 (Or you can use 'January'!C4)

Similarly, to access the sales of Desktop in February, we have to use:

=February!C4 (Or you can use 'February'!C4)

Therefore, the formula to find out the total sales of Desktop in two months will be:

=January!C4+February!C4

(Or you can use 'January'!C4+'February'!C4)

Reference Worksheet Name without Space in Formula in Excel

Then you can drag the Fill Handle to do the same for the rest of the products.


2. Reference Worksheet Name with Spaces or Punctuation Characters

If you have any space or punctuation character in the name of any worksheet, you must reference it using its name enclosed within an Apostrophe (‘’) along with an Exclamatory sign (!).

The Syntax is:

='Worksheet Name'!

Here we’ve got another workbook with two worksheets, “Week 1” and “Week 2”.

Data Set for Reference Worksheet Name in Formula in Excel

Data Set for Reference Worksheet Name in Formula in Excel

We have created another workbook called “Total” to find out the total sales of each product.

As there is a space in the worksheet name “Week 1”, so to access the sales of Desktop in Week w, we have to use:

='Week 1'!

Similarly, to access the sales of Desktop in Week 2, we have to use:

='Week 2'!

Therefore, the formula to find out the total sales of Desktop in two weeks will be:

='Week 1'!C4+'Week 2'!C4

Reference Worksheet Name with Space in Excel

Now, you can drag the Fill Handle to copy the formula to the rest of the cells.


3. Dynamic Worksheet Reference

Up till now, we have seen how to reference any worksheet name in a formula in Excel.

Now, think for a moment that you have created a bunch of worksheets like January, February, etc., and inserted your formulas with these names.

But somehow for any reason, you had to change the worksheet names to Month 1, Month 2, etc.

What will you do now? You have to manually change all the formulas again.

To avoid these types of mishappenings, you can create a dynamic worksheet reference in your worksheet.

Now, what is a dynamic worksheet reference?

A dynamic worksheet reference is a formula that returns the names of one or more worksheets of the workbook.

If any of the worksheet names are changed, it will automatically return the changed worksheet names.

We will accomplish this in two steps.

  • Will return all the worksheet names using a VBA function first.
  • Then will use the required worksheet references in the formula using the INDIRECT function.

Step 1: Returning All the Worksheet Names Using VBA

Create a new module in the VBA window and insert this code:

Code:

Function SHEET_NAMES()

Dim mainworkBook As Workbook
Set mainworkBook = ActiveWorkbook

Dim out As Variant

Dim x As Variant
x = mainworkBook.Sheets.Count - 1

ReDim out(x, 0)

Dim i As Variant
For i = 0 To x
    out(i, 0) = mainworkBook.Sheets(i + 1).Name
Next i

SHEET_NAMES = out

End Function
VBA Code to Generate All Worksheet Names
(Click here to see how to save VBA codes in Excel.)

This code creates a function called SHEET_NAME that returns the names of all the worksheets of your workbook as an array.

Enter this function in any cell of your workbook.

=SHEET_NAMES()

All Worksheets Names Returned by VBA Function in Excel

Now we will use these names in formulas using the INDIRECT function.


Step 2: Using the Worksheet Names in Formula with the INDIRECT Function

An INDIRECT Function turns a string into a cell reference.

Therefore, to use the cell reference ‘Week 1’!C4, we have to simply insert:

=INDIRECT("'Week 1'!C4") 

But we won’t insert the worksheet name “Week 1” directly, we will take it from the SHEET_NAMES output.

So our formula will be:

=INDIRECT("'"&E4&"'!C4")
[Here E4 is the cell reference that contains the first worksheet name returned by the SHEET_NAMES function].

Reference Worksheet Name in Excel Using INDIRECT Function

See, we’ve got the value from the cell C4 of the worksheet Week 1, $250,000.00

Now if you change the worksheet name to anything from “Week 1”, you will see that the formula has adjusted itself. You needn’t do anything.

Dynamic Reference Worksheet Name in Formula in Excel

See, we have changed the worksheet name from “Week 1” to “Sheet1”, and it has automatically adjusted itself.

No change in the output.

Therefore, the formula to calculate the total sales of Desktop (C4) in two weeks will be:

=INDIRECT("'"&E4&"'!C4")+INDIRECT("'"&E5&"'!C4")

Excel Reference Worksheet Name Using INDIRECT Function

You can do the same for the rest of the products.


Frequently Asked Question

How to get the name of the active worksheet in a cell using a formula?

Solution

To get the name of the active worksheet, you can enter this formula in any cell of your worksheet:

=MID(CELL("filename",A1),(FIND("]",CELL("filename",A1))+1),31)

Getting the Active Worksheet Name Using Formula

Note: Here in place of A1, you can use any cell reference.


Conclusion

Using these methods, you can reference any worksheet name in a formula in both non-dynamic and dynamic ways. Do you have any questions? Feel free to ask us.


Further Readings

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo