How to Reference a Previous Sheet in Excel for the Web (INDIRECT)
Question details
The user needs to automatically reference a cell from a previous worksheet when duplicating a sheet in Excel for the Web, as the =@prev formula does not work.
- Product
- Excel for the Web
- Device & OS
- not provided
- Scenario
- Duplicating worksheets for recurring data, such as weekly reports, and needing to seamlessly pull cell data from the prior week's sheet.
- Observed behavior
- The =@prev(A5) formula returns an error or fails to compute because it is not supported in the web-based version of Excel.
Ensure you know the exact name of the previous worksheet you want to reference, as precise spelling and spacing are critical for text-based referencing formulas to work.
Use the INDIRECT Function to Reference the Previous Sheet
Store the previous sheet's name in a helper cell and use the INDIRECT formula to pull the data dynamically across sheets.
Since Excel for the Web does not support macro-based relative sheet referencing or the legacy @prev formula, the most reliable workaround is to use the INDIRECT function combined with a helper cell.
Select an empty cell in your current worksheet (for example, A2) and type the exact name of the previous worksheet you want to reference.
Click on the target cell where you want the referenced data to appear. Type the formula =INDIRECT("'"&A2&"'!A5") and press Enter. This tells Excel to look at the sheet name specified in A2 and fetch the value from cell A5.
Whenever you duplicate the sheet for a new week or period, simply update the text in cell A2 to match the newly duplicated previous sheet's name. The formula will recalculate automatically.
Consolidate Data Using a Single Sheet with Filters
Instead of creating a new sheet every week, keep all data in one master sheet and use built-in tools like PivotTables to view specific periods.
Easily Manage and Reference Spreadsheets with WPS Office
WPS Office Spreadsheet provides complete support for advanced referencing formulas like INDIRECT, allowing you to seamlessly link and calculate data across multiple worksheets with a smooth desktop interface.
- 1. Open Your File in WPS: Download and launch WPS Office, then open your existing .xlsx workbook in the WPS Spreadsheet application.
- 2. Set Up Your Helper Cell: Click into cell A2 (or another dedicated cell) and type the name of the previous worksheet you need to reference.
- 3. Apply the Formula: Navigate to the cell where you want the data to appear and enter =INDIRECT("'"&A2&"'!A5").
- 4. Calculate and Save: Press Enter to instantly pull the data from the designated sheet. You can seamlessly copy this sheet and update A2 without experiencing web-browser lag.
Frequently Asked Questions
Why does the =@prev formula return an error in Excel for the Web?
Excel for the Web does not natively support the =@prev formula. It is considered a specialized or legacy function and is excluded from the standard online Excel function library, resulting in a '#NAME?' or '#VALUE!' error.
Can I dynamically reference the previous sheet without typing its name manually?
Without utilizing VBA macros (which are completely unsupported in Excel for the Web), you cannot dynamically fetch the previous sheet's name. You must manually type the sheet name into a helper cell or directly into the INDIRECT formula.
What happens if the referenced sheet name contains spaces?
If your worksheet name contains spaces, Excel requires it to be wrapped in single quotes. The standard INDIRECT formula structure INDIRECT("'"&A2&"'!A5") automatically accounts for these spaces, preventing reference errors.
Will using the INDIRECT function slow down my web spreadsheet?
Yes, INDIRECT is known as a volatile function. This means it recalculates every single time any change is made anywhere in the workbook. Overusing it across hundreds of cells in a web browser can cause noticeable performance drops.
