Help using a multiple cell criteria from a source sheet to auto populate a formula sheet

I'm stuck. Really stuck. I've attempted more than 30 formulas. I hope someone can help me.

I am using a master HR data file with 1500+ rows (reference/source sheet) to auto-populate a training enrollment roster (formula sheet), only I don't want every person listed on the source sheet added to my formula sheet.

I need a formula that will look at my source sheet [Career Level] column and find multiple criteria [M2 (or) M3 (or) M4 (or) M5 (or) M6 (or) M7] -- when the criteria are met, I need the formula to return the @row data in the corresponding [Email] column to my enrollment roster formula sheet. Ultimately giving me a roster of email address specific to an employee's career level.

Source Sheet Column Names: [Email] & [Career Level]

Formula Sheet Column Name: [Email Address]

I dealing with a major blind spot! HELP PLEASE! :)

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @CAI-RAS

    You can build a formula which will bring in all Emails that match your criteria into one cell, but it won't be able to parse out individual emails to each row.

    I would suggest that the easier way to get this information would be to create a Report with a filter looking for the Career Level. Then you can Group by the email in order to see a unique list of emails on a row-by-row basis.

    Would this work for you?

    Cheers,

    Genevieve

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi@CAI-RAS

    Hope you are fine, Your formula will use Join With Collect Function.

    Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    Or if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.

    My Email for sharing : Bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • CAI-RAS
    CAI-RAS ✭✭

    Hi Bassam

    Here are two screenshots. Thanks for the help.

    If #1 [career level] = M2 (or) M3 (or) M4 (or) M5 (or) M6 (or) M7 (or) M8 (or) E1 (or) E2, then use column #2 [email]@row to auto-populate #3 [email address]@row on formula sheet.

  • CAI-RAS
    CAI-RAS ✭✭

    Hi SmartSheet Community.

    Can anyone help me with this one? (see thread) I've tried multiple formula's and I'm completely out of ideas.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @CAI-RAS

    You can build a formula which will bring in all Emails that match your criteria into one cell, but it won't be able to parse out individual emails to each row.

    I would suggest that the easier way to get this information would be to create a Report with a filter looking for the Career Level. Then you can Group by the email in order to see a unique list of emails on a row-by-row basis.

    Would this work for you?

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!