How to Use Excel Sheet Name From Cell Value (Three Ways)

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

Download Practice Workbook

Three Ways to Use Excel Sheet Name From Cell Value

1. Using MID, CELL and FIND Function

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 name in cell B6.

Excel sheet dataset

Type the formula in cell B6,

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

Excel sheet name from cell value

After pressing ENTER,  you will get the Excel sheet name as the cell value.

MID, FIND, AND CELL

If you change the Sheet Name your cell value will automatically change.

MID, FIND AND CELL

2.   Using INDIRECT Function

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 quantity of different items sold by that particular salesman.  Now we will extract the number of laptops sold from different Excel sheet using this Excel sheet name as cell values.

INDIRECT DATASET

Now type the formula in cell C6,

=INDIRECT(B6&"!D6")

Excel sheet name from cell value

After pressing Enter, you will get the Value of cell D6 from the sheet named “Jhon”

Excel sheet name from cell value

In the similar way, you can get the value for the sheet named “Antony

INDIRECT FUNCTION

3.   Sheet Name from Cell Value With VBA

We can create an Excel sheet Name from any cell value by using Visual Basic Application (VBA). Consider the following dataset. Here we will name the Excel sheet as the name of the salesman in cell B6.

DATASET VBA

First, Right click on the sheet name from the sheet name tab  and select View Code.

Opening VBA

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

Excel sheet name from cell value

Save the window and close it.

Saving VBA

After that, the Excel sheet name will be changed as cell value of B6.

CHANGE SHEET NAME FROM CELL VALUE USING VBA

Conclusion

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

Prantick

Hi, I'm prantick bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo