WPS Spreadsheets: Use a Cell Value as a Dynamic Sheet Name
Question details
The user needs to use the Cost Code in cell F6 as a dynamic worksheet name inside a FILTER and SUM formula.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains separate worksheets named after Cost Codes, and the formula must retrieve and sum matching values from the worksheet named in F6.
- Observed behavior
- The worksheet name is hard-coded in the formula instead of being generated from the cell value in F6.
Verify that the worksheet named in F6 exists exactly, including spaces and special characters. Confirm that the source ranges H6:H58 and A6:A58 have the same number of rows.
Use INDIRECT to create a dynamic worksheet reference from F6
Use this solution when the worksheet named in F6 exists in the current workbook and the source ranges have matching sizes.
INDIRECT converts text into a cell reference, allowing the value in F6 to become the worksheet name. The single quotes around the worksheet name support names that contain spaces or special characters.
Open the WPS Spreadsheet workbook and click the cell where you want to display the filtered sum.
In the formula bar or selected cell, enter =SUM(FILTER(INDIRECT("'"&F6&"'!H6:H58"),INDIRECT("'"&F6&"'!A6:A58")=A6,0)).
Press Enter to calculate the result. WPS Spreadsheets will use the worksheet name stored in F6, filter rows whose column A value matches A6, and sum the corresponding values in column H.
If the formula returns an error, compare F6 with the actual worksheet tab name and verify that both referenced ranges use the same row boundaries.
Create Dynamic Worksheet References in WPS Spreadsheets
WPS Spreadsheets lets you build dynamic formulas with INDIRECT, FILTER, and SUM so a Cost Code cell can determine which worksheet to query without repeatedly editing hard-coded sheet names.
- 1. Open your workbook: Launch WPS Office, choose Spreadsheets, and open the workbook containing the Cost Code and source worksheets.
- 2. Select an output cell: Click the cell where the calculated total should appear, then select the formula bar.
- 3. Insert the dynamic formula: Enter =SUM(FILTER(INDIRECT("'"&F6&"'!H6:H58"),INDIRECT("'"&F6&"'!A6:A58")=A6,0)) and press Enter.
- 4. Validate the result: Confirm that F6 matches an existing worksheet tab and that the filtered total changes when the Cost Code changes.
Frequently Asked Questions
Why does the formula use single quotes around the value in F6?
The formula uses "'"&F6&"'!" so worksheet names containing spaces or special characters are interpreted correctly, such as 'Project Costs'!H6:H58.
What happens if the worksheet named in F6 does not exist?
INDIRECT returns a reference error because it cannot resolve the generated worksheet reference. Make sure F6 exactly matches an existing worksheet tab, including capitalization, spaces, and punctuation.
Why must the two referenced ranges have matching sizes?
FILTER compares each value in A6:A58 with A6 and returns corresponding values from H6:H58. Both ranges must contain the same number of rows for the filter array and result array to align.
Can INDIRECT reference a worksheet in a closed external workbook?
No. INDIRECT generally cannot resolve references to closed external workbooks. For large or external datasets, import the data with Power Query or keep the source workbook open.
Can I change the dynamic sheet name cell from F6 to another cell?
Yes. Replace every reference to F6 in the formula with the cell containing the desired worksheet name, while keeping the single quotes and ampersands intact.
