Restricted Dropdown - Find cells that don't comply

Peter Graham

Hello All

I am loving this community, it has helped me develop my sheets... so Thank you. This is my first discussion because I can't seem to find answer anywhere:

I have sheet's with multiple dropdown columns on sheet that have restricted values. All these sheets feed other sheets with formulas so it is important to have consistency on inputting correct info on dropdown columns.

I continue to get 'pop-up' to inform that "there are 2 cells in column with unrestricted values".

How can I find these cells to correct them?

Paul Newcome

@Peter Graham My apologies. For whatever reason I missed your screenshots from yesterday. I hope you were able to get it working. If not, below is a screenshot that explains my solution.

Using this method, you only need to enter the data in the [Allowed Column] one time. You can then reference the entire [Allowed Column] in the contains formula on each row in the Flag column.

You could even hide the [Allowed Column] if you wanted to keep the sheet looking cleaner.

Andrée Starå

Hi Peter,

Do you have any headers or similar?

If yes, try adding the values of those to the list.

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Peter Graham

Thanks Andree.

I'm confused on 'Headers'?

This is used as source sheet with over 2000 rows of information. I would like to know a method of checking each dropdown to make sure all cell's comply to restricted values. Or how to find the one's that don't?

I have manually done this from report and sort this cell then drag and drop all matching. This cleaned one column up however, some columns have over 200x Restricted values. Looking for quicker, easier way?

Paul Newcome

You could insert a flag column. Then insert an Allowed (text/number) column.

In the Allowed column, you can copy/paste your allowed values.

Then in the flag column you can use something along the lines of...

=IF(CONTAINS([Dropdown Column Name]@row, Allowed:Allowed), 0, 1)

This will flag any row that has something in the dropdown column that is not in the Allowed column. You can sort/filter, automate alerts/update requests, conditional formatting, reports, etc all based on this flag column.

Peter Graham

Hi Paul

Thanks for this however I can't get it to work....

Can you check you have the correct Order of columns in formula? When using 'CONTAINS(' it suggests 'Search for' comes before 'search within'?

Also, I have having problems selecting full column from 1 click. Normally I click the Grey Header on column. It allows me to select full column from another sheet but not on this sheet??

I have tried using a OR formula below but to no success. However even if I get this to work here, we have another dropdown with over 200x restricted values so maybe not possible.

Have I done this correctly in created the 'Allowed Column' text/number column?

I have attempted a formula on Flag Row 1 (but limited by formula bar to select other cells) and then tried in Row 7 (see below).

Peter Graham

I think i have solved it..... See below for formula and the 'Test' text in cell to make sure it works!

Peter Graham

I have found limitations to my madness!

When using the above method on the dropdown with over 200x restricted values as seems to be maximum character to a cell? The full list does not paste!

Any Ideas?

Paul Newcome

That's odd. When I copy from my dropdown column properties to my sheet, it puts each one in its own cell...

Andrée Starå

@Peter Graham

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

Peter Graham

Thank you all

Andrée Starå

@Peter Graham

You're more than welcome!

Have a fantastic weekend!

Paul Newcome

@Peter Graham My apologies. For whatever reason I missed your screenshots from yesterday. I hope you were able to get it working. If not, below is a screenshot that explains my solution.

Using this method, you only need to enter the data in the [Allowed Column] one time. You can then reference the entire [Allowed Column] in the contains formula on each row in the Flag column.

You could even hide the [Allowed Column] if you wanted to keep the sheet looking cleaner.

Haley S

Hi Paul - How does this work if the package plant labor materials column was a multiselect drop down, so that some used values may be found in allowed but others are not?

Paul Newcome

@Haley S If you want to just flag on rows that have at least one that is not allowed (regardless of how many are allowed), you would use something like:

IF(COUNTIFS([Multi-Select Column]@row, HAS([Allowed List]:[Allowed List], @cell)) = 0, 1)