Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# 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.

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

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

`=INDIRECT("'"&B4&"'"&"!"&"D11")`

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

• “‘”&B4&”‘”&”!”&”D11″ → & operator will join the cell value of B4 with inverted commas, exclamatory sign, and the cell reference D11
Output → “‘January’!D11”
• INDIRECT(“‘”&B4&”‘”&”!”&”D11″) becomes
INDIRECT(“‘January’!D11”)
Output → \$23,084.00 ➤ 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. ### 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

`=INDIRECT("'"&\$C\$3&"'"&"!"& ADDRESS(ROW(D4),COLUMN(D4)))`

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

• ROW(D4) → returns the row number of the cell D4
Output → 4
• COLUMN(D4) → returns the column number of the cell D4
Output → 4
Output → \$D\$4
INDIRECT(“‘January’!”&”\$D\$4”) INDIRECT(“January!\$D\$4”)
Output →\$4,629.00 ➤ 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

`=INDIRECT("'"&\$D\$3&"'"&"!"& ADDRESS(ROW(D4),COLUMN(D4)))`

Here, \$D\$3 is the name of the worksheet. Similarly, for the sales records of March use the following formula

`=INDIRECT("'"&\$E\$3&"'"&"!"& ADDRESS(ROW(D4),COLUMN(D4)))`

Here, \$E\$3 is the name of the worksheet. ### 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. ## 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

`=January!D11`

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

`=February!D11`

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. ## 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. ## 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 #### Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts 