Copy row for all columns before "Yes" Column
I am working on a project, and there will be MANY times that there will be multiple requests. This column is a dropdown with 2 options, "Yes" or "No". If the response is "Yes" I would like the data from that row, all columns PRIOR to that column copied to the next row. If the dropdown selection is "No" nothing is done. This would NOT be indented as a child, it would be identical in every way. I reviewed automation, and could only find information copying to another sheet. This must be a part of the same sheet. This column on the RIGHT that I have circled would indicate if the row would get copied to the next row, and it would be that whole role from the left aft the number to the last column BEFORE Multiple request. Which could be Yes again (then it would be auto-copied), or it could be No.
Sherry Fox
Data Science & Reporting Specialist | Information Technology
United HealthCare Services (UHS)
EAP | Mobilizer | Automagician | Superstar | Community Champion
https://www.linkedin.com/in/sherryfox/
Best Answer
-
Hi @Sherry Fox,
I'm not sure if this is doable on a single sheet. What you can do is set up another sheet (with the same columns) and use automation to clear columns to the right of "Multiple Requests" (including this if you wish), and then once these become blank have the row copied back to the original sheet.
The "mirror" sheet being "New Sheet 2" for terms of the automation.
On the original sheet, the following automation will move the row to the mirror sheet once Multiple Requests is changed to "Yes".
On the mirror sheet (New Sheet 2), there are 2 bits of automation:
This clears data in the multiple requests and column the right (if there are additional, these should be added, if you don't want the Multiple Requests cleared then take it out).
This second automation then moves the now cleaned up row back to the original sheet. If you want to retain the data in "Multiple Requests", then I would suggest adding a hidden column to be cleared by the first automation and use this instead. The row will then be re-inserted at the bottom of the original sheet with data on the left still intact and anything else removed. If you want to see what rows are going through the mirror, then you can use Copy Rows instead of Move Rows.
An example of what you'd get from changing Row 1 to "Yes" using the above:
As with all Smartsheet automation it may not be instantaneous, so don't be alarmed if it doesn't happen right away!
Answers
-
Hi @Sherry Fox,
I'm not sure if this is doable on a single sheet. What you can do is set up another sheet (with the same columns) and use automation to clear columns to the right of "Multiple Requests" (including this if you wish), and then once these become blank have the row copied back to the original sheet.
The "mirror" sheet being "New Sheet 2" for terms of the automation.
On the original sheet, the following automation will move the row to the mirror sheet once Multiple Requests is changed to "Yes".
On the mirror sheet (New Sheet 2), there are 2 bits of automation:
This clears data in the multiple requests and column the right (if there are additional, these should be added, if you don't want the Multiple Requests cleared then take it out).
This second automation then moves the now cleaned up row back to the original sheet. If you want to retain the data in "Multiple Requests", then I would suggest adding a hidden column to be cleared by the first automation and use this instead. The row will then be re-inserted at the bottom of the original sheet with data on the left still intact and anything else removed. If you want to see what rows are going through the mirror, then you can use Copy Rows instead of Move Rows.
An example of what you'd get from changing Row 1 to "Yes" using the above:
As with all Smartsheet automation it may not be instantaneous, so don't be alarmed if it doesn't happen right away!
-
Thanks so much for the detailed response. I am anxious to give this a try!!! 🦊
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
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!