Method 1 – Combining MID, CELL, and FIND Functions to Insert the Sheet Name into Cell Value
Consider the following dataset. We want to insert the Excel sheet name Mark as the salesman’s name.
Steps:
- Select cell B5.
- Insert the formula in cell B5.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
- By pressing Enter, you will get the sheet name as the cell value.
- If you rename the Sheet, your cell value will automatically change.
Method 2 – Using the INDIRECT Function to Add Excel Sheet Name to Cell Value
We want to know the number of laptops sold by different salespeople. We have different sheets, each referring to a salesperson and containing their sales record. The sheet names are inserted in cells B6 and B7. We will extract the number of laptops sold from different Excel sheets using this Excel sheet name as cell values.
Steps:
- Choose cell C5 and insert the formula.
=INDIRECT(B5&"!D5")
- By pressing Enter, you will get the value of cell D5 from the sheet named John.
- In a similar way, you can get the value for the sheet named Antony.
Method 3 – Applying VBA Code to Insert a Sheet Name from Cell Value in Excel
We will name the sheet as the name of the salesperson in cell B5.
Steps:
- Right-click on the sheet name from the sheet name tab and select View Code.
- A new window named Visual Basic for Applications will appear. Insert the following code in this window.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("B6")
If Target = " " Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub- Save the window and close it.
- The sheet name will be changed to the value of cell B5.
Download the Practice Workbook
Related Articles
- How to Get Excel Sheet Name
- How to List Sheet Name in Excel
- How to Rename Sheet in Excel
- How to Use Sheet Name Code in Excel
- How to Search by Sheet Name in Excel Workbook
<< Go Back to Excel Sheet Name | Excel Worksheets | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi! I would like to have a month in sheets, would it be possible to have all the sheets somehow linked so I would have an easier time to change the dates than manually double click every sheet whenever I need to change the month?
Hi MIRA,
It’s a great pleasure that you are watching our articles. You have a query regarding this article. You want to make an Excel sheet, where the dates of the Excel sheet will change according to the sheet name. Yes, you can do this. Read the below steps to get your solution.
Steps:
● First, take an Excel sheet with the Date and Attendance columns.
We will insert dates in the Date column based on the Sheet Name.
● Before that, we will form a table that consists of the month’s name and serial number.
● Now, go to Cell E5 and put in the following formula.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)It will extract the name of the Sheet which is a month name.
Formula Explanation:
● CELL(“filename”,A1)
This finds the location of the Excel file with the Sheet name.
Result:
D:\Alok\[Excel-Sheet-Name-From-Cell-Value.xlsx]January
● FIND(“]”,CELL(“filename”,A1))
This will find out the location of the symbol mentioned in the formula from the location.
Result: 47
● FIND(“]”,CELL(“filename”,A1))+1
Add 1 with the previous result.
Result: 48
● MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256)
Separate the sheet name from the location.
Result: January
● After that, put the following formula on Cell F5.
=INDEX(Month,MATCH(E5,Month[Month],0),2)
This returns the serial number of the month comparing the values of the table.
● Now, use the DATE function on Cell B5.
=DATE(2022,$F$5,1)
● Fill up the Date and Attendance columns.
● Now, change the sheet name from January to February and look at the changes that take place in the Date column.
Download File:
Solution.xlsx
Regards.
– Alok Paul
Author at ExcelDemy