Rifat Hassan
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.
=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.
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.
='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.
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
⚡ 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.
- Close the VBA window >> enter the function Active_Work_Sheet_Name() to get all the sheet names.
=Active_Work_Sheet_Name()
- 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”.
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.
=[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.
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 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.
=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.
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.
