Function Problems

Use INDIRECT, SUM, and INDEX for Dynamic Excel Sheet Names

Elise WilliamsElise Williams Nov 18, 2026 869 views

Question details

The user needs to sum the Consumption column on a worksheet named in cell B2, even when the column position varies between worksheets.

Sum a Dynamic Excel Sheet Column with INDIRECT and INDEX
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook has multiple worksheets with Consumption headers in different columns, and B2 identifies the sheet to calculate.
Observed behavior
The desired formula should locate the Consumption header on the named worksheet and sum the values beneath it.
Before you start

Check that B2 contains the exact target worksheet name and that the target sheet has a Consumption header in row 1. The example sums rows 2 through 100 and searches columns A through Z; adjust these ranges to fit your data.

Solution 1Recommended

Sum the Matching Column with INDIRECT, INDEX, and MATCH

Use this formula when B2 names the target sheet and the Consumption column can move between sheets.

INDIRECT builds references to the sheet named in B2. MATCH finds the Consumption header, and INDEX returns the corresponding column within the data range for SUM.

The quoted sheet reference supports spaces and many special characters. If a sheet name contains an apostrophe, use SUBSTITUTE(B2,"'","''") in place of each B2 reference in the formula.

1
Enter the sheet name

On the worksheet where you want the result, enter the target worksheet name in cell B2.

2
Insert the formula

Select an empty result cell and enter =SUM(INDEX(INDIRECT("'"&B2&"'!$A$2:$Z$100"),0,MATCH("Consumption",INDIRECT("'"&B2&"'!$A$1:$Z$1"),0))).

3
Check the ranges and result

Press Enter, then change B2 to another worksheet name to test the result. Edit $A$2:$Z$100 and $A$1:$Z$1 in the formula if your data extends beyond those ranges.

Sum the Matching Column with INDIRECT, INDEX, and MATCH
Keep the column ranges aligned: The header range and data range must start and end in the same columns. If your Excel settings use semicolons as argument separators, replace the formula's commas with semicolons.
Work with dynamic sheet references

Sum a Dynamic Sheet Column in WPS Spreadsheet

Use INDIRECT, INDEX, MATCH, and SUM in WPS Spreadsheet to find and total the Consumption column on the sheet named in B2.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your workbook.
  2. 2. Specify the target sheet: On the summary worksheet, enter the target sheet's exact name in cell B2.
  3. 3. Enter the formula: Select an empty cell and enter =SUM(INDEX(INDIRECT("'"&B2&"'!$A$2:$Z$100"),0,MATCH("Consumption",INDIRECT("'"&B2&"'!$A$1:$Z$1"),0))).
  4. 4. Verify the total: Press Enter and compare the total with the values under Consumption on the named sheet. Adjust the formula ranges if needed.
Find the Consumption column even when its position changes between sheets.Open and edit Microsoft Excel XLSX and XLS files.Use familiar spreadsheet formulas and worksheet tabs.Work with spreadsheets using the free WPS Office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return #REF!?

Check that B2 exactly matches an existing worksheet name. If the name contains an apostrophe, replace each B2 reference in the formula with SUBSTITUTE(B2,"'","''").

What if the formula returns #N/A?

MATCH returns #N/A when it cannot find Consumption in the specified header range. Check the spelling and make sure the header is within A1:Z1 on the target sheet.

How do I include more than 100 rows?

Edit $A$2:$Z$100 in the formula to cover the required rows, such as $A$2:$Z$1000. Keep the header and data ranges aligned across the same columns.

Will the formula work if the sheet name contains spaces?

Yes. The single quotes constructed around the name in B2 allow references to sheet names containing spaces.