Why is INDIRECT() not working on a dynamic named ranged with worksheet scope?

Question

I'm using Excel Office 365. I'm trying to understand why INDIRECT() doesn't work with dynamic named ranges. Here's a screenshot:

In sheets 1, 2, and 3, I have a named range "Item1Price" contain some numbers. In Sheet3, the range with the numbers is defined with an OFFSET formula. Why does this break INDIRECT()? Is there another way to do this?

Thanks

Answer 1

INDIRECT cannot refer to a Dynamic Named Range. Just one more reason I hate INDIRECT.

You can use helper cells.

Depending on your version:

Office 365 or latter put this in the first cell:

=INDEX(Sheet3!$C$3:$E$5,MATCH(Sheet3!$C$11,Sheet3!$B$3:$B$5,0),0)

or if earlier version highlight three cells in a row and put the formula in the formula bar and hit Ctrl-Shift-Enter to array enter.

Then instead of the named range being a variable range assign the helper cells to the named range:

Then you can use your formula:

=SUM(INDIRECT("sheet3!Item1Price"))

And now as the value in C11 changes so does the SUM:

The other option is to skip the INDIRECT completely with CHOOSE:

=CHOOSE(MATCH(A1,{"Sheet1","Sheet2","Sheet3"},0),Sheet1!Item1Price,Sheet2!Item1Price,Sheet3!Item1Price)

This assumes you will have the desired sheet name in A1. This would then use your dynamic range name without problem.