Say Cell A1 = Net Worth and it is updated periodically (sometimes weekly, sometimes monthly).

Instead of manually creating another table to store all past values in A1, is it possible to auto transfer these historical values in A1 to create my bar graph?

I want to eliminate the extra step of creating another table if possible.

Also like to know if I can plot the % change for every value without first creating the table too. Thank you.

As far as I know, the data has to sit somewhere. Changing the value in one cell deletes the previous value in that cell unless the old value has been written somewhere else. The location of the historical data doesn't need to be visible on a sheet - but it does need to exist somewhere.

I would have thought you need two data points for each new value: The applicable date and the value on that date.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

The attached contains the very basics of a way to do it.
Changing the value of cell A1 will update the chart with a timestamp of when you do it.

How?
Unhide Sheet2
See the vba code of Sheet1


Instead of a plain chart, you could have a pivot chart based on the data in sheet2 and have a column in the pivot showing % change. Then you'd need an extra line of code to refresh the pivot.

Attached Files

msofficeforums47888.xlsm (22.7 KB, 5 views)