Function Problems

How to Reference the Same Cells Across Multiple Excel Scenario Sheets

Ayan Masood Sep 28, 2026 870 views

Question details

The user needs to create an automated formula that references identical cells across different scenario worksheets without manually updating the sheet names for each reference.

How to Reference the Same Cells Across Multiple Excel Scenario Sheets

Product: Excel
Device & OS: not provided
Scenario: Consolidating or analyzing data from multiple sequentially named scenario worksheets on a master summary sheet.
Observed behavior: The user is looking for a dynamic way to pull cell data across multiple sheets, as manually typing or editing sheet names into each cell's formula is inefficient and prone to errors.

Before you start

Ensure your scenario worksheets follow a consistent naming convention (such as Sheet1, Sheet2, Sheet3) so they can be easily linked using numerical patterns in your formulas.

Solution 1Recommended

Use the INDIRECT Function with a Helper Row

Use the INDIRECT function to dynamically build worksheet references based on scenario numbers placed in a header row.

The INDIRECT function turns a text string into a valid Excel reference. By combining static text (like "Sheet") with a dynamic scenario number from a helper row, you can drag the formula across your summary table to instantly reference multiple sheets.

Set up a helper row for scenario numbers

In your summary sheet, type your scenario or sheet numbers sequentially across a header row. For example, enter 1 in cell A1, 2 in cell B1, and 3 in cell C1.

Enter the INDIRECT formula

In the cell where you want to display the data (e.g., cell A2), enter the formula: =INDIRECT("Sheet"&A$1&"!I20"). This concatenates the word 'Sheet' with the number in A1 to pull data from cell I20 of 'Sheet1'.

Copy the formula across columns

Select cell A2, grab the fill handle at the bottom right corner, and drag it across the row. The mixed reference A$1 will change to B$1, C$1, etc., pulling data from Sheet2, Sheet3 accordingly.

Adjust references for different rows

If you need to pull data from other cells like I25 or I30, copy the formula down and manually update the string portion (e.g., change "!I20" to "!I25").

Use the INDIRECT Function with a Helper Row

Mixed References: Using the mixed reference (A$1) ensures that the column changes dynamically when dragged across, while the row stays fixed to your header containing the scenario numbers.

Powerful Spreadsheet Tool

Seamlessly Consolidate Multi-Sheet Data in WPS Spreadsheet

WPS Spreadsheet fully supports advanced cross-sheet references and complex formulas like INDIRECT. You can easily build dynamic summary reports from multiple scenario sheets in a familiar, high-performance environment.

1. Open your workbook: Launch WPS Spreadsheet and open your multi-sheet scenario workbook.
2. Create a summary layout: Add a new sheet and set up a header row containing your scenario numbers.
3. Apply dynamic formulas: Use the =INDIRECT() formula to link your scenario sheets, then drag to fill your summary table effortlessly.

100% compatible with Microsoft Excel formulas and .xlsx formats.Fast calculation engine handles complex cross-sheet data aggregation smoothly.Free and lightweight alternative with a highly intuitive user interface.

microsoft office alternative - wps office

Frequently Asked Questions

What if my worksheet names contain spaces?

If your worksheet names have spaces (e.g., 'Scenario 1'), you must wrap the sheet name in single quotes within the INDIRECT function. The formula should look like this: =INDIRECT("'Scenario "&A$1&"'!I20").

Why is my INDIRECT formula returning a #REF! error?

The #REF! error indicates that the text string generated by the INDIRECT function does not form a valid cell reference. Check if the scenario sheet exists, ensure the spelling matches perfectly, and verify that the target cell is valid.

Can I sum the same cell across multiple sheets without using INDIRECT?

Yes, if you only need to calculate an aggregate like SUM or AVERAGE, you can use a 3D reference. For example, the formula =SUM(Sheet1:Sheet10!I20) will add the value of cell I20 from all sheets between Sheet1 and Sheet10 inclusive.

Will the INDIRECT formula update if I rename my scenario sheets?

No. Because INDIRECT constructs the reference from static text strings, renaming a worksheet will not automatically update the text in your formula. The formula will return a #REF! error until you manually adjust the text string in the INDIRECT function to match the new name.

Excel & Data Problems