Excel graph with offset formula so it updates with data is add

Hi,

Does the attached help.

To be able to use a Pivot Table/Chart the data needs to be in a different layout. See sheet Data.
I've also added the dynamic range name 'data' to the data set and a one line macro to the Pivot Table sheet activate event which refreshes the chart every time you select the sheet.

Hi,

You can't.

New data should be entered in the new data layout sheet I gave you. That's the only way you can utilise a Pivot Table/Chart

Yes, if the original data can't be fed directly into the three column database without transformation then the smart way is as you suggest a pre-processing macro to take the data in its original format and convert it to the new.

I think this is what you want.

First I unhid some of your stuff so I could actually see what was there. Column E threw me for a loop for a while.

I made a data validation in Cell A17 to look up which car you want to plot.

In Cell B18, I have the formula: =MATCH(A17,A3:A15,0) – this gives me the row, relative to the date row, on which the car is found.

In Cell B19, I have another helper cell with the formula: =COUNTA(INDIRECT(B18+2&":"&B18+2))-5. The -5 is there because we don’t want to “count” the information in columns A:E. This gets a count of how much data there is on the row for the selected car.

Then I have two named dynamic ranges:
P_Date =OFFSET('MAIN DATA'!$F$2,0,0,1,'MAIN DATA'!$B$19)
P_Values =OFFSET(P_Date,'MAIN DATA'!$B$18,0)

I selected the existing chart and right clicked and selected Select Data and where you had the fixed data, I substituted these names.

I also clicked in the chart title went up to the formula bar and then selected Cell A17 so the chart title reflects the Car selection.

To get more information on named dynamic ranges and dynamic charting see these articles.
http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
http://www.utteraccess.com/wiki/inde...namic_Charting