nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Help needed - Conditional Formatting a multi-answers cell

LauraCIC

Hi all,

I have tried about everything I could to create a functional rule of conditional formatting but to no avail. Here is a detailed look into what I have and need :

I have around 5 different columns for which I need a rule to apply individually. Basically, each column respresents a task, while the row represents in which project the task is. Each cell of each column contains the abbreviated names of the staff members who need to be delegated for this given task. This formatting cannot be changed due to the nature of my work and my needs for this sheet. Once the user is officially delegated, a tick mark is added to their abbreviated name (for example, I would be listed as Lau as long as I am not delegated for the task, and would then be listed as Lau ✓ once delegated). Therefore, a cell can contain ticked names and unticked names simultaneously until everyone is appropriately delegated.

Now for the issue at hand : we need a way to see rapidly cells containing non-delegated users, as to know the delegation is not complete for a given task. We would need a rule making cells different when containing at least one unticked user (whether it be with a red background color, red font color, or really anything that stands out).

Could anyone help me ? Thank you!

Find more posts tagged with

Sheets

Formulas

Conditional Formatting

Accepted answers

Paul Newcome

Sorry about that. I missed a closing parenthesis:

=IF(COUNTM([Back-ups (DOR)]@row) <> LEN([Back-ups (DOR)]@row) - LEN(SUBSTITUTE([Back-ups (DOR)]@row, "✓", "")), 1)

Ker9

I got the same error with Paul's formula. I added some parenthesis and got it to work for me:

=IF(COUNTM([Back-ups (DOR)]@row) <> (LEN([Back-ups (DOR)]@row) - LEN(SUBSTITUTE([Back-ups (DOR)]@row, "✓", ""))), 1)

Apparently posted same time Paul did. - Paul, you are brilliant!

All comments

Paul Newcome

This should be possible using a helper column, but could you share a screenshot (sample data is fine) for context, so I can be sure I understand your structure?

LauraCIC

Of course. Is this screenshot sufficient ?

Paul Newcome

Perfect. You are going to need to replace the "✓" in the formula below with the actual checkmark used, but the formula is going to look like this:

=IF(COUNTM([Back-ups (DOR)]@row) <> LEN([Back-ups (DOR)]@row) - LEN(SUBSTITUTE([Back-ups (DOR)]@row, "✓", ""), 1)

This would go into a flag type column, and you would set your conditional formatting to be applied when this helper column is flagged. You will need separate helper columns for each of the different delegation columns you want formatted.

There is a way to do this with a single helper column, but that formula is a little more complex. My suggestion would be separate helper columns (that you can hide after setting up to keep the sheet more tidy).

LauraCIC

Wow, this really is getting somewhere finally! Instead of being labled as unparseable, it now is labled as #INCORRECTARGUMENTSET.

Here is a copy of your formula I applied - in which I changed the checkmark for ours -, do you see any issue :

=IF(COUNTM([Back-ups (DOR)]@row) <> LEN([Back-ups (DOR)]@row) - LEN(SUBSTITUTE([Back-ups (DOR)]@row, "✓", ""), 1)

Thanks again, I finally feel like I will make it!

Ker9

Will the names always have 3 characters not including checkmark (with checkmark always 4 characters)?

LauraCIC

We aim for it, but not always. For example, we cannot respect a 4 characters rule in the External (DOR) column; this is were we indicate users external to our organization, therefore need more details as we do not know them necessarily.

Paul Newcome

Sorry about that. I missed a closing parenthesis:

=IF(COUNTM([Back-ups (DOR)]@row) <> LEN([Back-ups (DOR)]@row) - LEN(SUBSTITUTE([Back-ups (DOR)]@row, "✓", "")), 1)

Ker9

I got the same error with Paul's formula. I added some parenthesis and got it to work for me:

=IF(COUNTM([Back-ups (DOR)]@row) <> (LEN([Back-ups (DOR)]@row) - LEN(SUBSTITUTE([Back-ups (DOR)]@row, "✓", ""))), 1)

Apparently posted same time Paul did. - Paul, you are brilliant!

Paul Newcome

@Ker9 Thanks!

I used the LEN() - LEN(SUBSTITUTE()) method in another solution to count commas (replaces the last one with ", and" in a JOIN/COLLECT).

I figure if it can count commas, it can count checks or any other character too. Comparing that count to the total number of selections made (multi-select dropdown made this a lot simpler by allowing for the COUNTM function) should tell us if there are any selections without checks.

LauraCIC

It works! Thank you both so much, you guys are incredible.

Paul Newcome

Happy to help. 👍️

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, category/bannerUrl, category/bannerImageAlt, category/extensions, category/customizationLabels, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/bannerImageAlt, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.