Restricted Dropdown - Find cells that don't comply
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?
Best Answer
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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?
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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).
-
I think i have solved it..... See below for formula and the 'Test' text in cell to make sure it works!
-
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?
-
That's odd. When I copy from my dropdown column properties to my sheet, it puts each one in its own cell...
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Happy to help!
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you all
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
-
@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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives