Data Shuttle Conditional Formatting?

Kayla Q

Hi all,

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:

1. Existing data updates
2. Data is added as a new row
3. 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:

1. 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:

1. 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?"

Mike TV

@Kayla Q

Create a new sheet and a second Data Shuttle workflow. The new Data Shuttle workflow will replace all rows in the target sheet. Then write a formula in the open/close column to search for your unique identifier in the new sheet. If it's found, the formula should write Open in the cell. Use an IFERROR for when it gets a #NOMATCH and that response will be Closed.

Mike TV

@Kayla Q

Create a new sheet and a second Data Shuttle workflow. The new Data Shuttle workflow will replace all rows in the target sheet. Then write a formula in the open/close column to search for your unique identifier in the new sheet. If it's found, the formula should write Open in the cell. Use an IFERROR for when it gets a #NOMATCH and that response will be Closed.

Kayla Q

@Mike TV That is SO smart!

This hilarious thing is that the entity providing this data just today added a new column to the source sheet. The source sheet now has all projects on it (regardless of open or closed status), and the new column indicates the open/closed status.

I guess I wasn't the only one experiencing this issue - I was just the only one willing to deal with it as is :)