Auto Data Range AND Filter Criteria

Hi

I have data that looks like this:
Model Week Failures
29M40 2007-W20 8
29M40 2007-W20 10
21Z40 2007-W20 12
29M40 2007-W21 9
21Z40 2007-W22 10
29M40 2007-W22 8


1) I need to create a chart that will display the failures between two Week Numbers entered in cells on the worksheet - the update must be triggered by the cell value changing.

2) Pls see attached to see what the chart should look like...

3) The workbook must be able to be opened on different pc's

4) The range where the data is captured must automatically update as data is added

5) Using Excel 2002

I tried using queries which works nicely with the cell values as parameters which trigger automatically, but ran into problem trying to use the workbook in other locations, and I could not get the data for both models shown on the same chart.

Please give me some ideas how I can try to do this

Cheers

Hi Richard,

The attached workbook might serve as a starting-point for programming what you require. It's 100% "native" Excel - no VBA code is involved.

The chart updates automatically when extra data rows are added.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

Hi Greg

Thanks for your reply!!

That looks like what I want but how did you do it?

Specifically:
a) Get the chart to realize the new source data area as you add new data
b) Get the data filtered by the dates selected?


Regards

Richard

Hi again Richard,

Select the "Model Failure Rates" worksheet & take a look at the Names defined in the workbook. (You can ignore "z29M40_NEW" and "z29M40_OLD" - I should have deleted them before saving the workbook.)

The Names are not normal named ranges, but rather formulae which define dynamic ranges. Try adding a few extra lines of data and you'll see that the "Weeks" range automatically expands to include them - clever, eh? Please note however that you cannot have any blank rows WITHIN the data range.

The "Weeks_Graph" range is updated dynamically on the basis of the values in cells H1 and H2, which in turn are determined by the weeks selected in cells G1 and G2.

The model number ranges are also dynamic and are determined by specific offsets from the "Weeks_Graph" range.

And here's where it all comes together - the series defined for the graph are not specified using specific range notation (e.g. "$A$1:$C$7"), but rather by using the dynamic named ranges mentioned above.

So basically, you add new data or select a different time window, the named ranges update automatically, the chart is linked to the named ranges so the chart updates automatically too.

Hope this helps - please let me know how you get on.

Best regards,

Greg M