Creating a chart for a year so that only data for most recent 15 weeks shows..

agwalsh
agwalsh used Ask the Experts™
on
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..
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
There are a couple of ways round this:

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
Finance Analyst
Commented:
See attached with both options.

Option 1: 52 weeks of data and then a separate table of 5 weeks worth, pulling data from the relevant weeks in the main table based on inputs top left of sheet.

Option 2: Only one set of data but graph is fed from 4 different dynamic ranges setup as Defined Named Range using OFFSET function to determine range again using the inputs top left.

Thanks
Rob H
Dynamic-Graph-2.xls

Author

Commented:
Oh man, this is brilliant...I LOVE this (and sorry for not getting back to you sooner :-)
A

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial