Auto update cells

P.Bushong

I have two columns Status and Closed Date

[Status]; data type is lookup (Open, Pending, More Info, Closed)

[Data Closed]:  data type calendar.

Is there a way that I can automatically update the [Date Closed] column with [Status] has been set to "Closed"

 

Mike Wilday

This is possible but only with a third-party service like Zapier. A better way might be to have your users input a date when they close something and then have the status update to closed when there is a date in that field. 



Would that work for you? 

P.Bushong

the users would be setting the pick list to closed.

Can a formula be put into the drop down column?

Jennifer Whitlock

Mike,

 

Do you actually know how to do this via Zapier? I use both services and have been having this same issue.

Mike Wilday

I have played with Zapier to get this working but found that the cost of Zapier was too enormous. I happen to know that Smartsheet is experimenting with some new features that would grab the specific date that a cell was modified, but I don't have any clarity on how soon this type of a feature would be implemented. 

@J Craig Williams has had explicit experience working with Zapier and wrote several blog posts on how to do it. Here's one http://ronin-global.com/2016/12/11/smartsheet-fixed-date-upon-completion/. 

Hopefully that helps! 

 

Mike Wilday

A formula can be put into a dropdown column. But it can't put a specific timestamped date into another column. Formulas insert data into the same field that it exists in. To circumvent the timestamp we had the users input a date and automated the status field. Basically instead of providing dropdowns to choose from, we provided Actual Start Date, Actual Finish Date and had the status column populate with the status based on whether there was an Actual Start Date or an Actual finish date. You can set the status automatically but you CANNOT set a timestamped date into a cell when they choose a specific dropdown option. 

P.Bushong

Thanks

Mike Wilday

Sure thing. I hope that helps. 

Mike Wilday

@Jennifer, did you see the link I posted below to the Zapier post? I hope that helps. 

Andrée Starå

@Mike

I also work with Zapier all the time.

What was the enormous cost? Do you mean price/time? Everything is relative! 

The most significant factor for the cost of Zapier is how often it should check and trigger if true and how many tasks you spend because of it per month. 

So basically, in this case, the cost depends on how often you'd want to check for a change in the sheet in combination with how many tasks it uses if the Zap triggers.

Make sense?

Andrée Starå

Hi Jennifer,

I work with Zapier all the time in my business and for my client's workflow needs.

Let me know if you have questions!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Jennifer Whitlock

Thanks! I may be reaching out very soon as I'm having a difficulty with constantly getting errors for Zaps. 

Andrée Starå

I'm always happy to help!

Mike Wilday

Yes, we just found that the data we wanted to zap would quickly increase the price. And that it wasn't justified since we were already paying so much for smartsheet as a service. I'm hoping the Modified Date for a specific cell functionality comes into fruition! This would save a lot of people a lot of money.

Andrée Starå

Yes, it can increase rapidly.

Yes, the Cell Modified Date would be an excellent addition, and many of my own and clients workflows with Zapier would probably not be needed when it comes. (think positive)

Have a fantastic weekend!