agwalsh
asked on
Creating a chart for a year so that only data for most recent 15 weeks shows..
Hi
I have a chart for a year and what I want the chart to do automatically is just show the last specified number of weeks (without me having to change data source every week). e.g. if I am in in week 10 of a year and I only want the chart to show the last 5 weeks, when I add data for week 11, what will appear in my chart (automatically) are weeks 6-11 inclusive. Equally when I add data for week 12, the chart will now just show week 7-12 inclusive.
My gut feeling is that it is some sort of dynamic range creation but am looking forward to seeing your answers..
I have a chart for a year and what I want the chart to do automatically is just show the last specified number of weeks (without me having to change data source every week). e.g. if I am in in week 10 of a year and I only want the chart to show the last 5 weeks, when I add data for week 11, what will appear in my chart (automatically) are weeks 6-11 inclusive. Equally when I add data for week 12, the chart will now just show week 7-12 inclusive.
My gut feeling is that it is some sort of dynamic range creation but am looking forward to seeing your answers..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh man, this is brilliant...I LOVE this (and sorry for not getting back to you sooner :-)
A
A
1) Setup a separate data source for the graph. This secondary data source is linked to the primary source and will change dynamically when the specified criteria change. For example you have columns of primary data for weeks 1 to 52 but the secondary data is formula linked, eg with lookups, to the relevant columns based on a week number input.
2) As suggested use a dynamic range name for the graph data. I will have a look to see if I can figure this one out, might need to make some assumptions with the data.
Thanks
Rob H