Dynamic Named Range Works on All Except One Worksheet within a single workbook

Question

I have a dynamic named range "GIO" like this, say all the named references within are one worksheet A:

=OFFSET(INDEX(LatestTRDays,1,MATCH("709-HK",LatestTRDaysCols,0)),0,0,4+'Price Mod2'!C9-INDEX(AquisitionDates,1,MATCH("709-HK",LatestTRDaysCols,0)),1)

On worksheets A, B, C-H I can use this named range in formulas like =SUM(GIO) or to create sparklines, but on worksheet J, this will give me a #REF! error. The dynamic named range seems to work on every other worksheet in the workbook, but not for worksheet J.

There are no conflicting named ranges at any scope, copying and pasting worksheet J content into a new worksheet does not solve the problem, nor does renaming the dynamic named range.

Answer 1

OK, I probably spent a couple hours on this, then immediately figured it out after I posted the question. Isn't the brain mysterious.

The strange behaviour difference between worksheet did not actually have to do with worksheets but with what cell I was referencing the dynamic named range from. Of course, that tipped me off that the problem was a relative reference in the named range definition that should have been absolute, 'Price Mod2'!$C$9.

The lesson is that dynamic named ranges are not just sensitive to changes in referenced cell values (the behaviour I wanted) but also to the cell that they are referenced from (need to correct for this when you don't want it by making references in the name definition absolute).

Fixed a problem / discovered a feature!