Referenced cell changes if new line added to the source

HI,
This uesd to work fine but recently wont

Using data for a chart - there is a new line added each week between the top line and last week.

There is a chart generated by this data for the first 11 rows (eg - this to 10 weeks ago)
=Payments!$A$1:$C$11

it has the $ in so it always looks at the same cells - This is the bit thats not working

What has started to happen is that when a new line is added on source page, the chart referance changes too so it will referance the same data as before
=Payments!$A$1:$C$1,Payments!$A$3:$C$12

How do i stop it from changing?

What is happening is that you are changing the "anchor point."

However, I suggest another approach. You obviously want to see the tabular data with newest on top and display the last 11 points on the chart in chronological order. To do this under your current system, you first have to insert a row and then type in the data.

I suggest a setup as shown on sheet 2. The data are entered in an Excel Table and the chart is built off a pivot table based on the Excel Table.

The table contains a formula: =ROW()>COUNTA([Week End Date])-10 this is TRUE is the date is within 11 weeks. as you add data to the bottom of the chart, the topmost true turns to false. This is used as a filter on the pivot table. Since the data is in an Excel table, adding a line after the table makes that line become part of the table also formulas and formats are copied down automatically.

The pivot table contains the dates in the order you want them. The chart is built on the pivot table and the X axis is reversed.

The only issue with this is that when you add new data, you have to right click on the pivot table and refresh data. With a tiny bit of VB code, the pivot table can be made to refresh whenever data are added to the table.