Excel Formula Indirect() errors when referencing a Range of Sheets

Question

Am I looking at a bug with Indirect()?

It seems it breaks when trying to evaluate a sheet range in it's reference string.

=SUM(INDIRECT("'Sheet 1'!R2C2",FALSE)) returns 2

=SUM(INDIRECT("'Sheet 2'!R2C2",FALSE)) returns 3

=SUM('Sheet 1:Sheet 2'!R2C2) returns 5

=SUM(INDIRECT("'Sheet 1:Sheet 2'!R2C2",FALSE)) returns #REF!

I like to work using R1C1 referencing, but I get the same results when I use A1 referencing.

Answer 1

Hi @Joseph Burns,

For your concern, this is not a bug but an expected behavior of INDIRECT() in Excel. INDIRECT() can resolve a single-sheet cell reference like INDIRECT("'Sheet 1'!R2C2",FALSE)) and INDIRECT("'Sheet 2'!R2C2",FALSE)) but it cannot interpret a 3-D reference 'Sheet 1:Sheet 2'!R2C2 as a valid one so it returns #REF! error.

Refer to Create a 3-D reference to the same cell range on multiple worksheets - Microsoft Support, INDIRECT() is not listed as a supported formula with 3-D reference. Therefore, in your case, I suggest:

• If you want to use a direct 3-D reference, use only SUM('Sheet 1:Sheet2'!R2C2).
• If you want to use INDIRECT(), you should build separate call per sheet and then sum them later. For example =SUM(INDIRECT("'Sheet 1'!R2C2",FALSE),INDIRECT("'Sheet 2'!R2C2",FALSE))

Hope my sharing maybe helpful for your concern. If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.    

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 2

INDIRECT does not support 3D references (sheet ranges like Sheet 1:Sheet 2). This is a limitation, not a bug.

Explanation of the behavior:

• =SUM(INDIRECT("'Sheet 1'!R2C2",FALSE)) works because the text resolves to a valid single-sheet reference.
• =SUM(INDIRECT("'Sheet 2'!R2C2",FALSE)) also works for the same reason.
• =SUM('Sheet 1:Sheet 2'!R2C2) works because Excel natively supports 3D references in functions like SUM when written directly in the formula.
• =SUM(INDIRECT("'Sheet 1:Sheet 2'!R2C2",FALSE)) returns #REF! because INDIRECT cannot interpret a 3D reference string. When a function returns an invalid reference, Excel shows #REF!.

This is consistent with other documented cases where INDIRECT (and some other functions) cannot handle certain reference types or closed workbooks and therefore return reference errors.

To sum across multiple sheets dynamically, use:

• A direct 3D reference like =SUM('Sheet 1:Sheet 2'!R2C2) where possible, or
• Alternative approaches such as helper sheets, consolidating data into a single table, or listing sheet names in cells and using other techniques (not via INDIRECT with a sheet range).

---

References:

• How to correct a #REF! error
• How to avoid broken formulas in Excel
• Detect formula errors in Excel