Thread: XY chart dynamic range problem

I am trying to add a series by way of naming ranges to make my existing chart have dynamic ranges.

I am trying to add to an existing XY chart dynamic X and y ranges with existing series. I have tried defining ranges and inserting a new series to the existing chart linked to the named ranges like below.

XAXIS => =OFFSET(YAXIS,0,-3)
YAXIS => =OFFSET(YAXISLABEL,1,0,COUNT('DATA!$S2:$S100),1)
SERIES LABEL => ='Data'!$S$2

Once I add the series excel automatically modifies the data count to S147:S198. Why does excel do this and how can I prevent this ?

Change

COUNT('DATA!$S2:$S100)

to

COUNT('DATA!$S$2:$S$100)