WPS Spreadsheet: Retrieve Cell Values from Multiple Worksheets with INDIRECT
Question details
Retrieve the value from the same cell across multiple worksheets using a list of worksheet names and an INDIRECT formula.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Worksheet names are stored in a column, and the user wants to retrieve the value from cell B5 on each corresponding worksheet.
- Observed behavior
- The required values need to be collected from multiple worksheets by creating dynamic worksheet references.
Confirm that the worksheet names in your list exactly match the tab names, including spaces and punctuation. Also verify that cell B5 is the source cell on every worksheet.
Use INDIRECT with a list of worksheet names
Use this method when the target cell is the same on every worksheet and the sheet names are available in a column.
INDIRECT converts text into a cell reference. Wrapping the sheet name in single quotes allows worksheet names containing spaces or special characters.
In WPS Spreadsheet, enter the worksheet names in cells B2:B1000, with one exact sheet name per row.
Select the cell next to the first sheet name, such as C2, and enter =INDIRECT("'"&B2&"'!B5").
Press Enter, select C2, and drag the fill handle down or use the Fill Down command to apply the formula to the remaining worksheet names.
Review the results in column C. Each row should display the value from cell B5 on the worksheet named in the corresponding column B cell.
Generate the worksheet-name list with a macro
Use this approach when the workbook contains many worksheets and you want to create the sheet-name list automatically before applying INDIRECT.
Retrieve Matching Cells Across Worksheets in WPS Spreadsheet
WPS Spreadsheet lets you build dynamic worksheet references with INDIRECT, fill the formula across a sheet-name list, and collect matching values without opening each worksheet individually.
- 1. Open the workbook: Launch WPS Office and open the spreadsheet containing the worksheets you want to query.
- 2. List worksheet names: Enter the exact worksheet names in a column, such as B2:B1000.
- 3. Build the reference: In the adjacent cell, enter =INDIRECT("'"&B2&"'!B5") and press Enter.
- 4. Fill down the results: Drag the fill handle or use Fill Down to apply the formula to all listed worksheet names.
Frequently Asked Questions
What formula retrieves cell B5 from the worksheet named in B2?
Enter =INDIRECT("'"&B2&"'!B5") in the adjacent result cell. The formula uses the text in B2 as the worksheet name and returns that worksheet's B5 value.
Why are single quotes included around the worksheet name?
Single quotes make the reference valid when a worksheet name contains spaces or special characters. The pattern 'Sheet Name'!B5 is safer than an unquoted reference.
How do I retrieve values from a different cell instead of B5?
Replace B5 in the formula with the required cell reference. For example, use =INDIRECT("'"&B2&"'!D10") to retrieve cell D10 from each listed worksheet.
What should I do if INDIRECT returns an error?
Check that the sheet name in the list exactly matches the worksheet tab and that the target cell exists. Extra spaces or misspelled names can cause a reference error.
Is INDIRECT suitable for a very large workbook?
INDIRECT is volatile, so it may recalculate frequently and slow down large workbooks. For extensive data collection, consider Power Query or a macro-based solution.
