Search code examples
excelvbaexcel-2013

Excel Dynamic Ranges changing (with no logic)

A confusing one. I'm using a dynamic range

=OFFSET('EA OPs'!A4,0,0,COUNTA('EA OPs'!$A:$A)-1,96)

I've assigned it to rOps in the NAME MANAGER. I've then tried to change a Pivot Table source to rOps. Click in to a random cell. Click back in the NAME MANAGER and it has changed to:

=OFFSET('EA OPs'!G3,0,0,COUNTA('EA OPs'!$A:$A)-1,96)

then 

=OFFSET('EA OPs'!G24,0,0,COUNTA('EA OPs'!$A:$A)-1,96)

seemingly at random. Althought it does seem to keep near to the cell I've clicked on?

Any ideas?

Solution

  • You need to anchor the A4 with dollar signs and make it an absolute reference. Otherwise it's relative to whatever cell is active:

    =OFFSET('EA OPs'!$A$4,0,0,COUNTA('EA OPs'!$A:$A)-1,96)

    Named ranges always behave this way, which can be useful. For example with A2 active, you can create a range named "CellAbove" and set it to "=A1". You can then use it in a formula in any cell, e.g., `=CellAbove/2' and the result will be the value of the cell one row up, divided by 2.