Stopping a formula from updating after a givendate

[mick6623]

Hey, does anyone have a way of stopping a formula from updating after a given date has been reached.

The idea is to produce a weekly summary of outstanding jobs
Row 2 tracks the number of Normal and High Priority Jobs in progress i.e the number of outstanding enquiries. 'Build Log'! F:F contains the date the enquiry was raised. K1 is the end date of the week in question

Row 2
{=SUM(('Build Log'!$C:$C="High Priority")*('Build Log'!$F:$F<=K1))+SUM(('Build Log'!$C:$C="Normal Priority")*('Build Log'!$F:$F<=K1))}

It does the job but when I change an outstanding enquiry from say 'high priority' to 'complete' it updates all the previous weeks totals.
Really I want row 2 to remain fixed at whatever value it was when on when the date in row 1 was reached.

How should I be going about this?


-- removed inline image ---

 

[asad]

Add something like this in the begining of formula:

If(today()>A1,

and then your formula here.

 

[mick6623]

Yeah that solves half the problem but how can I get it to maintain the value it had on the last day of the previous week?

 

[asad]

You can try using circular referance if you want to do it by formula, or the other way is probably using VBA.
For circular ref you will first have to go in options > Calculations > Check Iteration and set the Maximum Iteration value to 100.
Then use something like this in A3:

=IF(TODAY() < =A1,B2-A2,A3)