Save old values of a cell before or after refresh

**asandy1234** · 06-11-2010, 02:31 PM

Hi Geeks,
I've a problem which may be very simple for the gurus here.
There is a webquery which refreshes data every 1 minute and based on these datas, I calculate 10 KPI's and chart them in a graph.
The graph only gives me a snapshot at that time and I want to do trend analysis for all these KPI's for which I need to store these previous KPI values.
Is there a way to store these previous KPI values in a column on refresh and I need only the last x number of values to be stored so that I can do the trend analysis.

Thanks,

**Launchnet** · 06-12-2010, 04:17 PM

Hi . . . Welcome to the forum

How many KPI values are there and what cells are they located in?

What cells are available to store them in?

Do you want to restore them again later with another click of a button?

**asandy1234** · 06-14-2010, 11:04 AM

Originally Posted by Launchnet

Hi . . . Welcome to the forum

How many KPI values are there and what cells are they located in?

What cells are available to store them in?

Do you want to restore them again later with another click of a button?

Hi,
Thanks for the reply. There are currently 8 KPI's.
All three are in different worksheets now, for example the webquery is in sheet 1, all the caluculated KPI's are in sheet 2 and the graph which is based on these KPI's is in sheet 3.The KPI table is of just 2 columns with 8 rows having name in one column and the calculated value which changes according to the refreshed data in the other column. The graph is a simple plot graph plotting all the 8 values in y axis and name in x axis. I don't want to restore the values later.

Thanks.

**Launchnet** · 06-14-2010, 01:33 PM

Good Morning California Time . . .

From the info you sent, I think I can answer for you. Will give it a good shot.

It would save time and help me if you could attach a sample workbook so I could work with actual data and layout.

Thanks
Matt

**asandy1234** · 06-14-2010, 04:39 PM

HI Matt,
I've attached a sample workbook along wit this .

Thanks,
Andy

**Launchnet** · 06-17-2010, 12:21 AM

Hi
Sorry I won't beable to work on this until this week end. But, if I can work it in I will earlier.

**Launchnet** · 06-19-2010, 07:06 PM

I am assuming a couple of things, which sometimes is not good, but it will get us started.

1. Copy columns A & b to lets say E & F. Paste special and check values only.

This gives you a backup of data on the same page.

You could also do this with a macro button, but probably not necessary if my assumption is correct.

NOW . . . You have the data backed up.

2. If you change your chart links to use columns E & F, you still get the same graph. The only time that your graph will then change is when you copy the current data columns A & B to columns E & F.

3. The chart will stay as is until you update the columns E & F from A & B.

If my assumption is not correct, please comment on necessary changes.