Formula issue

Susan van Niekerk

Hi Brain Trust

We have a formula set up to report when two columns don't have the same drop-downs selected.

=IF([Candidate Compliance & Training Check]@row = [Job Compliance & Training Requirements]@row, "Low", "High")

The team now want to use the following symbols

What or how can I set up a formula that will give me :-

Empty symbol when Candidate Compliance is empty

Full When both columns Match

and half when the drop down counts are there but arent exact

Using the 25% and 75% would be great but dont see how as each row will have different number if requirements

Find more posts tagged with

Accepted answers

All comments

Susan van Niekerk

This would have to be if Candidate Compliance is 25 % of the contents of Job Compliance ect ???

Debbie Sawyer

Have a go at changing the column type for "CC Check" to this one:

Then amend your formula to:

=IF(ISBLANK([Candidate Compliance & Training Check]@row),"Empty",IF(

[Candidate Compliance & Training Check]@row = [Job Compliance & Training Requirements]@row, "Full", "Half"))

Does that work for you?

Kind regards

Debbie

Debbie Sawyer

CSSO (Chief Smartsheet Solutions Officer)

Smarter Business Processes

Smartsheet Community Champion & Ambassador

Book your free consultation Consultancy | Training | Smarter Apps | API Integration

Debbie Sawyer

Alternatively if you wanted to use ALL the options you would need 3 helper columns (which you could then hide).

"Count of Job Compliance Entries" (column name) with a formula of

=COUNTM([Job Compliance & Training Requirements]@row)

"Count of Candidate Compliance Entries" (column name) with a formula of

=COUNTM([Candidate Compliance & Training Check]@row)

These two columns would give you the number of Multi-select entries chosen for each row.

Then you'd have a 3rd helper column with "% difference" (column name)

=[Count of Job Compliance Entries]@row/[Count of Candidate Compliance Entries]@row

Which you'd then display as a % formatted column.

Then your formula could be:

=IF(ISBLANK([Candidate Compliance & Training Check]@row),"Empty",IF([Candidate Compliance & Training Check]@row = [Job Compliance & Training Requirements]@row, "Full", IF(AND([% difference]@row>0,[% difference]@row<0.5),"Quarter",IF(AND([% difference]@row>=0.5,[% difference]@row<0.75),"Half","Three Quarter"))))

The issue with this is that it is just counting the entries and not comparing the values of the entries. However it will only mark as FULL if both columns are an exact match…

Might work!?

Kind regards

Debbie

Susan van Niekerk

Hi Debbie

Thanks that has worked well, Another Question - is there any formula that could be used to return what the missing values are EG-

A third column that would return any value that is in the Job Compliance but not in the Candidate Compliance?

so in the sample below - third column would report HSSE Drivers license, CSE ect ect

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.