Data Shuttle Conditional Formatting?
I had a chat with Pro Desk, and they couldn't come up with an answer for my question, so I'm turning to you all :)
I have an Excel data source with project information that I receive every two weeks. One row = one project.
One of three things could happen to this data source:
- Existing data updates
- Data is added as a new row
- Data is deleted, and a row is removed
Regarding #3, when data is deleted, it means the project associated with that row has closed.
I have set up a Data Shuttle workflow to add/update the data in the sheet I have created. I do not want to run the delete function, as I need to maintain historical project data. I have also set up the Data Shuttle with an additional column labeled "Open/Closed" - this column does not appear on the Excel source file. The purpose of this column is to track whether a project is open (present in the Excel source file) or closed (absent from the Excel source file).
Here's the rub. When a matter drops off of the Excel source file, I would like the information in the column to change from "Open" to "Closed."
Suggested solutions that do not work:
- Run an automation that looks at the modified date. After x amount of time with no modification, mark the project "Closed."
Why the solutions do not work:
- Projects could be open/dormant for years. There is no logic behind how long it sits unmodified that ties to the project being closed. If it is on the Excel source file, it is open - end of story.
So, fabulous Smartsheet Community, any suggestions for how I could get Project rows that drop off of the source data to update to "Closed?"
Help Article Resources
Check out the Formula Handbook template!