NEED TO ADD LAST COMPLETION DATE IN COMPLETION DATE COLUMN

Rob_PM · 01/28/22

I refresh my sheet.. So to contact support I just follow the steps on Smartsheet Support page, correct?

Jeff Reisman · 01/28/22

https://community.smartsheet.com/discussion/comment/317771#Comment_317771

That's correct. Good luck, sorry we couldn't get this working. It's so weird.

Rob_PM · 01/28/22

I will contact support and let you know..

Thanks for your help

Rob

Rob_PM · 01/28/22

Jeff

The support response:

Thank you for providing the community post. I was able to see the issue in the automation.

To prevent an infinite loop or circular reference, actions that will automatically modify the sheet cannot be triggered by inbound cell-links, cross-sheet formulas, or formulas that refer to another cell with an inbound cell link or cross sheet formula. This includes Request an approval, Move rows, Copy rows, Lock rows, Unlock rows, Record a date, and Assign people.

For example, if the Status cell on the row is populated by a formula pointing to a cell with a cell link from another sheet, changes to that cell value through the cell link will not trigger a workflow that locks the row when Status changes to Complete.

Some workflows can be triggered by cross-sheet formulas, it depends on the type of trigger and the action block. Actions that modify a sheet (such as Move Row, Assign People, Record a Date, etc) are the ones that can't be triggered directly from a cell link or cross-sheet formula.

I would suggest creating a Workflow that doesn't modify the sheet, like notifications, reminders, or an update request to be sent weekly.

I have attached a help article for your reference:

https://help.smartsheet.com/articles/2479236-trigger-blocks-define-when-your-workflow-is-executed

Jeff Reisman · 01/28/22

Tell them you need to talk to second level support. Your automation rule doesn't do what they are saying. It fires once a day by a schedule, based on a calculated cell being a certain value and a date field being blank, AND IT WORKS FOR OTHER USERS. If it can't work, why did I watch it work yesterday and today?

Rob_PM · 01/28/22

Ok I will ask second level support

Rob

Rob_PM · 02/05/22

Jeff

Support never got back to me. I am trying to find a solution. I added this formula to the CompletedDate and it worked but registered today's date: =IF(CompleteCount@row = "4", TODAY(), "")

but if I added that formula today, for all columns that have "completed" will show today's date. Any idea how to change this formula to register the last column update date?

Thanks

Rob

Jeff Reisman · 02/07/22

https://community.smartsheet.com/discussion/comment/318749#Comment_318749

I just read something from Andree Stara regarding setting date/time values using a little trick. So here's an idea you can try:

Set up automation rule that triggers when your complete count = 4, to copy the row over to a helper sheet. Make sure the helper sheet has the system column for Created date included. The Created date on the helper sheet row will be the date and time that the row was copied.

Back on your main sheet, use an INDEX/MATCH in your Completed Date column to pull the Created date value back from the helper sheet, by matching the Location column values:

=IFERROR(INDEX({Helper sheet Created date}, MATCH(Location@row, {Helper sheet Location}, 0)), "")

Rob_PM · 02/10/22

Jeff

I contact the support and they told me that automation don't work with cell-link and formulas.

I have another question about automation and I would like to know if you can help me. This is the link ALERT SOMEONE — Smartsheet Community

I will appreciate very much if you could help me

Thanks

Rob

Jeff Reisman · 02/10/22

https://community.smartsheet.com/discussion/comment/319249#Comment_319249

Someone there is giving you the run-around. That's crazy. At least SOME automation works with cell-links and formulas. What doesn't work with cell-links and formulas is when the cell change triggering the automation is one made by cell-link.

With time-based automation, the automation is triggered by a time and date being reached. If the conditions it's looking for are met, then it completes its action. It doesn't matter how those conditions were reached, whether by cell-link, formula, or manual entry.

I'm going to run my working automation again, at 10am eastern this morning:

My Status D column in the first row is referencing this remote cell, and when this cell = "Jeff", the formula sets the StatusD value to "Complete", otherwise leaves it blank:

The formula in the CompleteCount column for the top two rows counts the columns from StatusA to StatusD that have a value of "Complete".

StatusD column in the top row is the one set by formula based on remote cell value. StatusA through StatusD columns in the second row are set by manual entry.

The CompletedDate column is blank for both rows.

Now to set up my test, I start here:

StatusD is blank for both rows. CompleteCount counts the three existing "Complete" entries in the other three Status columns.

I type "Complete" in the StatusD cell in the second row, and the CompleteCount updates to 4.

On my remote sheet, I change the referenced cell to "Jeff," and the StatusD column on the first row changes to "Complete" and the first row CompleteCount updates to 4.

Now we wait a few more minutes for 10am EST to roll around... Will update shortly.

Andrée Starå · 02/10/22

Hi @Rob_PM

I hope you're well and safe!

They've must have misunderstood because it's in fact possible, but you'll have to set it to trigger by date instead.

Make sense?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!