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!

 

[yky]

Not sure what you want. Look at the following scenario:

You have this task with a date, say, April 11, 2018. When the task is done, it is marked "completed". So, you have:

April 11th, 2018 Completed.

Now, you want to change the date, what about the status of the task, namely the word "Completed"? Do you want to keep it? Is this what you want?

April 18th, 2018 Completed

Isn't this confusing? If you don't want the word "Completed" there, you can remove it but, then, where is the record for the task dated April 11th, 2018? Maybe you don't care and don't need it. Don't know.

 

[RasGhul]

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!

Do these tasks regularly restart depending on month or task start date?

 

[hollypop1986]

Do these tasks regularly restart depending on month or task start date?

Yes, it would be in regular intervals. There are certain tasks that have to be done once a week, other that have to be done monthly, or every 6 months, etc.

 

[hollypop1986]

Sorry, yeah, I was hoping not to come off confusing, but it is inevitable!

Using your example:
April 11th, 2018 In Progress

Ideally, what I would like to happen is upon marking the cell in that row completed, that the date would then reset like this:
April 11th, 2018 In Progress

Kind of like a work flow, I guess? But I do not know if you can set those kind of rules in Excel.

 

[My Aswer Is This]

holly:
Please give specific details and I may be able to help you.

Say something like this:

I have:

Wash Car in column A of row 2
I have Monthly in column B Row 2
And I have last day car was washed in column C of row 2
So for example I have 1/1/18 in column C
When I complete car wash I double click on Wash Car in column A and if Today is
1/15/18 the date in column C is advanced by 30 days so now column C would show 2/15/18

Or maybe you would want last time car was washed in column C and next car wash is do on in column D

See if you explain things this way I could help you.

The script would look in column B for frequency like Daily Monthly of Yearly.

Or maybe you should in column B the number of days like 1 for daily 30 for monthly or 365 for yearly or 45 for every 45 days.

 

[hollypop1986]

Here is what I have on my spreadsheet:

Column A: Task
Column B: Deadline
Column C: Created by
Column D: Responsible Party
Column E: Notes
Column F: Status (drop down with Not Started, In Progress, Deferred, Pending, Completed as options)
Column G: Priority (drop down with Low, Medium, High, and Immediate as options)
Column H: Tag

[TABLE="width: 923"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Task[/TD]
[TD]Deadline[/TD]
[TD]Created by[/TD]
[TD]Responsible[/TD]
[TD]Notes[/TD]
[TD]Status[/TD]
[TD]Priority[/TD]
[TD]Tag[/TD]
[/TR]
[TR]
[TD]Follow-up with Dale Pederson for Cisco about training video[/TD]
[TD="align: right"]4/5/2018 [/TD]
[TD]Holly Guerrero[/TD]
[TD]Holly Guerrero[/TD]
[TD]emailed {3/12}; emailed {4/5}; emailed {4/13}[/TD]
[TD]In Progress[/TD]
[TD]Medium[/TD]
[TD]TRAINING[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 923"]
<tbody>[TR]
[TD="class: xl66, width: 210"]NMSDC Certification Renewal[/TD]
[TD="class: xl67, width: 101, align: right"]4/16/2018 [/TD]
[TD="class: xl66, width: 88"]Holly Guerrero[/TD]
[TD="class: xl68, width: 105"]Holly Guerrero[/TD]
[TD="class: xl70, width: 209"]supplier gateway[/TD]
[TD="class: xl69, width: 66"]Not Started[/TD]
[TD="class: xl69, width: 80"]High[/TD]
[TD="class: xl72, width: 64"]ADMIN[/TD]
[/TR]
</tbody>[/TABLE]


This is an example. The first line above would not need to be repeated and would be listed as a one-time event. The second, however, would need to be renewed every 6 months. I could add a column named Frequency right after the deadline column. How then could I link them to make the change?

Thanks for your help!

 

[hollypop1986]

Ha, I don't know why the third row showed up like a table and the first two rows did not.

 

[My Aswer Is This]

Thanks for that very clear explanation.
You said:

The first line above would not need to be repeated and would be listed as a one-time event​

At present from looking at what you have provided how would I know the first line was a one time event?

And when you say:

would not need to be repeated

What does that mean?
Do you mean you would want that entire row copied and pasted after the last current row?

So do you want a script written where if you double click on column A you want Completed entered in column F

And the entire row copied and pasted one row after current last row
A new date entered in column B
And not started entered in our new row column F

Or you tell me when you double click on column A what do you want the script to do.
A script can do almost anything but it needs exact instructions on what to do and when to do it.

Or we can have the script run when completed is entered in column F.

Look forward to hearing back from you.
​

 

[hollypop1986]

Ah ha, I see what you are asking. Okay, so lets say I add in the Frequency column. This is currently missing from my spreadsheet and would be necessary for anyone who is not familiar with the spreadsheet to know whether or not it is a recurring task (this is what I meant by repeated, that is recurring). My spreadsheet would then look like this:

Column A: Task
Column B: Deadline
Column C: Frequency
Column D: Created by
Column E: Responsible Party
Column F: Notes
Column G: Status (drop down with Not Started, In Progress, Deferred, Pending, Completed as options)
Column H: Priority (drop down with Low, Medium, High, and Immediate as options)
Column I: Tag

This is what I want: If a task is a recurring task, then when the Status (Column G) is set to Completed (Column G), I want that to trigger the new deadline (Column B) to update to the next interval (indicated in Column C) and the Status (Column G) to be reset to Not Started.

In the snippet I included above, the last line is a renewal that must be done every 6 months. The deadline was 4/16. When I update the current status to Completed, I want that action to trigger the deadline to update to 10/16/18 (6 months later) and to reset the status to Not Started. At this point, it is not important to keep a record of the previously completed tasks, so that does not need to be a consideration.

Thank you for being so thorough!