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)
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:
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.
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:
(Or you can use
Similarly, to access the sales of Desktop in February, we have to use:
(Or you can use
Therefore, the formula to find out the total sales of Desktop in two months will be:
(Or you can use
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:
Here we’ve got another workbook with two worksheets, “Week 1” and “Week 2”.
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:
Similarly, to access the sales of Desktop in Week 2, we have to use:
Therefore, the formula to find out the total sales of Desktop in two weeks will be:
='Week 1'!C4+'Week 2'!C4
Now, you can drag the Fill Handle to copy the formula to the rest of the cells.
- How to Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)
- How to Link Files in Excel (5 Different Approaches)
- Link Excel Workbooks for Automatic Update (5 Methods)
- How to Link Word Document to Excel (2 Easy Methods)
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:
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
(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.
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:
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:
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.
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:
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?
To get the name of the active worksheet, you can enter this formula in any cell of your worksheet:
Note: Here in place of A1, you can use any cell reference.
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.