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?




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!