Function Problems

How to Use OFFSET and INDIRECT for Dynamic Cross-Sheet References in Excel

Tauseeq Magsi Oct 22, 2026 868 views

Question details

The user needs to correctly implement dynamic cross-sheet references without triggering a circular reference error.

How to Use OFFSET and INDIRECT for Dynamic Cross-Sheet References in Excel

Product: Excel
Device & OS: not provided
Scenario: Using the OFFSET and INDIRECT functions in combination to dynamically fetch data from a cell on a different worksheet.
Observed behavior: Using a formula like OFFSET(INDIRECT("A2"),0,1) results in a circular reference because it evaluates A2 on the current worksheet instead of following the external reference stored inside cell A2.

Before you start

Verify the exact spelling of your target worksheet's name, paying special attention to any spaces, to ensure the text reference will parse correctly.

Solution 1Recommended

Use a Worksheet-Qualified String in INDIRECT

Provide the full worksheet name and cell reference directly as text inside the INDIRECT function to explicitly target the correct sheet and avoid circular logic.

The standard INDIRECT("A2") simply points to A2 on the sheet where the formula lives. By hardcoding the sheet name into the text string, you force Excel to look at the external sheet.

Select the destination cell

Click on the cell where you want to output the dynamically referenced data.

Enter the worksheet-qualified formula

Type the formula =OFFSET(INDIRECT("'Project overview'!A21"),0,1), replacing 'Project overview' with your actual worksheet name.

Apply the formula

Press Enter. The formula will now correctly resolve the sheet name from the text string before applying the OFFSET shift.

Use a Worksheet-Qualified String in INDIRECT

Watch your quotes: Notice the single quotes around the sheet name inside the double quotes (e.g., "'Sheet Name'!A1"). These are strictly required if your worksheet name contains spaces.

Solution 2

Use a Direct Cross-Sheet Reference with OFFSET

Bypass the INDIRECT function entirely by referencing the target sheet's base cell directly within the OFFSET function.

Solution 3

Use the INDEX Function Alternatively

Replace OFFSET with INDEX to retrieve dynamic cross-sheet references without utilizing volatile functions.

Advanced Formulas in WPS

Easily Manage Complex Cross-Sheet Formulas in WPS Office

WPS Spreadsheet provides robust, full-featured support for all major lookup and reference functions, including OFFSET, INDIRECT, and INDEX. You can easily build, debug, and manage dynamic cross-sheet references perfectly without performance bottlenecks.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet file (.xlsx).
2. Navigate to the calculation cell: Select the cell where you want to calculate the dynamic cross-sheet reference.
3. Insert the nested formula: Type the correct syntax, such as =OFFSET(INDIRECT("'YourSheetName'!A1"),0,1).
4. Check for errors instantly: Press Enter. If there are circular references, WPS Spreadsheet will automatically flag them in the status bar so you can trace and fix them easily.

100% format compatibility with Microsoft Excel formulas and syntaxBuilt-in error checking to quickly trace circular referencesLightweight application design for rapid calculation speedsFree to use for everyday data analysis and reporting

microsoft office alternative - wps office

Frequently Asked Questions

Why does INDIRECT("A2") create a circular reference?

When you use INDIRECT("A2"), the function strictly evaluates cell A2 on the current worksheet. If this formula is placed in cell A2, or if evaluating A2 loops back to the cell containing the formula, it causes a circular reference error rather than reading a text string that points somewhere else.

What is the difference between OFFSET and INDEX?

OFFSET calculates a cell address by moving a specific number of rows and columns from a starting point, but it is a volatile function that recalculates on every workbook change. INDEX retrieves the value at a given coordinate within a specified range and is non-volatile, making it much more efficient for complex workbooks.

How do I reference a sheet name containing spaces inside INDIRECT?

If your target worksheet name contains spaces, you must enclose the sheet name in single quotes within the text string. For example, you must type =INDIRECT("'Project overview'!A1") instead of =INDIRECT("Project overview!A1").

Can I dynamically build the sheet name within INDIRECT?

Yes. You can concatenate text strings and cell values to build the address dynamically. For example, if cell B1 contains the sheet name 'Sales', you can use =INDIRECT("'" & B1 & "'!A1") to reference cell A1 on the Sales sheet.

Excel & Data Problems