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
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
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
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
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
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
There is a sample file on my website that has VBA code for selecting multiple items from a data validation drop down list. You can insert all the selected items into the active cell, or down the adjacent column, or across a row.
There is a video at the end of this article, that shows how the technique works.
Add a Matching Code in Adjacent Cell
Last week, someone asked it it was possible to put a code for each selection, in the cell to the right. For example, if you select a product in cell C3, put the
Continue reading Multiple Selection Drop Down With Codes
There are a few versions of the data validation combo box technique on my website. The files use programming to make a combo box appear when you click, or double-click, on a cell that has a drop down list.
The advantage to using a combo box is that you can show the text in a larger font, and it autocompletes as you type, unlike a cell with a drop down list.
Add New Item to Drop Down List
Another one of my sample files let you add new items to a drop down list, on the fly. If you
Continue reading Add New Items to Data Validation Combo Box
There is a new sample file on my Contextures web site, which lets you pick players for each inning in a baseball game. You could tweak the file a bit, and use it to assign employees to workstations each day of the week, or anything similar.
Once you have assigned a player in an inning, that name disappears from the drop down list in that column. In the screen shot below, Mike has been assigned as the pitcher for inning one. When I open the list in the cell below, Mike’s name is missing – it used to be between
Continue reading Remove Used Items in Excel Drop Down
With data validation, you can control what is entered in a cell, and prevent invalid data. I usually use drop downs, to create a list of options for people to choose from.
Sometimes a list isn’t the best option, and I have to use one of the other data validation settings.
Enter a Number
There are several other things that you can allow in a cell, instead of a list, such as whole numbers or dates. In this example, the cell will only allow whole numbers.
After you select from the Allow drop down, one or more boxes
Continue reading Show Input and Error Messages in Excel