Use INDIRECT, SUM, and INDEX for Dynamic Excel Sheet Names
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.

- 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.
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.
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.
On the worksheet where you want the result, enter the target worksheet name in cell B2.
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))).
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 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. Open the workbook: Open WPS Spreadsheet and use Open to select your workbook.
- 2. Specify the target sheet: On the summary worksheet, enter the target sheet's exact name in cell B2.
- 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. Verify the total: Press Enter and compare the total with the values under Consumption on the named sheet. Adjust the formula ranges if needed.

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.




