Show Drop Down List With Specific Letters

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!)

Continue reading “Show Drop Down List With Specific Letters”

Clear Dependent Drop Down Cells

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.

dependentclear02

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

dependentclear07

Continue reading “Clear Dependent Drop Down Cells”

Add New Items to Excel 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”

Adding Form Controls on a Worksheet

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 podcasts.

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.

datavaldependtables08

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.

DV102b

The INDEX function created the applicable drop down list, based on your previous selections on the data entry sheet. INDEX was an alternative to using volatile functions like INDIRECT or OFFSET, when creating Dynamic Ranges. 

Using Excel Tables

When Roger created the Excel 2003 version, the List object had just been introduced, and it wasn’t as full-featured as the Excel Tables feature.

Now, if you’re using Excel 2007 or later, you can create dynamic ranges that are based on tables. So, to take advantage of this improvement, Roger has created 2 updated versions of his technique, using tables as the source for the dynamic ranges.

Version 1: One Table With All Lists

The first version looks similar to the Excel 2003 version, and it has these components:

  • 1 data entry table
  • 1 lookup table, with all the lists
  • 3 named ranges.

In the screen shot below the lookup list has been created, and now it’s being changed to an Excel table, named tblVal. The Regions are listed in the first column, and the remaining columns have the lists for each region and country.

datavaldependtables04

Roger has created two sample files for this version:

  • one is macro free – if a value is changed, any selections to the right are not affected
  • one has macros – if a value is changed, selections to the right are cleared (in a specific number of columns)

You can read the details and get the sample files for this version here.

Version 2: Multiple Tables and INDIRECT

The second version uses several single-column tables, instead of one giant lookup table. The tables can be in adjacent column, or you can leave gaps.

datavaldependtablesindirect02

Roger names each single-column table with the same name that is used in the column heading. So, the table in column J is named Europe.

Why? Because Roger discovered that this allows you to refer to the name directly, and return the range of cells belonging to that name. For example, this formula:

 =Europe

will return exactly the same range as this formula

=INDIRECT(Europe[Europe])

Roger has created two sample files for this version:

  • one is macro free – if a value is changed, any selections to the right are not affected
  • one has macros – if a value is changed, selections to the right are cleared (in a specific number of columns)

You can read the details and get the sample files for this version here.

More Sample Files and Tutorials

You can find all of Roger’s tutorials and sample files listed here: Roger Govier’s Files and Templates

________________

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.

datavaldynamicblanks01

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

datavaldynamicblanks02

We’ll fix the problem, to create a list with all the items, and no blank cells.

Remove Blanks for Drop Down List in Excel  blog.contextures.com

Number the Non-Blank Cells

To create a drop down list without blanks, you can add formulas to the worksheet.

First, add a formula in cell A2, and copy it down to cell A13. This will number the cells that are not blank.

=IF(B2="","",MAX(A$1:A1)+1)

datavaldynamicblanks03

Create a List Without Blanks

Next, create a list that pulls the numbered items into a new column.

Enter this formula into cell D2, and copy down to D13. This INDEX/MATCH formula creates a list with all the blanks at the end.

=IFERROR(INDEX($B$2:$B$13,MATCH(ROW()-ROW($D$1),$A$2:$A$13,0)),"")

datavaldynamicblanks04

Create a Dynamic Range Without Blanks

After you use formulas to create a second list, without the blanks, you can name that range, and base the dynamic range on that.

This range is named ListMonths, and uses the MAX from column A as the row count.

=OFFSET(Sheet1!$D$1,1,0,MAX(Sheet1!$A:$A),1)

datavaldynamicblanks05

If you create a drop down list based on the ListMonths range, it does not include blanks, and December is the last month.

datavaldynamicblanks06

Download the Sample File

To see the formulas, you can download the file from my Contextures website. On the Sample Excel Files page, go to the Data Validation, and look for DV0063 – Dynamic List With Blank Cells. The zipped file is in xlsx format, and does not contain macros.

________________________

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.
multiselectlistbox01
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 the worksheet, and it appears when you click on a cell that has a data validation list.
multiselectlistbox02

Show a Listbox

In my new example, when you click on a cell that has a data validation list, a listbox appears. It shows all the items from the source list, with check boxes, so you can select multiple items.
The listbox is embedded in a UserForm, which also has OK and Cancel buttons.
multiselectlistbox03
When you click OK, all the selected items are added to the cell, separated by a comma and space.
multiselectlistbox04

Listbox Instead of Combo box

I used the listbox, instead of a combo box, because it’s much easier to select several items, by adding check marks. You can see several options at a glance, and scroll down, if the list is very long.
In the next screen shot, there are 1100 entries in the ItemList, and you can quickly scroll through them all in the Listbox.
multiselectlistbox05

Download the Sample File

To see the code, and experiment with the Listbox, you can download the sample file from my Contextures website.
The zipped file is in xlsm format, so enable macros when you open the file.
__________________________

Multiple Selection Drop Down With Codes

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.

datavalmultiselectcode03

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 product code in cell D3.

Here is the lookup table in the sample file:

datavalmultiselectcode01

The data validation drop downs are in column C, where you can select one or more numbers in any cell.

datavalmultiselectcode02

I created a variation of the original code, so it finds the matching ID for the selected number, and add that ID to the string in the adjacent cell in column D.

datavalmultiselectcode04

How the Code Works

In the sample file, this example is on the sheet named SameCellWithCode. To see the code, right-click on the sheet tab, and click View Code.

The lookup table is on the same sheet as the drop down lists, but you could do the same thing with a list on a different sheet. The lists of numbers and IDs are in named ranges, and referenced in the code.

    Set wsList = ActiveSheet
    Set rngList = wsList.Range("NumWordList")
    Set rngListID = wsList.Range("NumWordID")

Then, when you select a number, the code finds the matching ID, and puts it at the end of the string in the adjacent cell

         lCode = rngListID.Range("A1") _
            .Offset(Application. _
                WorksheetFunction _
                .Match(Target.Value, _
                rngList, 0) - 1, 0)
         Target.Offset(0, 1).Value = lCode

Download the Sample File

You can download the sample file from the Select Multiple Items page on my Contextures website. The zipped file is in xls format, and you’ll have to enable macros if you want to test the code.

If you copy the code into a different workbook, remember to check the code for sheet names, range names, row numbers and column numbers, and adjust those if necessary.

Video: Select Multiple Items in Drop Down List

You can see how it works in this video:

Or watch on YouTube: Select Multiple Items from Excel Data Validation List

_______________

Add New Items to Data Validation Combo Box

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.
datavalcombosheet14

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 type a new item, a message box asks if you want to add it. Then, if you click Yes, it’s put into the list, in alphabetical order.
datavaladdsorttable01

Add New Items to Combo Box

Someone recently asked if it was possible to combine those techniques, so I’ve created a new sample file that does just that! When you click on a cell that has a data validation list, a combo box appears. If you type an item that isn’t in the list, you’re prompted to add it.
NOTE: The Add New Item code is triggered when you press Tab or Enter to get out of the Combo box.
To see the file, and how it works, you can download it from my Contextures website. On the Excel Samples page, look for DV0062 – Data Validation Click Combobox – Add New Items. The file is in xls format, and contains macros.
___________________