Consider the following data set:

Data Set A:
A2:B4 where (A2) = "Apples", (B2)="25", (A3)="Bread", (B3)="04", (A4)="Milk"
and (B4)="12" where the values (B2:B4) change on a daily and random basis,
but the series values (A2:A4) remain the same.

Then I have another range of cells, D1:G4 the purpose of which is to hold
the historical values of the different series mentioned in A2:A4, on specific
dates, as follows:

Data Set B:
(D1)="05 Jan 09", (E1)="12 Jan 09", (F1)="19 Jan 09", (G1)="26 Jan 09" and
where C2:C4 is the same series as contained in A2:A4 above, i.e. (C2) =
"Apples", (C3)="Bread", (C4)="Milk".

I need a formula that will look at the value of each series in B2:B4 of Data
Set A, an if TODAY is = to the date in E1:G1, the value of that series will
be transferred
into the relvant cell and frozen there for trend analysis purposes. E.g. If
today's date is 12 Jan 09, then whatever the values are for Apples, Bread and
Milk as contained in B2:B4, will automatically be transferred and stored in
F2:F4. Once the value is stored it will not change further and becomes a
historic "snapshot" of the static values for that day. Then the same process
occurs for the next week when TODAY =19 Jan 09, the values contained in B2:B4
will again be stored in G2:G4 and so on.

I have tried working with = between TODAY and the given target date, but it
does not "store" the data, and keeps dynamically updating the entire set of
"hisotric" data to the current values of the dynamic set in B2:B4.

Please hep and thanks in advance!

Thanks jasontferrell!

Could you advise me on where/how I would implement this code into my
spreadsheet? Have never done something like this before. Thanks!

"jasontferrell" wrote:

Without code, I think the formula solution you seek would be
cumbersome to keep the historic values and get the new values with the
same formula. You might be better off with a subroutine that you
could run whenever the sheet was opened or whenever the values change:
Dim sht As Worksheet
Dim x As Integer
Set sht = ActiveSheet
For x = 4 To 7
If Date = DateValue(sht.Cells(1, x).Value) Then
sht.Cells(2, x).Value = sht.Cells(2, 2).Value
sht.Cells(3, x).Value = sht.Cells(3, 2).Value
sht.Cells(4, x).Value = sht.Cells(4, 2).Value
End If
Next x
Set sht = Nothing