Automated conditional data update in cell

[hollypop1986]

Hey guys,

I need to automate a process in my excel sheet and I am not even sure what terminology to search for in order to find what I need.

I have a tasks list that I share with various people on my team. There are a few recurring tasks that happen on a regular basis. Right now, I have to go in and manually change the date once it has been completed but I want to know if there is a way I can set the cell to roll over to the next date (whether it is set for once a week, every day, every work day, monthly, etc.) when a certain condition is met, namely, once the task has been marked "complete."

Please tell me there is a way to automate this system? It is driving me mad constantly having to clean up the sheet after the team when they just mark the task complete but do not update the date to the next period. It causes some tasks to fall between the cracks too.

Thank you!

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter Completed into any row in column G the script will run

You need to enter into column C the frequency as number of days.
If every six months enter 180
If every 10 days enter 10
If every 90 days enter 90

Lets try this and see if it works. If you do not like entering numbers like that then I need to know all the possible frequencies.
Like Monthly Daily Weekly Yearly
I would need to know the exact terms.

The script will update date in column B and change value in column G to Not Started

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G:G")) Is Nothing Then
'Modified 4-18-18 9:35 PM EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
Dim r As Long
r = Target.Row
Dim ans As String
ans = Target.Value
If ans = "Completed" Then
Cells(r, 2).Value = DateAdd("d", Cells(r, "c").Value, Cells(r, 2).Value)
Target.Value = "Not Started"
End If
End If
Application.EnableEvents = True
End Sub

 

[hollypop1986]

This works perfectly! Thank you! Now I just need to analyze your code so I can learn about it!


Thanks again!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

This works perfectly! Thank you! Now I just need to analyze your code so I can learn about it!


Thanks again!