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.


Download Practice Workbook


4 Ways to Reference Worksheet Name in Excel Formula

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.

January sales dataset for excel reference worksheet name in formula

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.

February Sales dataset for excel reference worksheet name in 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.

=January!C5+February!C5

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

Reference Worksheet Name Without Spaces or Punctuation Characters in excel reference worksheet name in formula

Read More: How to Link Excel Sheets to Another Sheet (5 Ways)


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.

Reference Worksheet Name with Spaces or Punctuation Characters

📌 Steps:

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

='January Sales'!C5+'February Sales'!C5

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

excel reference worksheet name in formula

Read More: Reference from Another Excel Workbook without Opening (5 Examples)


Similar Readings


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.

Reference Dynamically Cell in Another Sheet with VBA code

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

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

Inserting 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

Inserting VBA code

Code Breakdown:

Here, we’ll explain the VBA code to generate the worksheet names.

  • In the first portion, the sub-routine is given a name, here it is Active_Work_Sheet_Name().
  • Next, define the variables wbk, result, j, and k and assign the data type Workbook and Variant respectively.
  • In the second portion, use the Count property to count the number of sheets and a For Loop to iterate through all the sheets in the workbook.

VBA Code explanation

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

=Active_Work_Sheet_Name()

Using User defined function

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

=INDIRECT("'"&E5&"'!C5")+INDIRECT("'"&E6&"'!C5")

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

Using VBA code to excel reference worksheet name in formula

Read More: Transfer Specific Data from One Worksheet to Another for Reports


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.

=[Referencing_Worksheet_Name_in_Excel_Formula.xlsx]January!C5+[Referencing_Worksheet_Name_in_Excel_Formula.xlsx]February!C5

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.

Create Reference to Another Workbook


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.

=MID(CELL("filename",B5),(FIND("]",CELL("filename",B5))+1),45)

Formula Breakdown:

  • CELL(“filename”,B5) → returns information about the formatting, location of the cell contents. Here, the “filename” is the info_type argument which returns the file name and location. Next, the B5 cell is the optional reference argument where the result is returned.
  • FIND(“]”,CELL(“filename”,B5)) → returns the starting position of one text string within another text string. Here, “]” is the find_text argument while CELL(“filename”,B5) is the within_text argument. Here, the FIND function returns the position of the square brace within the string of text.
    • Output → 103
  • MID(CELL(“filename”,B5),(FIND(“]”,CELL(“filename”,B5))+1),45) becomes
    • MID(CELL(“filename”,B5),(103+1),45)  returns the characters from the middle of a text string, given the starting position and length. Here, the CELL(“filename”,B5) is the text argument, (103+1) is the start_num argument, and 45 is the num_chars argument which represents the maximum number of characters in the worksheet name.
    •  Output → “Active Sheet Name”

How to Get the Name of the Active Worksheet in Excel


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.

How to Reference Another Sheet Based on Cell Value in Excel

📌 Steps:

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

Data Validation steps

=VLOOKUP(C4,'Sales Data'!B5:D13,2,FALSE)

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

Formula Breakdown:

  • VLOOKUP(C4,’Sales Data’!B5:D13,2,FALSE) → looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify. Here, C4 ( lookup_value argument) is mapped from the ‘Sales Data’!B5:D13 (table_array argument) which is the “Sales Data” worksheet. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
    • Output → $1090

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

Using VLOOKUP function


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.

Practice Section

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.

Excel files


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. Or you can check out our other articles related to Excel functions on ExcelDemy.


Related Articles

Rifat Hassan

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 the 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