Multiple Selection Data Validation in Google Sheets

preview_player
Показать описание
How to add a script and html file to your Google Sheet to allow multiple values in a data validation drop down list to be selected.

⭐❗👉MOST COMMON MISTAKE: When you make a copy of the sheet, go to Data Validation Advanced Options and under "If data is invalid:" make sure "Show a warning" is checked instead of "Reject Input". Otherwise, nothing will happen when you try to fill the current selection from the script.

We receive commission on purchases made through some links on this page
_______________________________________________

🎓Software Recommendations:

_______________________________________________
🎓Data Course Suggestions: (coursera)

_______________________________________________
🎓AI Course Suggestions: (coursera)

_______________________________________________

🖥️ Office items I can't live without:

📲 Find me
_______________________________________________
Рекомендации по теме
Комментарии
Автор


❗MOST COMMON MISTAKE: When you make a copy of the sheet, go to Data Validation Advanced Options and under "If data is invalid:" make sure "Show a warning" is checked instead of "Reject Input". Otherwise, nothing will happen when you try to fill the current selection from the script.

EamonnCottrell
Автор

I've been looking for something like this for sooo long! I found Ivanov's video yesterday and have been trying to find something more current. Nothing I've tried up to now gave me the results I needed. Thank you soooo

IamDesertFlower
Автор

this is the closest feature that we need for better management. thanks, we really appreciate it 👍

elarjei
Автор

Thank you for your help. I appreciate it. I did get it to work. Now I can select multiple values where it is appropriate.

rogerdrolet
Автор

Works awesome, thanks!
I need others to also be able to add this data unfortunately so went back to creating a list with all combinations of the multiple selection items. But great video!

truecauseofchaos
Автор

Thank you Eamonn, really helpful, thank you for taking the time to put this together.

KirtenNana
Автор

Damn, this still works and is exactly what I was looking for! Thank you so much!

Solereaper
Автор

This is very useful and help me and my team a lot with organizing item list. Thank you so so so much!!

pornpimolpienchana
Автор

thank you sir. it's really works and help me alot.

kejaksaannegerilomboktenga
Автор

Seen a few other examples that I couldn't get to work but this one worked for me and is the most flexible. I had to deal with Google Authorization issues though. With 2FA on the account, it looks like you can't use the default built-in Google Cloud project but you have to change the project to a "standard" project. Fortunately, I'd messed with it before and had a project I could attach it to. The process is not for the faint of heart. Once I got it, however, it worked.

DavidLeighAlsace
Автор

🤔
I'm fairly certain you could do this natively without a script. For one, your solution is a bit of a bad data practice - you're jamming multiple values into a single cell. If you wanted to filter characters based on who likes purple, 3:30, you will have to do some data cleaning in order to split Raph's color choices.
A better method would be to create a separate table with the color choices. This is how it should be done, especially if the data is going to be used for analysis in something like Power BI. The table would contain all color choices, then combined only on this table.

I get it that it's just for a kid to easily select colors, but I see a lot of comments with people wanting to use this for more than just playing with spreadsheets. 😕

chrishoffman
Автор

Thank you so so much for this. We were searching for a solution on this for a while. Finally able to get it done! I have a question though: Currently we can select multiple options and they are visible with commas, is there a possibility that after being selected the data is seen in different rows within the cell? If yes, how can we do it?
Would be a great help if you can help with this as well. Thanks!

vikakiEHS
Автор

This is great! However I was wondering, is it now possible to filter or sort the results to quickly show all where a given item from the list was selected? Of course you can sort by column as usual, but if you're looking for all rows with "Red", say, and sometimes it's "White, red, green" and sometimes it's "Red, yellow" or "Brown, red", when just sorting alphabetically those will be all over the place in the column.

Phagocytosis
Автор

Thank you so much! I have been trying to implement something like this for so long and no solution comes close to this in terms of simplicity.

paurushdube
Автор

Hi Eamonn, thank you for this! Super helpful. I got the script to work for me, but I'm wondering if you know if there's a way for the side-bar to automatically popup when clicking into a cell that has data validation (instead of having to click on the multiple select data validation -> show dialogue)??

Coco-oc
Автор

Hi, Eamonn! Noce tutoria! I really need your help. After applying the script, the question arose of calculating the values. According to the classics, I tried to use COUNTIF, but it basically does not see the value separated by commas. When I output a list with a multiparameter, the formula counts it as a single cell. Do you know the solution?

kockfromthemountainq
Автор

Exactly what I was looking for! Thank you so much!!!😁

katerinadrakop
Автор

WWOOOO FREAKING Thank you SO much, you saved my bacon and HOURS of trying to figure something out. AWESOME!!!

FaradaysFlipper
Автор

Hello, thanks for the tutorial!
I want to ask something, Could you please explain how to count the data from the multiple selection?

Magicbubble
Автор

Hello! I've looked through the comments and have checked on common issues such as:

1. Show a Warning instead of Reject Input
2. Re-logging in my google drive
3. Make a copy of the original sheet that I created.

I could use the 'Reset Checkbox Selections' but not the fill current. Wonder if you can help me with this.

Thank you!

LeonardDCA
join shbcf.ru