How to Use Cell Value as Worksheet Name in Formula Reference in Excel

If you are trying to use cell value as worksheet name in formula reference in Excel, then this article may be helpful for this purpose. So, let’s start with our main article to explore more about the usage of a cell value as a worksheet name.

How to Use Cell Value as Worksheet Name in Formula Reference in Excel: 3 Ways

Here, we have 3 worksheets January, February, and March containing the sales records of these 3 months for different products. So, we will try to use cell values as these worksheet names in a formula as a reference to extract the values in a new sheet.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

Method-1: Using INDIRECT Function to Use Cell Value as Worksheet Name in Formula Reference

Here, we can see we have the total sales value in cell D11 in each of the three sheets January, February, March.

We have gathered the sheet names as cell values in a new sheet to use these values as reference. Using the INDIRECT function we will use these values as worksheet names in a formula and the advantage is that it will create a dynamic reference. So, for changing, adding, or deleting these cell values the result will be automatically updated.

Steps:
➤ Type the following formula in cell C4

Here, B4 is the sheet name January and D11 is the cell in that sheet that contains the total sales value.

➤ Press ENTER and drag down the Fill Handle tool.

After that, you will get the total sales values corresponding to the sheet name references in the Sheet Name column.

Read More: How to Find and Replace Cell Reference in Excel Formula

Method-2: Using INDIRECT and ADDRESS Functions to Use Cell Value as Worksheet Name

In the three sheets January, February, and March we have some records of sales for these months for different products.

For making a summary table where we will extract the sales values from those sheets and combine them in the January, February, and March columns. To use the sheet name reference here we will use the headers of these columns and with the help of the INDIRECT function and the ADDRESS function, we will summarise them.

Steps:
➤ Type the following formula in cell C4

Here, $C$3 is the name of the worksheet.

➤ Press ENTER, drag down the Fill Handle Tool.

Then, you will get the sales record of January month from the January sheet in the January column.

For having the sales values from the February sheet for this month in the February column use the following formula

Here, $D$3 is the name of the worksheet.

Similarly, for the sales records of March use the following formula

Here, $E$3 is the name of the worksheet.

Read More: How to Use OFFSET for Cell Reference in Excel

Method-3: Using VBA Code to Use Cell Value as Worksheet Name in Formula Reference

Here, we have the total sales value in cell D11 in each of the three sheets January, February, March containing the sales records of January, February, and March.

In the Sheet Name column, we have put down the sheet names as cell values to use them as references in a VBA code. With the help of this code, we will get the total sales values from these sheets and gather them in the Total Sales column corresponding to their sheet names.

Steps:
➤ Go to the Developer Tab >> Visual Basic Option.

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

After that, a Module will be created.

➤ Write the following code

Sub sheetreference()
Dim SheetR As String, ws As Worksheet, ws1 As Worksheet
Set ws = Worksheets("VBA")

For i = 4 To 6
SheetR = ws.Cells(i, 2).Value
Set ws1 = Sheets(SheetR)
ws.Cells(i, 3).Value = ws1.Range("D11").Value
Next i

End Sub

Here, we have declared SheetR as String, ws, and ws1 as Worksheet, ws will be assigned to the worksheet VBA where we will have our output. SheetR will store the cell values with sheet names in the VBA sheet. Then, we have assigned the sheets January, February, and March to the variable ws1.

The FOR loop will extract the total sales values from each sheet to the VBA sheet and here we have declared the range for this loop as 4 to 6 because the values start from Row 4 in the VBA sheet.

➤ Press F5.
Finally, you will get the total sales values corresponding to the sheet name references in the Sheet Name column.

Read More: How to Reference a Cell from a Different Worksheet in Excel

Typing the Worksheet Name for Using Reference in a Formula

If you don’t want to use the above methods to reference a cell value as a sheet name, then you can simply type the sheet name or manually select it to get the values from that sheet easily.

Here, we will extract the total sales values from the sheets January, February, and March, and gather them in the Total Sales column in a new sheet.

For having the total sales value of the January month type the following formula in cell C4

Here, January is the sheet name and D11 is the total sales value in that sheet.

Similarly, for the sales value of the February month use the following formula

Here, February is the sheet name and D11 is the total sales value in that sheet.

If you don’t want to type any formula, you can just select the cell of the March sheet to extract that value in cell C6.

➤ Firstly, type Equal sign (=) in cell C6.
➤ Click on the March sheet.

Then, you will be taken to the March sheet, and from here select the cell D11.

➤ Press ENTER.
You will get the total sales value of the March month from that sheet in cell C6 in the Type sheet.

Read More: How to Reference Cell in Another Sheet Dynamically in Excel

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, we tried to cover the ways to use cell value as worksheet name in formula reference in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• How to Display Text from Another Cell in Excel 
• How to Reference Text in Another Cell in Excel 
• How to Use Variable Row Number as Cell Reference in Excel
• How to Reference Cell by Row and Column Number in Excel 
• How to Reference a Cell from a Different Worksheet in Excel
• Excel VBA Examples with Cell Reference by Row and Column Number
• Excel VBA: Cell Reference in Another Sheet 

<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel