Auto update dataset

**nickcegar** · 03-30-2009, 11:39 AM

Hi I am new excel and still trying to establish which of the many funtions would be best for the following requirments.

I am looking for away to dynamically update a data range with a limit of 30 records.

e.g Cell A1 has a new value entered in to it every 1 second.

Lets say data range to be stored from C1 to C30.

Everytime the data is entered in A1 it adds the value to C1, with the value in C1 moving to C2, value in C2 moving to C3 etc up to C30.

After C30 the data is deleted hence - only the previous 30 secs of data is shown at one time.

Hope you can help!!!!

**bitterenemy** · 03-30-2009, 12:12 PM

Do you want the macro to be populating the cell A1 in your example? Is the 1 second interval exactly what you're looking for? Where will the macro be pulling the A1 info from?

I know that most of what you're doing is doable, but I just need a bit more info to give an answer.

**nickcegar** · 03-30-2009, 12:21 PM

hi bitterenemy,

A1 will be pulling the data from anothersheet which is dynamically linked to a live prices server.

So I just need to record the value in A1 every 1 second (thats how often it updates) into a data range that displays the last 30 seconds worth of data.....

hope that makes it clearer???

**bitterenemy** · 03-30-2009, 01:17 PM

This might help, except I can see how it might be hard to break since your information is being updated at small intervals. Put this code in the worksheet it will be called from. The worksheet_change event runs automatically whenever anything changes in your worksheet.

Please Login or Register  to view this content.

You may want to make a macro with a keyboard shortcut which will turn on and off application events so you can break this while it's running. Something like...

Please Login or Register  to view this content.

(I did not test the above code...) You could put this on a keyboard shortcut so you can stop this from running if you need to.

Let me know if this works.