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.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
3 Easy Ways to Insert Excel Sheet Name from Cell Value
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.
Method 1: Combining MID, CELL, and FIND Functions to Insert Excel Sheet Name from Cell Value
By using the MID function, the CELL function, and the FIND function 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.
- Firstly, select cell B5.
- Secondly, type the formula in cell B5.
- After that, by pressing ENTER, you will get the sheet name as the cell value.
- Then, if you change the Sheet Name your cell value will automatically change.
Read More: How to Get Excel Sheet Name (2 Methods)
Method 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.
- Firstly, choose cell C5.
- Secondly, type the formula in the C5 cell.
- After that, by pressing Enter, you will get the value of cell D5 from the sheet named “John”.
- Therefore, in a similar way, you can get the value for the sheet named “Antony”.
- How to Search Sheet Name in Excel Workbook (2 Methods)
- Apply Sheet Name Code in Footer in Excel (3 Ways)
Method 3: Applying VBA Code to Insert Sheet Name from Cell Value in Excel
We can create 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.
- Firstly, right-click on the sheet name from the sheet name tab and select View Code.
- Secondly, a new window named Microsoft 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.
- After that, the sheet name will be changed to the cell value of B5.
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.