Automation in one sheet to change cell in another sheet?

Matt Rasmussen

I have two sheets in our workspace, one is the master list of projects we are working on, the other is used to record the amount of time each person works on a project for a given date.

Projects sheet		Timesheet sheet
Project ID		Project ID
Project Name		Staff Name
Status			Date
Priority		Hours Worked
etc.			etc.

A project gets a default status of “Requested” when it comes in via the intake sheet. When work is recorded in the Timesheet, we want to have Automation on the Timesheet with the "When rows are added" trigger to change the Status column in the Projects sheet to “Active” rather than having a project manager or another person remember to manually update the project status.  

This is a simple SQL statement in a database but I can’t seem to find a way to have Automation in one sheet update another sheet. Is this possible? We have the Enterprise license of Smartsheet. I’ve read some forum posts that suggest two or three helper sheets but 1) I’m too new to Smartsheet to follow the discussion, and 2) simple update triggers should be possible since cells can already reference other sheets.

Thanks for your help!

Kelly Moore

Hey Matt

I try as much as possible to have my Status column determined by rules, and not manual input. This means we have a series of IFs (nested IFs) that account conditions that define the different dropdown selections. All that being said, rules can't account for everything so I sometimes have to add an 'override' column where manual selection is possible. For this column I may limit the status selection to High Risk (or whatever your company calls this), Completed and Cancelled (and maybe On Hold). All other choices (particularly 'In Progress' or 'Active'!) I force using through formula rules- this aids in data consistency but most importantly it ensures getting the appropriate escalation as soon as possible so that the help can be provided to that project.

If you begin to think of rules for your drop down selection (under what condition should 'a dropdown choice be a selected), then we can help you put that together.

@Andrée Starå might have a different trigger in mind, but if I was using Andrée's approach, I would have a helper column in the Project sheet with the COUNTIFs formula in it. I would then trigger the automation Change Cell Value in the Project sheet when this helper column value equaled 1. Using the value '1' will trigger only on the 1st occurrence of that project ID.

=COUNTIFS({your Timesheet column Project ID}, [Project ID]@row, {your Timesheet Hours Worked column}, ISNUMBER(@cell)

Let us know what help you need and we'll be glad to assist

Kelly

Kelly Moore

Hey @Matt Rasmussen

If I understand you correctly, you should be able to do this via formula in your Project sheet. This approach assumes all the responses in your Status column are formula driven - not updated manually.

An Index/Match can be used if only one criteria matches Projects, an Index/Collection if more criteria are needed to further filter the results.

You could put this IF statement into the string of nested IFs I assume already populate your Status column.

The formula below looks to see if the project ID exists in the timesheet with a number in the Hours worked column (if a formula automatically populates a zero in this timesheet cell we may have to add a different criteria). If the cell exi

=IF(COUNTIFS({your Timesheet column Project ID}, [Project ID]@row, {your Timesheet Hours Worked column}, ISNUMBER(@cell))>0, INDEX({your Timesheet Hours Worked column}, MATCH([Project ID]@row, {your Timesheet column Project ID},0)), "Active")

Will this work for you?

Kelly

Matt Rasmussen

Hi @Kelly Moore

Thanks for your answer. I can see now how that would work but our Status column is a dropdown with other values that we assign. For instance: Closed, On Hold, Rejected, etc. A project may be approved but work doesn't start on it for some time and then people forget to update the status. This is an attempt to automate it when that happens so our reporting and dashboards correctly reflect the project status.

Your formula could help if there's a way to update the Projects sheet even though the Status field is a dropdown, but I don't see a way to make that happen.

Any other suggestions?

Andrée Starå

Hi @Matt Rasmussen

I hope you're well and safe!

To add to Kelly's excellent advice/answer.

• Have you explored using Workflow combined with the Change cell Action?

Would that work/help?

I hope that helps!

Have a fantastic week & Happy New Year!

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!

Kelly Moore

Hey Matt

I try as much as possible to have my Status column determined by rules, and not manual input. This means we have a series of IFs (nested IFs) that account conditions that define the different dropdown selections. All that being said, rules can't account for everything so I sometimes have to add an 'override' column where manual selection is possible. For this column I may limit the status selection to High Risk (or whatever your company calls this), Completed and Cancelled (and maybe On Hold). All other choices (particularly 'In Progress' or 'Active'!) I force using through formula rules- this aids in data consistency but most importantly it ensures getting the appropriate escalation as soon as possible so that the help can be provided to that project.

If you begin to think of rules for your drop down selection (under what condition should 'a dropdown choice be a selected), then we can help you put that together.

@Andrée Starå might have a different trigger in mind, but if I was using Andrée's approach, I would have a helper column in the Project sheet with the COUNTIFs formula in it. I would then trigger the automation Change Cell Value in the Project sheet when this helper column value equaled 1. Using the value '1' will trigger only on the 1st occurrence of that project ID.

=COUNTIFS({your Timesheet column Project ID}, [Project ID]@row, {your Timesheet Hours Worked column}, ISNUMBER(@cell)

Let us know what help you need and we'll be glad to assist

Kelly

Matt Rasmussen

UPDATE ON WHAT WORKED FOR ME:

I followed the suggestion given by @Kelly Moore to count if the record exists and while we did it slightly differently, it works perfectly. We made one difference to the formula, which was to count the number of hours worked to be able to report that and use it on our dashboard. I used a formula column titled "Last 180 days IT hours" on the Projects sheet with the formula =SUMIFS({IT Timesheets Total Hours}, {IT Timesheets Range Project ID}, = [Project Number]@row, {IT Timesheets Range Week Ending}, (TODAY() - 180))

I then added a "change cell value" automation workflow on the Projects sheet that runs nightly with the conditions of "Project Status" is one of Requested, Blank and where "Last 180 days IT hours" is greater than 0 to change the cell value in "Project Status" to Active.

This solution updated all the rows that had been overlooked and now automatically updates the relevant rows on a nightly basis. No manual intervention on rows if we can help it! Thanks to Kelly and I hope this example can benefit others in the future.