Formula Needed For Combining Rows with Exact Values in Separate Sheet

Hello Smartsheet Community,

I have a compliance tracker sheet that captures employee's HIPAA and FEMA certifications. I have been tasked with creating a dynamic view for their immediate supervisors. I have two separate views for FEMA and then HIPPA. However, I also have employees who have submitted separate reports for each individual FEMA module. And I would like in the dynamic view for these individuals to only appear once, however I am seeing an individual appear 5x (even if they have only 4 FEMA rows), there HIPPA row will appear too. So is there a formula or some type of way (whether creating a separate sheet/or running the view through a report) for me to combine these separate cells/rows into one row when the same individual submits multiple forms.

Thank you in advance,

Alan

Public Health

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @AlanBal

    I hope you're well and safe!

    Yes, if there is something unique for each submitter that we could use to "connect" the records, we could use cross-sheet formulas and either VLOOKUP or INDEX/MATCH to collect it together in a so-called helper sheet.

    Make sense?

    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 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: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hello Andree,

    I figured I needed to follow a similar process, but the part where I am stuck on is that I need individual records from the same individual to be joined together under certain conditions. So is there a way or how would I create a dynamic view with set conditions? Ex: Alan has submitted 3 separate forms, 2 for FEMA and 1 for HIPPA. I need all the FEMA rows to be be joined in the dynamic view or a separate sheet so that each individual appears only once in the dynamic view. However, I have noticed that when I filter just for rows with FEMA in the dynamic view, an individual's HIPPA row will also appear.