Formula to stop counting if a value is met.

spinmaniac2

New Member
Joined
Oct 12, 2017
Messages
2
Hi all, I am having issues with a nested formula (unless someone had a better idea...lol) here is what I have

A1 - Date (this is manually entered)
B1 - Age (in days) using the following formula - =IF(ISBLANK(A2),"",TODAY()-A2)
C1 - Status - this is manually entered - "Completed", "in progress" etc.

What I am trying to figure out is how to get the formula in B1 to stop counting, and retain the last value, if C1 states "completed"

Any ideas?

Thanks so much.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Welcome to the Board!

This requires VBA. So you can change the value in B1 from a formula to a hard-coded value so that it does not change when C1 is set to "Completed".

I am not sure if you want this to happen on just row 1, or all of column C. I am assuming all of column C.

Here is the code that you need:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run when a single cell is manually updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run when column C is updated
    If Target.Column = 3 And LCase(Target.Value) = "completed" Then
        Application.EnableEvents = False
        Target.Offset(0, -1).Value = Target.Offset(0, -1).Value
        Application.EnableEvents = True
    End If

End Sub
To put it in the proper place, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code into the resulting VB Editor window. It will run automatically when any value in column C is updated to "completed".
 
Upvote 0

Forum statistics

Threads
1,223,975
Messages
6,175,749
Members
452,667
Latest member
vanessavalentino83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top