WPS Spreadsheet: Use an Excel Header to Reference Another Worksheet
Question details
The user needs to use a header value as a worksheet name and retrieve matching data from that worksheet.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A workbook contains similarly structured worksheets, and a header cell identifies which worksheet should supply data for a calculation.
- Observed behavior
- The desired worksheet reference must change dynamically based on the value in the header cell.
Verify that each header value exactly matches an existing worksheet name, including spaces and date formatting. Confirm that the source sheets use consistent columns for the lookup criteria and values.
Use INDIRECT with the header value to reference another worksheet
Use this formula-based method when the worksheet name is stored in a header cell and the source sheets share a consistent layout.
INDIRECT converts text into a cell or range reference. Combining the header cell with a quoted sheet reference lets the formula point to a different worksheet as the header changes.
The example below uses the sheet name in B2, matches the criterion in A3 against column A, and sums corresponding values from column F.
In the result cell, choose the Formula Bar or a blank cell and enter =SUMIFS(INDIRECT("'"&B$2&"'!$F$1:$F$1000"),INDIRECT("'"&B$2&"'!$A$1:$A$1000"),$A3).
Make sure B2 contains the exact worksheet name, including any spaces. The single quotes around the dynamic sheet name allow names with spaces to work correctly.
Select the formula cell and drag its fill handle across and down, or use Home > Fill to copy the formula to the required rows and columns.
Replace the value in B2 with another valid sheet name and confirm that the result updates from the newly referenced worksheet.
Use a consolidated table for larger or frequently updated workbooks
Use this approach when many worksheets or large ranges make volatile INDIRECT formulas slow or difficult to maintain.
Build Dynamic Cross-Sheet References in WPS Spreadsheet
WPS Spreadsheet lets you use INDIRECT formulas to convert a header value into a worksheet reference, match criteria, and return data from the selected sheet. You can fill the formula across and down while maintaining Excel-compatible workbook formats.
- 1. Open the workbook: Launch WPS Office, select Spreadsheet, and open the workbook containing the header and source worksheets.
- 2. Confirm sheet names and ranges: Check the header cell and verify that each referenced worksheet uses consistent criteria and value columns.
- 3. Enter the formula: Select the result cell and enter =SUMIFS(INDIRECT("'"&B$2&"'!$F$1:$F$1000"),INDIRECT("'"&B$2&"'!$A$1:$A$1000"),$A3).
- 4. Fill and verify: Drag the fill handle across and down, then change the header value to confirm that the returned data comes from the selected worksheet.
Frequently Asked Questions
What formula uses a header cell as the worksheet name in WPS Spreadsheet?
Use INDIRECT to create the reference dynamically. For example, =SUMIFS(INDIRECT("'"&B$2&"'!$F$1:$F$1000"),INDIRECT("'"&B$2&"'!$A$1:$A$1000"),$A3) uses B2 as the worksheet name and A3 as the matching criterion.
Why does the dynamic worksheet reference return an error?
The header value probably does not exactly match a worksheet tab name. Check spelling, spaces, date formatting, and punctuation. Keep the single quotes in the INDIRECT expression for sheet names containing spaces.
Can I fill the INDIRECT formula across and down?
Yes. Use mixed references such as B$2 for the header row and $A3 for the criteria column, then drag the fill handle across and down to adjust the intended references.
Is INDIRECT suitable for a workbook with many worksheets?
INDIRECT is convenient for smaller or moderately sized workbooks, but it is volatile and can slow large files. For many sheets or frequent updates, use a consolidated table or an automated data-combining workflow.
