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! :)

Find more posts tagged with

Sheets

Formulas

Cell linking

Accepted answers

Genevieve P.

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

All comments

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

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

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.

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.

Would this work for you?

Cheers,

Genevieve

CAI-RAS

Thank you @Genevieve P.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.