Sheet to sheet workflows


I want to link two sheets together by creating a new row in sheet 2 based on values in sheet 1. Example below:

Sheet 1 has either a multi-select column or several checkbox or yes/no columns (any works fine for my needs). The input in these columns tells the next team working on this project to perform these set of tasks. I want to track each task separately in sheet 2 as a new row and need to pull some data from sheet 1 (project name, selected tasks, user, etc) but not all of the data tracked in Sheet 1.

So the data would move like this:

I don't want to pre-populate sheet 2 with any data to search from. This would create more work for users - I'd like it to be as simple as when the user says yes or no in the task columns, sheet 2 will automatically add a new row with the appropriate info. Is this possible?

Thanks for the help!



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    My suggestion would be to insert a column on the first sheet that has a formula outputting the next task. Something along the lines of...

    =IF([Task #3]@row = "Yes", "Completed", IF([Task #2]@row = "Yes", "Task 3", IF([Task #1]@row = "Yes", "Task 2", "Task 1")))

    Then you can set up a Copy Row automation to copy the row over to sheet two any time this new column changes to any value other than "Complete". The Copy Row automation will pull all columns, but you can hide all of those extra columns that aren't needed.

  • Thanks for the idea Paul, I'll give this a try. I played around with the Copy Row automation and hiding columns but every time I reopened the new sheet or a new row was added, the hidden columns were made visible again, which I wanted to avoid. But if I can't get around this, I think the team can live with that solution.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you save the sheet with the columns hidden, they should not be visible when you open it again unless someone un-hid the columns and then saved.

  • Ah, tricks of the trade. Thanks again Paul.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!