How to Reference Worksheet Name in Formula in Excel

Oftentimes, while working with Microsoft Excel, we may need to insert data from another Excel worksheet. Sounds complex, right? Wrong! In this article, we’ll demonstrate 4 ways to reference the name of a worksheet in an Excel formula. In addition, we’ll also learn to obtain the name of the active worksheet and reference another worksheet based on value.

How to Reference Worksheet Name in Excel Formula: 4 Creative Ways

First and foremost, let’s consider the Sales Data for January in the “January” worksheet, which depicts the “Product Name” and the “Sales” in USD.

In a similar manner, we have the Sales Data for February in the “February” worksheet. Here, we want to obtain the “Total Sales” by pulling in the data from the “January and February” worksheets. So, without further delay, let’s explore how to reference a worksheet name in an Excel formula.

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.

1. Reference Worksheet Name Without Spaces or Punctuation Characters

First of all, we’ll demonstrate the case where the worksheet names have no spaces or punctuation characters between them.

📌 Steps:

• At the very beginning, go to the C5 cell >> enter the formula given below.

Here, “January and February” refer to the worksheet names, and the C5 cell corresponds to the “Desktop Sales” in these two months.

2. Reference Worksheet Name with Spaces or Punctuation Characters

Alternatively, this method considers the case where there are spaces or punctuation characters in between the names of the worksheets. In this case, let’s assume the “January and February Sales” worksheets. Now we want to extract the “Sales” data from the two worksheets and return the “Total Sales” in another worksheet.

📌 Steps:

• To begin with, move to the C5 cell >> type in the expression below.

In this case, “January Sales” and “February Sales” represent the worksheet names; in contrast, the C5 cell indicates “Desktop Sales” respectively.

3. Reference Dynamically Cell in Another Sheet

In truth, what we’ve done so far is nice, but there is a major problem. Simply put, if there are numerous worksheets, then we have to manually enter their names. Don’t despair! We can automate this repetitive task using VBA Code and the INDIRECT function which returns the cell reference of a string.

📌 Steps:

• First, navigate to the Developer tab >> click the Visual Basic button.

Now, this opens the Visual Basic Editor in a new window.

• Second, go to the Insert tab >> select Module.

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

Function Active_Work_Sheet_Name()

Dim wbk As Workbook
Set wbk = ActiveWorkbook
Dim result As Variant
Dim j As Variant

j = wbk.Sheets.Count - 1
ReDim result(j, 0)

Dim k As Variant
For k = 0 To j
    result(k, 0) = wbk.Sheets(k + 1).Name
Next k
Active_Work_Sheet_Name = result
    
End Function

• Close the VBA window >> enter the function Active_Work_Sheet_Name() to get all the sheet names.

• Finally, navigate to the C5 cell >> insert the equation into the Formula Bar.

For instance, the E5 and E6 cells point to the worksheet names “January and February” while the C5 cell refers to their corresponding “Sales”.

Read More: How to Use Sheet Name in Dynamic Formula in Excel

4. Create Reference to Another Workbook

For one thing, we can create a reference to other workbooks (source workbooks) to bring data into a different workbook (destination workbook). Therefore, just follow along.

📌 Steps:

• To begin with, copy and paste the formula below into the C5 cell.

For example, “[Referencing_Worksheet_Name_in_Excel_Formula.xlsx]” is the workbook name that contains the “January” worksheet. Moreover, the C5 cell indicates the “Sales” values.

How to Get the Name of the Active Worksheet in Excel

Conversely, we can extract the name of the active worksheet by combining the MID, FIND, and CELL functions. It’s simple and easy, so let’s see the process in detail.

📌 Steps:

• To start with, click the B5 cell >> insert the following equation.

How to Reference Another Sheet Based on Cell Value in Excel

Last but not least, we can also refer to another Excel worksheet based on a cell value. On this occasion, let’s consider the PC and Accessories Sales Data which shows the “Product” name, the “Sales in January”, and the “Sales in February” respectively.

📌 Steps:

• In the first place, proceed to the Data tab >> click on Data Validation >> then follow the steps shown in the GIF given below.

• Afterward, jump to the B7 cell >> use the VLOOKUP function as shown below >> compute the “Total Sales” using the SUM function.

In this case, the C4 cell is the chosen “Item” from the drop-down list.

Besides, you can follow the steps in real time by referring to the animated GIF shown below.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

As a note, the “Dynamic Worksheet Reference.xlsx” and the “Referencing from Another Workbook.xlsx” files are used in Method 3 and Method 4. In contrast, the “Referencing Worksheet Name in Excel Formula.xlsx” contains the rest of the methods.

Download Practice Workbook

Conclusion

In short, this tutorial explores all the ins and outs of how to reference a worksheet name in an Excel formula. Now, we hope all the methods mentioned above will prompt you to apply them to your Excel spreadsheets more effectively. Furthermore, if you have any questions or feedback, please let me know in the comment section.

<< Go Back to Reference to Another Sheet | Cell Reference in Excel | Excel Formulas | Learn Excel