Automate Workflow to Refresh 2 Sheets based on adding to another sheet

Sherry Fox

I have a Sheet, let's call it Sheet A. This sheet is a simple data entry list of Unique IDs. I have built another sheet, called Sheet B, to calculate various pieces of the projects for these Unique IDs. For Each Unique ID there is a parent and 5 children (the children are identical on each of these). Sheet B contains a helper column that includes either the parent or child name along with the Unique ID. My other sheet is Sheet C, which converts the data in Sheet B into a Single line (and multiple extra columns for each parent row) in order to create data for our dashboard. Now currently whenever I am given new Unique #s, I manually add them to Sheet A, then Save and Refresh. Then open Sheen B, Refresh and Save. And finally open Sheet C and Refresh and Save. So is it possible to create a workflow that whenever Sheet A has data added it can refresh Sheet B and Save, then Refresh and Save Sheet C? Is this even possible???

Lucas Rayala

Hi @Sherry Fox , presumably the other pages would eventually refresh, it just takes time? If so, there’s very little that will speed the process up. You might try adding some links to the other pages on your first page. That might help trigger a refresh. Similarly, maybe try writing some data to your other pages via a move automation that’s synced with the creation of your new lines ( just make sure you aren’t overwriting data you need). But the lag can be very annoying and make some processes impossible to implement in SS.

Sherry Fox

@Lucas Rayala ,

Since Sheet B & Sheet C are all formulas, any "move automation" like you mention would not work.

Lucas Rayala

Unless you put additional column off to the right and hid them. I’m not saying it’s a great idea 😀 but might be worth tinkering with.

Sherry Fox

@Lucas Rayala ,

Actually, on Sheet A is only raw data, Sheet B is all formulas and 1 text column (parent/child), that generates one of 4 columns designed to sequentially number ONLY the parent rows. This allows the INDEX/MATCH to be based off the Index of Sheet A. Now Sheet C, well, it is 100% formulas. And Sheet C is 1 row per Parent (no children), and this runs the dashboard.

Lucas Rayala

@Sherry Fox I cheated and asked ChatGPT. Here's a response, seems legit:

Sherry, to automate the refresh and save process between Sheet A, Sheet B, and Sheet C without using the Smartsheet API, you can leverage the built-in features within Smartsheet itself. Here's what you can do:

1. Create a report: Instead of working directly with Sheet B and Sheet C, create a report that combines the necessary columns and data from Sheet A, Sheet B, and Sheet C. The report will dynamically update to reflect changes made in the source sheets.
2. Set up an update request: In Sheet A, add a column called "Update Request." Whenever you add new data to Sheet A, mark the corresponding row in the "Update Request" column.
3. Create a workflow rule: In Sheet B, set up a workflow rule that triggers when a row is marked for an update. Configure the rule to refresh and save Sheet C automatically.

To set up the workflow rule:

• Open Sheet B and navigate to the "Automation" tab.
• Click on "Create Workflow Rule."
• Configure the rule to trigger when the "Update Request" column is marked.
• Add an action to refresh and save Sheet C.

With this setup, whenever you add new data to Sheet A and mark the corresponding row in the "Update Request" column, the workflow rule in Sheet B will be triggered. It will automatically refresh and save Sheet C, ensuring that the dashboard data is always up to date.

By utilizing the report feature and workflow rules within Smartsheet, you can achieve a certain level of automation for the refresh and save process without the need for external scripting or relying on the Smartsheet API.

Sherry Fox

@Lucas Rayala ,

Thanks so much, that works great! I never would have thought of that!!!!

Lucas Rayala

@Sherry Fox I've been finding that Chat GPT has some seriously solid insight on problems like this. I'm clever, but it would have taken me a day of testing to come up with a solution for this issue. Glad it worked out!

Sherry Fox

@Lucas Rayala ,

I have recently purchased Kat Norton's (aka: Miss Excel) course on GPT Chat, as she has helped me with Excel so much, and I was already advanced as it is. I just have not started that course as of yet.