Got Sheet is brought to you by beehiiv. If you’re considering starting a newsletter, check them out for 30 days free and 20% off your first 3 months…try beehiiv now.

HEADLINES

• Some new things brewing in the Excel world this past week…the new TRIMRANGE function is hitting a spreadsheet near you. This and the new Trim References syntax lets you to quickly trim leading or trailing blanks in a data range

• In the last issue we walked through three ways to quickly jump to the bottom of your dataset. Check it out here.

MULTIPLE SELECTIONS

What’s the big deal, anyway?

Until now, if you have been using a data validation drop down list in Google Sheets, you were forced to selecting only one value from that list at a time.

There was no way to have multiple values in a cell at once (without writing custom code).

Seems like a small thing, and a lot of people asked and searched for how to do it….

Below, I’ll go through:

1. Old Apps Script solution

2. Native functionality

3. Questions & workarounds

tldr;
The multiple values are stored in a cell as a comma-separated list just like in my original apps script solution.

Here’s the video walkthrough:

Apps Script

Naturally, there’s always been a way to do this in Apps Script. In fact, this is one of the first tutorials I made a couple years ago.

We used to have to force a comma-separated list of values into a data-validation dropdown list using a script combined with some HTML for a sidebar menu.

We were constrained to a real complicated solution to do a pretty simple thing.

No more 👇

Native Access

Now, it’s a simple toggle switch right there inside of our data validation sidebar menu.

Go to Data - Data validation from the menu. When the sidebar pops up, scroll down to find the checkbox for allowing multiple selections.

Questions and Workarounds

• When making a data validation list in a Google Sheets Table, you access the data validation menu differently. You have to click the edit selection from a cell in that table’s column where you’ve got the data validation.

  

• If your original data changes from where you’re pulling the data validation values, it doesn’t remove that value from the multiple selections and throws an error. To fix this, you have to delete that cell’s info altogether or remove the value before you delete it as an option in the original data.

  

• In order to count or filter data based on an individual value in the multiple selection, you have to use a little bit of function manipulation because of how the values are stored as a comma-separated array. Check out the video walkthrough for details on this.

  

Wrap up

How helpful is this for you? Have you ever needed to have multiple selections in a dropdown list?

I have several projects coming up from reader submissions, and I’m looking forward to sharing them with you.

Reach out if you’ve got a project you need a hand with. It may make it into a future issue!

Happy Spreadsheeting!

Enjoyed this issue? Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.