How to Reference a Cell on Another Sheet using INDIRECT and ADDRESS
Question details
The user needs to dynamically construct a reference to a cell located on a different worksheet using the INDIRECT and ADDRESS functions.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Building dynamic formulas that pull data from specific cells on other explicitly named worksheets.
- Observed behavior
- To successfully pull the data, the sheet name must be explicitly supplied within the ADDRESS function because the ROW and COLUMN functions will otherwise default to the active sheet where the formula is placed.
Ensure you know the exact name of the target worksheet. If the worksheet name contains spaces, it must be enclosed in single quotes within your reference string.
Use INDIRECT and ADDRESS to Build the Cross-Sheet Reference
Combine the INDIRECT and ADDRESS functions to dynamically target a cell on a different sheet by explicitly specifying the sheet name.
The ADDRESS function creates a text string representing a cell reference based on given row and column numbers. By wrapping it in the INDIRECT function, the spreadsheet converts that text string into an actual valid cell reference that retrieves the data.
Click on the cell in your current worksheet (e.g., Sheet2) where you want the dynamically referenced value to appear.
Type =ADDRESS(ROW(),COLUMN()-1,1,TRUE,"Sheet1") to generate the text string of the cell address. Replace 'Sheet1' with the exact name of your target sheet.
Complete the formula by wrapping it inside the INDIRECT function: =INDIRECT(ADDRESS(ROW(),COLUMN()-1,1,TRUE,"Sheet1")) and press Enter.
Alternative: Use INDEX for Direct Dynamic Referencing
Since INDIRECT is volatile and can slow down large workbooks, use INDEX to reference cells directly on another sheet for better performance.
Master Advanced Spreadsheet Functions in WPS Office
WPS Spreadsheets provides robust support for over 300 built-in functions, including INDIRECT, ADDRESS, INDEX, and XLOOKUP. Handle complex cross-sheet calculations and large datasets effortlessly.
- 1. Open WPS Spreadsheets: Launch WPS Office and open your existing workbook or create a new blank spreadsheet.
- 2. Navigate to the Formulas Tab: Click on 'Formulas' in the top ribbon to access the function library, including the Lookup and Reference category.
- 3. Insert the Function: Click 'Insert Function', search for INDIRECT or ADDRESS, and use the dialog box prompts to build your cross-sheet reference safely.
Frequently Asked Questions
Why does my INDIRECT function return a #REF! error when referencing another sheet?
A #REF! error usually occurs if the target worksheet name is misspelled, if the sheet name contains spaces but is not wrapped in single quotes, or if the referenced external workbook is currently closed.
Can I use INDIRECT to reference a closed workbook?
No, the INDIRECT function requires the referenced workbook to be open. If the target workbook is closed, the formula cannot evaluate the text string into a valid reference and will return a #REF! error.
What does the '1' and 'TRUE' mean in the ADDRESS function?
The '1' specifies an absolute reference type (e.g., $A$1), and 'TRUE' indicates that the formula should use the standard A1 reference style rather than the R1C1 reference style.
Why should I avoid using the EVALUATE function for dynamic references?
EVALUATE is a legacy macro function used to evaluate text strings as formulas. It is highly volatile, difficult to maintain, and is not supported in standard cell formulas without Name Manager workarounds. Standard functions like INDEX or XLOOKUP are much safer and more efficient.
