Replacing Formula in a Cell With the Value after the date has passed...

[largeneed]

Hi everyone,

Is there a way to change a cell to a value of a formula that was calculated but only for certain cells, and after the date has passed.

Here is the formula in a cell that I want to change to just the value :



=(COUNTIFS('Decision Tracking'!H:H,A7,'Decision Tracking'!I:I,""))

Which currently gives a value of 1

A7 in this case is today's date which is September 14, 2017.

So when it is tomorrow, I want the cell above to have just the result value of 1, and not have the formula.

Is this possible in excel ?

Will I have to use macro ?

Right now I'm just doing it manually at the end of each day . (Entering the result of the formula, and just putting the value in that cell.

 

[mrmmickle1]

Yes this is possible. Yes you will need to use a macro. You'll most likely need to store some kind of criteria in a cell or a custom document property... I would consider an Event Procedure or a WorkBook Open Procedure.

Sub Test()
    If Date > "Cell with Previous Date? or some other criteria" Then
        Range("A7") = Range("A7")
    End If
End Sub

Would need more detail to help with your exact issue.

 

[largeneed]

Yes this is possible. Yes you will need to use a macro. You'll most likely need to store some kind of criteria in a cell or a custom document property... I would consider an Event Procedure or a WorkBook Open Procedure.

Sub Test()
    If Date > "Cell with Previous Date? or some other criteria" Then
        Range("A7") = Range("A7")
    End If
End Sub

Would need more detail to help with your exact issue.

Hi Thank you so much for replying.

I don't know what a workbook open procedure is, but I can answer your other questions.

Sub Test()
    If Date > "Not yesterday, and not today (should only apply to yesterday's cells" Then
        Range("A7") = Range("A7")
    End If
End Sub

Here is how column A is setup:

A
[TABLE="width: 80"]
<colgroup><col></colgroup><tbody>[TR]
[TD]row 7 - 14-Sep-17[/TD]
[/TR]
[TR]
[TD]row 8 - 15-Sep-17[/TD]
[/TR]
[TR]
[TD]row 9 -18-Sep-17[/TD]
[/TR]
[TR]
[TD]row 10 - 19-Sep-17

The date is populated by the formula: =WORKDAY(A6,1)[/TD]
[/TR]
</tbody>[/TABLE]

Where a6 is the previous day.

So to recap, once it is tomorrow, the cell which contains this formula:

=(COUNTIFS('Decision Tracking'!H:H,A7,'Decision Tracking'!I:I,""))

Should then be only the value that the formula calculated on Sept 14, 2017, which in this case is 1.

I will research workbook procedure's today when I get home. Thank you again!

 

[mrmmickle1]

What column is your formula in? Should be something like this:

Private Sub Workbook_Open()
    Dim strtRow As Integer
    Dim LRow As Integer
    Dim intLp As Integer
    
    'The row we want to start at....
    strtRow = 7
    'Define the last row in column A
    LRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For intLp = strtRow To LRow
        If Range("A" & intLp) = Date - 1 Then
            Range("YOUR FORMULA CELL RANGE HERE") = Range("YOUR FORMULA CELL RANGE HERE")
            Exit For
        End If
    Next
End Sub