Cross sheet ref to find who's elligible for a job based on credentials

I give up - I need help with a seemingly easy formula pls.
I have the 2 sheets below - One is list of crew and their credentials. Other is a list of Jobs and the Required Credentials to work each job. The formula I used in the 'who is elligible' column is only listing exact matches. (Bob can work the Ace job because it has 3 reqs and he meets all 3). I need it to go a step further and collect Bob and Joe's names as elligible for the Farm job, and the Gas job etc etc. Right now I just get various error messages or is simply doesn't pull in more than one name where applicable.

Thanks in advance!! -Shelley

Formulas tried so far:

In the Job sheet=JOIN(COLLECT({crew name}, IF([req1]@row = 1, {REQ1}, {name2}), IF([req1]@row = 1, 1, {name2}), IF([req2]@row = 1, {REQ2}, {name2}), IF([req2]@row = 1, 1, {name2}), IF([req3]@row = 1, {REQ3}, {name2}), IF([req3]@row = 1, 1, {name2})), ", ")

In a metrics sheet=JOIN(COLLECT({crew name}, {crewcreda}, IF(INDEX({jobcreda}, MATCH(job@row , {jobname}, 0)) = 1, 1, {crewcreda}), {crewcredb}, IF(INDEX({jobcredb}, MATCH(job@row , {jobname}, 0)) = 1, 1, {crewcredb}), {crewcredc}, IF(INDEX({jobcredc}, MATCH(job@row , {jobname}, 0)) = 1, 1, {crewcredc})))

In a metrics sheet=IFERROR(JOIN(COLLECT({Name},{cred1}, IF(IFERROR(INDEX({Req1}, match(job@row , {jobname}, 0)), 0) = 1, 1, ">=0"),{cred2}, IF(IFERROR(INDEX({req2}, MATCH([Job]@row , {jobname}, 0)), 0) = 1, 1, ">=0"),{cred3}, IF(IFERROR(INDEX({Req3}, MATCH([Job]@row , {jobname}, 0)), 0) = 1, 1, ">=0")),CHAR(10)),"-")

Find more posts tagged with

Accepted answers

All comments

Jgorsich

You are on the right track with a Join(collect()), but I think your if() statements are breaking it.

I think you want to work on the Job sheet, and remember that a checked box counts as a 1 and an unchecked box as a 0 (so you can use a ">=" comparison to check if the employee has a checkmark for a requirement without excluding someone who has too many requirements).

Something like the following might work:

=Join(collect({crew name},{REQ1},@cell >= [req1]@row, {REQ2}, @cell >= [req2]@row ,{REQ3}, @cell >= [req3]@row ),", ")

This way you don't have to mess with any of the if() statements. A 1 (checked box) will always be greater than or equal to a 0 (unchecked box) and vice versa.

DKazatsky2

Hi @sdeclerk,

The formula you need for the job sheet is fairly complicated - try this.

=JOIN(COLLECT({crew name}, {REQ1}, OR(AND(@cell = 0, @cell = [req1]@row ), AND(@cell = 1, @cell >= [req1]@row )), {REQ2}, OR(AND(@cell = 0, @cell = [req2]@row ), AND(@cell = 1, @cell >= [req2]@row )), {REQ3}, OR(AND(@cell = 0, @cell = [req3]@row ), AND(@cell = 1, @cell >= [req3]@row ))), ", ")

I'm not quite clear on what your metrics sheet is supposed to look like - if you want to share an example of that I can try to assist there as well.

Hope this helps,

Dave

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.