Calculate events during a certain period

No, the Data doesn't have Week information.
The relation with time will be made through the formula today()

Added a file, maybe I'm not explaining well

Item A changes each week, and when we go to week 24, I would like to keep the information from previous week,

is it possible?

Thank you

Up, canīt solve this in automatically way, I could use like a paste special every week to hold those values, but the intention was having a mechanism for doing this,

Originally Posted by sktneer

Please find the attachment to see if this is something you can work with.

Iīve added some comments in the file, and the formulas in column E

Thanks sktneer your macro does solve the raised problem,
is there any way I can reproduce what you did by a macro but through a formula? (I'm not a great vba programmer)

If not what should I copy from your macro to add extra columns where it gets information from a specified cell?

-----------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng, cell As Range
Dim lr, n As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A3:A" & lr)
Application.EnableEvents = False
If Not Intersect(Target, Range("H25")) Is Nothing Then
For Each cell In rng
If WorksheetFunction.WeekNum(cell, 2) = WorksheetFunction.WeekNum(Date, 2) Then
n = cell.Row
Exit For
End If
Next cell
Cells(n, "E") = Cells(25, "H")
End If
Application.EnableEvents = True
End Sub

-------------------------------------------------------------------------------------------------------------------------------

Originally Posted by oeldere

reply to your question in #10

If you add new data in your table and refresh the pivot table you have the total of the item during the year (and the total per week).

Hi Oeldere,

When you update the pivot table you will have the same problem, because you use a column to define which is the present week,
the ones that are not return 0 " =IF(D27=1;$H$25;0) "
the pivot table will not hold the values for the past weeks, or are you suggesting a different approach?

Hi,

Some time I didnīt look at this again, but today founded a solution through a formula instead of using a Macro (that would solve this, but I'm not a "pro" in vba),using a circular reference I was able to hold the value of past week, you will get an alarm about his, then is a matter of going to Excel option > Formulas, and Enable iterative calculation check box, better explained here http://office.microsoft.com/en-us/ex...010066243.aspx