How can I identify multi-select cell values that are not in the dropdown list?

I have a multi select column "Cost Code." There are currently values in the column that are not within the dropdown list.

For the "Accessory Code" I was able to check and flag these via the following formula where I reference a column in a separate

=IF(CONTAINS([Accessory Type]@row, {A&A Column Selections Range 1}), 0, 1)

However, the same formula for a multi select column does not work. It is returning all values as false.

Find more posts tagged with

Formulas

Accepted answers

All comments

Malaina Hudson

Hello, @Bailey Neal .

This may not be a direct answer to your question, but since I also experience the "stragglers" in drop-downs, I've used 2 approaches to housekeeping on this dependent upon my user groups.

Users with high risk of variability to list - I added an Other to my main list and a second column for them to define their Other. Then I locked my list to only allow list items. I have a weekly schedule to go in and validate the Other, and add them to the list if they are more than one-offs. Locking the list prevents people from typing in free-form responses.
Users with a low risk of variability to list, I left the multi-select unlocked, but I have a housekeeping item weekly to check the list for variants and then to add them to the list. The mechanism I use for is simply to convert the column to Text/Number format, then turn it back to Multi-Select format. The only gotcha I've found in this method is the risk of multi-word entries to mean one thing that may be converted to one-word items in the list.

Bailey Neal

Thanks Malaina. I was hopeful there was an explanation why the contains function didn't work as it seems to me that it should, or that there was an alternative formula that might solve the problem

Amy Flores C

The way I tackled this (today funny enough) was I created a report for the sheet, and then grouped the info on the sheet by the offending column. Once I seen the duplicate that was spelled/upper-cased that was different from my list I knew which one(s) I needed to correct. I ensure my dropdowns are alphabetical, and if it's a longer list will copy it to a word doc so I can compare the report to the list (I have more than one screen to work from).

Hope that might help with your housekeeping of dirty data :)

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.