Is it possible to develop a task list from multiple source sheets through automation?

dhall

We are creating new courses and content for our LMS and we want to use a project sheet to track all the tasks associated with developing the course and its respective content from beginning to end.

We have a handful of different "Learning Object" types that each have their own repeated tasks or activities to develop and create that type of learning object.

Once the project owner has created the course outline and has determined which learning object types they'll need to create for the course, we want to be able to quickly populate a new project sheet, or add to their current one, from source sheets.

I read through this article (Automatically move or copy rows between sheets | Smartsheet Learning Center) but the way I'm reading it implies you can set one source sheet to copy the rows to one destination sheet and that's it. We want to be able to use that source sheet over and over and over, repeatedly.

Right now, the only feasible way of doing it would be to go into each source sheet, one at a time, and perform the copy to another sheet method (Copy Rows to Another Sheet | Smartsheet Learning Center), which can be time consuming if we have a course that needs multiple different learning objects planned for it.

What I am imagining is we select the destination sheet, then we select the learning object order and it will automatically pull copies of the rows from the source sheets to the destination sheet in that order. Is this possible or is it a lofty dream?

Amanda Alv

Hi,

You are able to use Automation to do a one time "Copy row" workflow to a destination sheet, over and over. It will not limit you to one action, but to your point, it will need to be done for each source sheet each time you need to copy info over.

I'm not familiar with your existing source sheets, but is it possible to consolidate them into one large Master file? Or copy them to one Master File? From there, add in a supporting column that designates which type of learning object the rows are for. At that point, anytime you are setting up a new form, you simply go into the Master File and do one automation, designating the rows that should be copied based on the Object Type.

Amanda Alv

Hi,

You are able to use Automation to do a one time "Copy row" workflow to a destination sheet, over and over. It will not limit you to one action, but to your point, it will need to be done for each source sheet each time you need to copy info over.

I'm not familiar with your existing source sheets, but is it possible to consolidate them into one large Master file? Or copy them to one Master File? From there, add in a supporting column that designates which type of learning object the rows are for. At that point, anytime you are setting up a new form, you simply go into the Master File and do one automation, designating the rows that should be copied based on the Object Type.

Andrée Starå

Hi @dhall

I hope you're well and safe!

To add to Amanda's excellent advice/answer.

• I'd recommend adding a checkbox or similar to the Parent for the group, and you check the parents of the groups you'd like to include and then have a filter show the parent rows that are checked and select them to copy to the destination sheet.

Make sense?

• Another way would be to use cross-sheet formulas, and the source would act as a database, and then by selecting what to include in the destination sheet, it would "collect" the correct information from the source.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

dhall

Hi @Andrée Starå and @Amanda Alv ,

Thanks for the insight. We can definitely do a master sheet and highlight the sections we want to copy over, that will at least save us from jumping from sheet to sheet. We'll also add the column with the Learning Object Type so we can do that filtering and easily find those within the Master Sheet.

I'm not familiar with cross-sheet formulas so I'd have to look into that functionality to help automate it a bit better.

Thanks so much! We'll keep playing around with it.

Andrée Starå

@dhall

Excellent!

Happy to help!

Cross-sheet Formulas - More info.

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!