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.

Download Workbook


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.

Excel use cell value as worksheet name in formula reference

Excel use cell value as worksheet name in formula reference

Excel use cell value as worksheet name in formula reference

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.

Excel use cell value as worksheet name in formula reference

Excel use cell value as worksheet name in formula reference

INDIRECT function

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.

INDIRECT function

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

INDIRECT function

➤ Press ENTER and drag down the Fill Handle tool.

INDIRECT function

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

INDIRECT function


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.

Excel use cell value as worksheet name in formula reference

Excel use cell value as worksheet name in formula reference

Excel use cell value as worksheet name in formula reference

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.

INDIRECT and ADDRESS function

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
  • ADDRESS(ROW(D4),COLUMN(D4)) becomes
    ADDRESS(4,4)
    Output → $D$4
  • INDIRECT(“‘”&$C$3&”‘”&”!”& ADDRESS(ROW(D4),COLUMN(D4))) becomes
    INDIRECT(“‘January’!”&”$D$4”) INDIRECT(“January!$D$4”)
    Output →$4,629.00

INDIRECT and ADDRESS function

➤ Press ENTER, drag down the Fill Handle Tool.

INDIRECT and ADDRESS function

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

INDIRECT and ADDRESS function

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.

INDIRECT and ADDRESS function

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.

INDIRECT and ADDRESS function


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.

Excel use cell value as worksheet name in formula reference

Excel use cell value as worksheet name in formula reference

VBA Code

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.

VBA Code

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

VBA Code

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

VBA Code

After that, a Module will be created.

VBA Code

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

VBA Code

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

Excel use cell value as worksheet name in formula reference


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.

Excel use cell value as worksheet name in formula reference

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.

Typing sheet name

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.

Typing sheet name

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.

Typing sheet name

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

Typing sheet name

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

Typing sheet name


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.

practice


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.

Tanjima Hossain

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

Leave a reply

ExcelDemy
Logo