offset function

[Excel_Enthuse]

Hi Guys

Any idea on how to perfrom the following. I have a chart which looks at data for monday - friday i.e week 38. The date format is dd/mm/yy. I then put in the following weeks data i.e week 39 and only want the chart to show the updated weeks data. If I use the offet function as commonly explained, the chart will update but also have the previous weeks data included of which I do not want to display.

Example
mon 25
tue 12
wed 13
thu 67
fri 23
mon 67
tue 25
wed 45
thu 67
fri 34


Therefore five rows of data will be the weeks data for my chart. Once data has been inputed on the 6th row I want the chart to look at the data for the following five days with the 6th row being a Monday. If this helps in the formula, every 6th row is a monday as data is not taken over the weekend.

I hope this makes sense and hope someone out there can help!

Thanks

[shg]

Post a workbook?

[Andy Pope]

Assuming your data table starts in A2:B2 with header in row 1.
The following named range will report last 5 data points.

ChtLabel: =OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A:$A)-5,0,5,1)

ChtData: =OFFSET(ChtLabel,0,1)