Select Multiple Items From Long List in Excel

Select multiple items with combo box and listbox http://blog.contextures.com/

It's hard enough to select one item from a long drop down list – how can we make it easier to select multiple items for a cell?

In the screen shot below, the worksheet has code that lets you select multiple item from the drop down list. Each new item is added to the cell, instead of replacing the previous selection.

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Select Multiple Items From Long List in Excel

Show Drop Down List With Specific Letters

drop down list with specific text http://blog.contextures.com/

If you've got a long list of items, it can take a while to find what you're looking for, in a data validation drop down list.

For example, in the screen shot below, you'd have to scroll down to the bottom of this long list, if you want to select the Teatime Chocolate Biscuits (and don't pick the Tofu by mistake!)

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Show Drop Down List With Specific Letters

Worksheet Combo Box Problem in Excel 2013

worksheet combo box listfillrange http://blog.contextures.com/

On of my favourite Excel tricks is to show a combo box, when you click a cell that has a drop down list. The combo box can have larger font, and it autocompletes, so it’s easier for people to use.

There is only one ActiveX combo box on the worksheet, and it is hidden until you click on a drop down cell. When it appears, it shows the data validation list for the selected cell.

Continue reading Worksheet Combo Box Problem in Excel 2013

Clear Dependent Drop Down Cells

clear dependent cells blog.contextures.com

You can use data validation to create a drop down list on an Excel worksheet. For example, show a list of countries, if you click a cell in column B.

After you’ve set up that first list, you can make another list, in column C, to show the cities for the selected country.

We’ll see how to set this up, and prevent problems, by clearing out the city cell, when necessary

Continue reading Clear Dependent Drop Down Cells

Add New Items to Excel Drop Down List

add new item to drop down list

There are a few sample files on my web site that let you automatically add new items to a drop down list. The most recent version works in newer versions of Excel, and uses named tables to store the lists.

When the code detects a new item, it asks if you want to add it to the drop down. If you click Yes, the item is added, and the lookup list is sorted in alphabetical order.

Continue reading Add New Items to Excel Drop Down List

Show Data Validation Items in List Box

list box for data validation

In February, I shared a sample file that shows a popup list box, when you click on a cell that has a data validation list. The list box shows all the items from the source list, with check boxes, so you can select multiple items.

Continue reading Show Data Validation Items in List Box

Adding Form Controls on a Worksheet

Excel drop down lists

Do you spend your days, and maybe your nights, talking about Excel with your friends? It’s amazing how quickly the time passes, while you chat about all the awesome features and tricks that you know.

This week, I was happy to spend some time on Skype, talking about Excel with my friend Chandoo. While he sweltered in India’s heat, I shivered in our Canadian snow, so it averaged out the perfect temperature for a friendly, and informative, discussion. And you'll be able to hear what we said -- Chandoo has posted a recording in the next episode of his Excel

Continue reading Adding Form Controls on a Worksheet

Dependent Drop Down Lists With Tables

With dependent drop down lists, you can control what appears in a drop down, based on what was entered in the previous cell. In this example, you select a region, then a country in that region, then an area, and finally a city.

Long ago, while we were all still using Excel 2003, Roger Govier shared his technique for creating dependent drop down lists using Index.

There is one big lookup table, with Regions listed in the first column, and the remaining columns have the lists for each region and country.

The INDEX function created the applicable drop

Continue reading Dependent Drop Down Lists With Tables

Dynamic List With Blank Cells

If a list contains blank cells, the usual method for creating a dynamic named range doesn’t work. For example, you can use an OFFSET formula, which counts the entries in the column. The count is used to set the number of rows in the range.

When there are blanks, as in the screen shot below, the range is incorrect. There are 9 items, so 9 rows are included, but the last item is in the 12th row of the list.

If you create a drop down list based on this range, it includes blanks, and August is the last

Continue reading Dynamic List With Blank Cells

Select Cell Items From ListBox

I’ve posted a few techniques for selecting multiple items from the drop down list in a cell. In the screen shot below, there is a drop down list in cell C3. When you select an item from the list, it’s added to the cell, instead of overwriting the previously selected value.

Now I’ve created a new variation on this, combining it with the combobox popup technique from one of my other data validation examples. In the screen shot below, there is a combo box over cell B3, with a list of Weekdays. There is one hidden combo box on

Continue reading Select Cell Items From ListBox