Copy row for all columns before "Yes" Column

Sherry Fox
Sherry Fox ✭✭✭✭✭✭
edited 08/01/22 in Formulas and Functions

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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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!

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    @Nick Korna ,

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!