How to Reference a Worksheet Dynamically by Name in WPS Office Spreadsheets
Question details
The user wants to create a formula that references a cell in another worksheet using the worksheet name stored in cell A5, instead of typing the sheet name directly.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a flexible workbook formula that updates automatically when the worksheet name in A5 changes.
- Observed behavior
- The formula should return the value from cell B16 on the worksheet whose name is stored in A5, and update when A5 changes.
Confirm that cell A5 contains the exact worksheet name, including any spaces or special characters. Make sure the target cell B16 exists on the referenced worksheet.
Use the INDIRECT Function to Build a Dynamic Worksheet Reference
Use this method when the worksheet name is stored in a cell and you want the formula to update automatically without hard-coding the sheet name.
The INDIRECT function converts a text string into a valid cell or range reference. Wrapping the sheet name in apostrophes ensures the formula works with worksheet names that contain spaces or special characters.
Type the exact name of the worksheet you want to reference into cell A5, for example Sales 2024.
Click the cell where you want the dynamic reference result to appear.
Type =INDIRECT("'"&A5&"'!B16") and press Enter.
Create Dynamic Worksheet References with INDIRECT in WPS Spreadsheets
Use the INDIRECT function in WPS Spreadsheets to build flexible formulas that read the worksheet name from a cell, so your references update automatically when the cell value changes.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the workbook where you want to create the dynamic reference.
- 2. Enter the worksheet name in A5: Type the exact target worksheet name in cell A5, such as Sales 2024.
- 3. Insert the INDIRECT formula: In the result cell, enter =INDIRECT("'"&A5&"'!B16") and press Enter.
- 4. Test the dynamic reference: Change the value in A5 to another worksheet name and confirm the formula returns the new sheet's B16 value.
Frequently Asked Questions
Why does my INDIRECT formula return #REF! when the sheet name has a space?
If the worksheet name contains spaces or special characters, wrap it in apostrophes: =INDIRECT("'"&A5&"'!B16"). Without apostrophes, WPS Spreadsheets and Excel may not recognize the sheet name and can return #REF!.
Can I use INDIRECT to reference a worksheet name from another cell without typing the sheet name?
Yes. Store the sheet name in a cell such as A5, then use =INDIRECT("'"&A5&"'!B16") to build the reference dynamically.
Does INDIRECT update automatically when I change the worksheet name in A5?
Yes. INDIRECT is a volatile function, so the formula recalculates when the value in A5 changes or when the workbook recalculates.
What is the difference between =INDIRECT(A5&"!B16") and =INDIRECT("'"&A5&"'!B16")?
The shorter version =INDIRECT(A5&"!B16") may work for simple one-word sheet names, but =INDIRECT("'"&A5&"'!B16") is safer because apostrophes allow spaces and special characters.
Can I use INDIRECT with WPS Spreadsheets and Excel files?
Yes. WPS Spreadsheets supports the INDIRECT function and is compatible with Excel .xlsx, .xls, and .csv files, so the formula works across both applications.
