How to Create a Dynamic Excel Worksheet Reference from a Week Number in WPS Office
Question details
The user needs a formula that dynamically references a worksheet based on a week number stored in a cell, retrieving the same cell from sheets named Week 104, Week 105, or similar.
- Product
- WPS Office Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- The workbook contains weekly worksheets with names such as Week 104 and Week 105, and the user wants to pull the same target cell from the sheet that matches the week number entered in a cell.
- Observed behavior
- The desired formula should build the sheet name dynamically and return the correct cell value; if the generated sheet name does not match an existing worksheet, INDIRECT returns a #REF! error.
Check that the weekly sheet names follow the exact pattern used in the formula, including spaces and capitalization, and note the cell that holds the week number and the target cell address in each weekly sheet.
Build the Sheet Reference with the INDIRECT Function
Use this approach when you have a week number stored in a cell and want WPS Spreadsheets to automatically retrieve the same cell from the matching weekly worksheet.
INDIRECT converts a text string into a real cell or worksheet reference. The formula =INDIRECT("'Week "&A1&"'!D4") creates the text 'Week 104'!D4 when A1 contains 104, so it can retrieve D4 from the corresponding weekly sheet.
The single quotation marks around the sheet name are important because sheet names such as Week 104 contain spaces. The generated sheet name must match the worksheet tab exactly.
Click the cell where you want the dynamic worksheet reference result to appear.
Type =INDIRECT("'Week "&A1&"'!D4") in the formula bar and press Enter. Replace A1 with the cell that contains the week number if necessary.
To retrieve a different cell, replace D4 in the formula with the cell address you want, such as E5.
Change the value in A1 to another existing week number and confirm that the formula returns the correct value from the matching weekly sheet.
Create a Dynamic Week-Based Worksheet Reference in WPS Office
WPS Spreadsheets supports the INDIRECT function, so you can build a worksheet reference from a week number exactly as you would in Excel. Open the workbook, enter the formula, and WPS will return the value from the matching weekly sheet.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the weekly worksheets and the week number cell.
- 2. Select the result cell: Click the cell where the dynamic reference result should appear.
- 3. Enter the INDIRECT formula: Type =INDIRECT("'Week "&A1&"'!D4") in the formula bar and press Enter. Adjust A1 and D4 to match your workbook.
- 4. Verify the reference: Change the week number in A1 and confirm that the result comes from the correct Week sheet.
Frequently Asked Questions
Why does my INDIRECT week-based reference return #REF!?
This usually means the generated worksheet name does not match an existing sheet tab. Check the value in A1 and compare it with the actual sheet name, including spaces, spelling, and capitalization. If the sheet is named Week 104, the formula must generate exactly 'Week 104'!D4.
Do I need single quotes around the sheet name in the formula?
Yes, when the sheet name contains a space, as in Week 104, use single quotes around the generated sheet name: =INDIRECT("'Week "&A1&"'!D4"). If your sheet names have no spaces, the quotes can be omitted, but keeping them is safer.
Can I return a different cell from the weekly sheet?
Yes. Replace D4 in the formula with the desired cell address. For example, use =INDIRECT("'Week "&A1&"'!E5") to return E5 from the matching weekly sheet.
Does this INDIRECT formula work in both WPS Spreadsheets and Microsoft Excel?
Yes, INDIRECT is supported in both applications. The same formula syntax works with .xlsx workbooks, so you can open and edit the file in WPS Office or Excel without changing the formula.
