How can I move columns from a row to individual tasks/ rows in a different sheet?

ksunderlin

I am trying to conduct a survey at 100+ locations and use the survey results to generate a "custom" action plan for each site based on the responses. Once the action plan/ task list has been created I will need to track the project status of the task completion. The logic for the action plans is easy to address. I'm having issues figuring out how to convert the column based responses from the Smartsheet form/ survey into individual tasks that can be tracked and built into various reports based on location and relevancy to different teams. I gave a simplified version below, but in practice there are about 50 questions on the survey and 100+ locations so I'm trying to limit the amount of manual intervention.

Any help or thoughts would be greatly appreciated.

For a simple example:

Survey:

1. Are the lights on? Yes/ No
2. Is there coffee? Yes/ No/ Unsure
3. Are the walls blue? Yes/ No

Action Plan per site:

1. If yes, turn them off.
2. If <> yes, order more coffee.
3. If no, paint the walls blue.

Andrée Starå

Hi @ksunderlin

I hope you're well and safe!

My suggestions would be something like this.

• Add an auto number column to the main sheet.
• Add a so-called helper sheet
• Add a sheet-summary field where you'd input the ID that you want to collect the answers for
• Use VLOOKUP or INDEX/MATCH to collect each column answer to each row (reference the sheet summary ID)
• Maybe structure formula based on the answers to show the text as needed or if they are empty.
• Make a copy of the helper sheet and create one for each site or similar
• Create reports as needed

What do you think?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @ksunderlin

I hope you're well and safe!

My suggestions would be something like this.

• Add an auto number column to the main sheet.
• Add a so-called helper sheet
• Add a sheet-summary field where you'd input the ID that you want to collect the answers for
• Use VLOOKUP or INDEX/MATCH to collect each column answer to each row (reference the sheet summary ID)
• Maybe structure formula based on the answers to show the text as needed or if they are empty.
• Make a copy of the helper sheet and create one for each site or similar
• Create reports as needed

What do you think?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ksunderlin

That makes sense and gives me some ideas to try. I think it could work.

Andrée Starå

@ksunderlin

Excellent!

Happy to help!