How to Insert Excel Sheet Name from Cell Value (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In many cases, you may need to use a sheet name from a particular cell value such as creating a sheet name from a cell value, referring to a sheet name from a cell value, and so on. In this article, I’ll introduce you to three ways to insert an Excel sheet name from cell value with multiple examples.


How to Insert Excel Sheet Name from Cell Value: 3 Easy Ways

This article will demonstrate how to insert Excel sheet names from cell values by combining MID, CELL, and FIND functions, using the INDIRECT function to add cell values, and applying VBA code.


1. Combining MID, CELL, and FIND Functions to Insert Excel Sheet Name from Cell Value

By using MID, CELL, and FIND functions altogether, you can insert the Excel sheet name as the cell value. Consider the following dataset. Here, we want to insert the Excel sheet name Mark as the salesman’s name.

Steps:

  • First, select cell B5.
  • Then, type the formula in cell B5.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

Combining MID, CELL, and FIND Functions to Insert Excel Sheet Name from Cell Value

Showing Results for Combining MID, CELL, and FIND Functions

Showing Results for Combining MID, CELL, and FIND Functions


2. Using INDIRECT Function to Add Excel Sheet Name from Cell Value

By using the INDIRECT function you can refer to any Excel sheet whose name is inserted as a cell value and extract any particular cell value from that Excel sheet to your current sheet.

Consider the following dataset. Here we want to know the number of laptops sold by different salesmen. We have different sheets named according to the salesmen. The sheet names are inserted in cells B6 and B7. In each sheet, we have the number of different items sold by that particular salesman. Now 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 type the formula.
=INDIRECT(B5&"!D5")

Using INDIRECT Function to Add Excel Sheet Name from Cell Value

  • After that, 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.

Showing Results for Using INDIRECT Function


3. Applying VBA Code to Insert Sheet Name from Cell Value in Excel

We can set a sheet name from any cell value by using Visual Basic Application (VBA). Consider the following dataset. Here we will name the sheet as the name of the salesman in cell B5.

Sample Data Set for Applying VBA Code to Insert Sheet Name from Cell Value in Excel

Steps:

  • Right-click on the sheet name from the sheet name tab and select View Code.

Applying View Code Option to Insert Sheet Name from Cell Value in Excel

  • A new window named Visual Basic for Applications will appear. Type 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
  • Then, save the window and close it.

Applying VBA Code to Insert Sheet Name from Cell Value in Excel

  • After that, the sheet name will be changed to the value of cell B5.

Showing Results for Applying VBA Code


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Conclusion

I hope you are now capable of using sheet names from cell values. If you have any confusion please leave a comment, so I’ll try my best to remove your confusion.


Related Articles


<< Go Back to Excel Sheet Name | Excel Worksheets | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

2 Comments
  1. 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.
      Data set
      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.
      Create Table
      ● Now, go to Cell E5 and put in the following formula.

      =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
      Formula to get Sheet name

      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)

      Use of INDEX MATCH functions
      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)
      Use of Date Function

      ● Fill up the Date and Attendance columns.
      Fill Up two columns

      ● Now, change the sheet name from January to February and look at the changes that take place in the Date column.
      Change sheet name

      Download File:
      Solution.xlsx
      Regards.
      – Alok Paul
      Author at ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo