Named ranges keep changing

[jaffacake]

Hi all,

I have defined names in my workbook that i'm using to populate a graph. This is what i currently have;

=OFFSET(Performance!$C$21,0,Performance!$C$17-1,COUNTA(Performance!$C21:$C33),1)

However, as soon as i add any more data into my worksheet the named range changes... This is what i have once i've entered some more data.

=OFFSET(Performance!$C$21,0,Performance!$C$17-1,COUNTA(Performance!$C48:$C60),1)

Why has the $C21:$C33 changed and how can i stop this from happening?

 

[Jerry Sullivan]

Hi jaffacake,

If you Insert or Delete cells, the references to the named range can change depending on the relationship of the Named Range reference to the Inserted or Deleted cells.

Mohammad Basem's post in this thread illustrates how this happens.
http://www.mrexcel.com/forum/showthread.php?t=633418

One easy workaround to preserve your range references would be to use Copy-Paste instead of Insert to add new data.