How can I convert multiple row items to individual tasks?

Options

I am looking to create a survey and "custom" action plan response for 90+ different sites. Each site would complete a simple questionnaire through a Smartsheet form. Based on the answers each site would then get an action plan based on how the question were answered. Once the individual tasks/ action plans have been assigned I need to be able to track the status of each item for each individual site. I would also like all the action plans to be on a master sheet so different reports can be created for different groups and regions. The individual logic for action plans I have figured out. I'm struggling with taking the multiple columns and breaking them down into individual rows on the Action Plans Master Sheet. We also have data mesh and the pivot apps available if it matters. Any thoughts or help would be greatly appreciated.

For a short example:

Survey:

  1. Are the lights on? Yes/ No/ Unsure
  2. Are the wall blue? Yes/ No
  3. Is the water warm? Yes/ No
  4. Are the doors closed? Yes/ No/ Some

Action Plan:

  1. If no, turn on the lights
  2. If yes, repaint them to green
  3. If No, check the boilers
  4. If the Yes, open some of them.


Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @ksunderlin

    Duplicate?

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @ksunderlin

    Duplicate?

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!