A moving auto population of cells based on date

[aevans7]

Hi,

I have learnt so much from this forum and have reached the point where I need to ask for some help! I am working on a workbook whereby the data needs to be stored each week in an archive sheet. Is there any way of doing this automatically? If this could be an action that is performed each time the workbook is opened that would be brill! The example shows (in a very basic form) what I would be after!

Thanks

[TABLE="width: 223"]
<COLGROUP><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 48pt" span=3 width=64><TBODY>[TR]
[TD="width: 105, bgcolor: transparent"]Example[/TD]
[TD="class: xl75, width: 128, bgcolor: transparent, colspan: 2"]Today's date: 05/12/13[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Date[/TD]
[TD="class: xl76, bgcolor: transparent"]Data 1[/TD]
[TD="class: xl76, bgcolor: transparent"]Data 2[/TD]
[TD="class: xl77, bgcolor: transparent"]Data 3[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]04/11/2013[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]1638[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]3533[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]247[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]11/11/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]506[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4654[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]4243[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]18/11/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]485[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4108[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1614[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]25/11/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3503[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4581[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3630[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]02/12/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4152[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2412[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3414[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]09/12/2013[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]16/12/2013[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]23/12/2013[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]30/12/2013[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]06/01/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

 

[oldbrewer]

why do you need to archive data? can you add all new data to your table in sheet 2 and pull from it (in sheet 1) only the last 2 weeks worth of data?

 

[aevans7]

I need the past data stored from each week to provide a look back comparrison. The data set is around 40 c by 3,5000 r, and I perform various countifs on the data. I then need to be able to refer back to previous data at a fixed point in time to demonstrate progress and throughput. It would be the result of these countifs that I would need pulled over on a weekly bases whilst still retaining the previous data.

Thanks

 

[oldbrewer]

So if you keep all your data on sheet 2 and analyse it on sheet one you are sorted.