(Probably small) rolling 13 week chart issues

[susieF]

Hi,

I am having issues with a chart that I want to auto-update on a rolling 13 week period. I have the time periods in column A and the data corresponding to each in column D (both with header names in cells A1 and D1 so the data itself starts in A2 and D2). Note: I will to have a number of ranges set up with data always corresponding to weeks given in column A, hence my example uses data in D as its not just an A:B thing, I need to match different data columns with column A for time (if that makes sense!)

I am using the following names range for the data:

Trilogy
=OFFSET(Sheet1!$D$2,MAX(1,COUNTA(Sheet1!$D$2:$D$100)-13),0,MIN(COUNTA(Sheet1!$D:$D),13),1)

And the source data for the chart is listed as:

='Rolling 13 weeks.xls'!Trilogy.. under the "Values" field of the Series tab.

---

The chart auto-updates when inserting new figures into the D column, but my problem is that the time column A will always display values for weeks until the end of the year, with the data column D being the one that is blank until values are filled in each week. At present because the 2 columns are not the same length my weeks along the X axis of the chart do not correspond to the rolling 13 week range in the spreadsheet.

Using the category X axis label of =Sheet1!$A$2:$A$100 does not work as the data needs to be the most recent 13 weeks...

I got it to work by setting up an identical named range for column A using the code above, but that only works in practice when I insert the column A week number data at the same time as the other data, and I need the weeks to be present all the time for usability.

Any help would be greatly appreciated!!!!!


I have a similar named range for another chart that simply expands when data is inserted (up to 100 rows but this is plenty!):

=OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$D$1:$D$100)-1)

Because data always stems from week 1 each time the category X axis label of =Sheet1!$A$2:$A$100 works fine!!

Its probably something really simple and minor but I am not the most advanced Excel user, espcially when it comes to this stuff.


Many thanks,


Susie

 

[baitmaster]

I'm not entirely sure I have fully understood the requirement, but I think you are having problems because you are trying to add data into a dataset, where your chart looks directly at that dataset: resulting in a need to continually change your chart to match your data

When I have had to automate systems like this, I have always included 3 stages in the process, where I think you are using 2:

1. your dataset, which varies each day/week/month, but is only data
2. a "charting" data set that contains functionality to pull in the latest 13 weeks of results only, but which itself is entirely static, i.e. you don't add anything to it, or move it around. It updates automatically based on time, or some other lookup parameter you build in
3. your chart, pointing at the charting data set, instead of your maintained dataset - this means your chart is also static, and hence does not need maintaining

Anything that needs to change, is handled purely by formulas in your charting dataset. E.G. SUMIF, LOOKUP, MATCH, OFFSET etc

Think about whether this approach might fix your problems, and ask again if this hasn't helped

 

[susieF]

Hi baitmaster,
That makes sense in principle I shall research how to do what you say

Thanks for the speedy response!!

Susie

 

[susieF]

Hi again,

I cannot get this to work, and I cannot see anyway to upload an attachment to my posts as I have a sample file that would make what I need much more self-explanatory.

Can anyone help, as I do not have FTP access where I am so cannot upload to a hosting account either!!

Susie