Fixed size dynamic range for chart

[mrmrva]

Hello all. I am trying to create data range used in chart that needs to be of fixed size but moving as we add rows to table.

So in the picture I have range of 3 rows

Range1.JPG

And as I add new data it should move down but remain height of 3 rows

Range2.JPG

I've been trying with OFFSET and COUNTA but getting error when using COUNTA as row reference in OFFSET command (I hope I am clear on this)

[AliGW]

Why not just make your table into a proper Excel table?

[mrmrva]

This is just exercise example although Spreadsheet I've received at work is also not formatted as table but we can convert it of course if it would help.
Haven't used Excel in probably 10 years, just started with some courses so apologies if I miss something obvious in my posts

[AliGW]

OK - attach a sample workbook.

Are you saying that you always wish to reference the last three months?

[Andy Pope]

Set up 4 named ranges

LABELS =OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A:$A)-3,0,3,1)
DATASET1 =OFFSET(LABELS,0,1)
DATASET2 =OFFSET(LABELS,0,2)
DATASET3 =OFFSET(LABELS,0,3)

Update chart series formula to use named ranges.


Example attached

[mrmrva]

Thank you both for extremelly quick replies.

[at] Andy
Many thanks, this is what I was trying to achieve