Dynamic data update

[gigiw1986]

Hi..I have a table like below

Date P1 P2 P3 P4
4/24/2010 22 33 44 55


Suppose i have these values P1,P2,P3,P4 obtained by VLOOKUP of The data from another table.Also this another table(Table1) is refreshed daily,so P1,P2,P3,P4 keep changing daily.However,i need to keep track of the changes and store each days data in another table.

For example,if today is 25th then the above table changes to

Date P1 P2 P3 P4
4/25/2010 42 3 54 89

but i need to have a central table like the below(Table3) where each days values are copied.so that i form a repository(i am doing this because when i said another table(Table1it is refreshed daily and is a pivot table so cannot store values on a daily basis,)

Table 3

Date P1 P2 P3 P4
4/21 12 13 9 76
4/22 33 43 62 12
4/23 21 32 43 54
4/24 22 33 44 55
4/25 42 3 54 89

Can i write a macro or is there an easier way to do this in excel..Please help...

Please look at the uploaded file for details on the problem...

[venkat1926]

you can create a macro and run this macro at the end of the day

suppose the downloaded(or refreshed) data in sheet 2(repeat sheet2) from A2 to E2 (row 1 has the column headings like date, p1,o2,o3 p4. the repository sheet is SHEET3.

the macro to run is repository given below (AT THE END OF THE DAY)

Please Login or Register  to view this content.

now the question is how NOT TO forget running the macro before closing for the day

So i have created an event code . before closing the file at the end of the day automatically it will ask you whether you have run the mAcro. If the reply is "yes" then it asks whether it is to be saved . respond yes.. If your reply is NO then closing the file automatically canceled and you can go back and run the macro

open vb editor (alt+F11)
click control+R
a "project" window opens on the left containing names of all the open files
go to your file and RIGHT click "this workbook"
in the resulting window copy this event code

Please Login or Register  to view this content.