Automate Workflow to Refresh 2 Sheets based on adding to another sheet
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???
Sherry Fox
Data Science & Reporting Specialist | Information Technology
United HealthCare Services (UHS)
EAP | Mobilizer | Automagician | Superstar | Community Champion
https://www.linkedin.com/in/sherryfox/
Answers
-
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.
-
Since Sheet B & Sheet C are all formulas, any "move automation" like you mention would not work.
Sherry Fox
Data Science & Reporting Specialist | Information Technology
United HealthCare Services (UHS)
EAP | Mobilizer | Automagician | Superstar | Community Champion
https://www.linkedin.com/in/sherryfox/
-
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.
-
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.
Sherry Fox
Data Science & Reporting Specialist | Information Technology
United HealthCare Services (UHS)
EAP | Mobilizer | Automagician | Superstar | Community Champion
https://www.linkedin.com/in/sherryfox/
-
@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:
- 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.
- 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.
- 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.
-
Thanks so much, that works great! I never would have thought of that!!!!
Sherry Fox
Data Science & Reporting Specialist | Information Technology
United HealthCare Services (UHS)
EAP | Mobilizer | Automagician | Superstar | Community Champion
https://www.linkedin.com/in/sherryfox/
-
@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!
-
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.
Sherry Fox
Data Science & Reporting Specialist | Information Technology
United HealthCare Services (UHS)
EAP | Mobilizer | Automagician | Superstar | Community Champion
https://www.linkedin.com/in/sherryfox/
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!