Remove Used Items in Excel Drop Down

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 Lee and Pat.

hideuseditems01

See the Assignments by Player Name

There was an older version of this sample file, and I have updated it to use Excel Tables. I’ve also added a sheet that shows the assignments by player name, and you can see how often each player was assigned to a position.

hideuseditems02

Download the File

To see the file, and how it works, you can download it from my Contextures website. On the Excel Samples page, look for DV0061 – Assign Players Each Inning. The file is in xlsx format, and does not contain macros.

___________________

Show Input and Error Messages in Excel

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.

datavalidationdropdown01

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.

datavalidationallow01

After you select from the Allow drop down, one or more boxes will appear. For most of the data types, you’ll have to choose an operator, such as greater than, less than or equal to, or between.

This cell should only allow numbers between 1 and 10, so the Between operator is selected, then 1 is entered as the minimum number, and 10 as the maximum number.

datavalidationallow02

Test the Validation

After you set up the data validation rule, you can test it, by typing an invalid entry. When I type 11 in the cell, an error message appears. It’s not too helpful – it tells me that the entry is not valid, but doesn’t explain why.

datavalidationallow03

Add More Information

There are a couple of things that you can do, to help people put valid data into the cell:

  • show an input message when the cell is selected. Give a brief description of what can be entered in the cell.
  • create an error message, which appears if invalid data is entered. The error message can stop invalid data, or show a warning but allow the invalid data.

You can read detailed instructions on my Contextures website, for setting up these messages. Or watch the video below, to see the steps.

Watch on YouTube: Show Input and Error Messages with Excel Data Validation

____________________

Block Changes in Excel Drop Down List

halloweenmovieHappy Halloween! It’s the day for tricks and treats, so here is a useful trick that you can use with your drop down lists.

This is a treat that you probably haven’t tried before, and it could prevent your worksheet’s data from turning into a nightmare!

To show how this trick works, I used the movies from the Horror section in The Guardian’s Greatest Films spreadsheet. The same movies were used a couple of Halloweens ago, to show how Slicers work. About half of the horror movies were made in the USA, so to keep things simple, I grouped the countries as USA and Other.

Let the Right One In

To show how this trick works, I set up list of countries (USA and Other), then a list of the top horror films made in each of those countries.

datavalmismatch06

The Exor-List

The data in each list is named:

  • ListCountry
  • ListOther
  • ListUSA

On the Data Entry sheet, I added a data validation drop down in column B, based on the ListCountry range.

In column C, I created another drop down, and it uses the INDIRECT function to create a range reference:

=INDIRECT(“List” & B2)

  • USA is selected in cell B2, so the drop down in C2 shows the items in the range named ListUSA.
  • Other is selected in cell B3, so the drop down in C3 shows the items in the range named ListOther.

datavalmismatch05

Don’t Look Now

Things can go wrong though, if someone goes back to the Country cell, and changes it. Then, the country and movie selections are mismatched — for example, Country is changed to Other, and Texas Chainsaw Massacre is selected in the second column. Oh, the horror!

datavalmismatch07

Evil Dead

Fortunately, we can prevent this kind of evil activity, by changing the data validation formula in the Country column.

Currently, the formula just refers to the country list: =ListCountry

I’ll change the formula, so it checks to see if the movie cell is empty. If the cell is empty, it will show the ListCountry items.

=IF(C2="",ListCountry,

If the movie cell is not empty, it will try to show the items in the range named “FakeRange”. I can’t use that name on its own in the formula, because Excel will detect that the range doesn’t exist. So, I’ll use it with INDIRECT, and Excel will let me enter the formula. If C2 has an entry, Excel will warn me about the error, but I can click “Yes” in the message, to ignore it.

=IF(C2="",ListCountry,INDIRECT("FakeRange"))

There isn’t any range with that name in this workbook, so the result is an error, and the drop down list doesn’t work. The arrow appears, but nothing happens when you click on it.

datavalmismatch08

Ha! We are truly Les Diaboliques. But we are doing this to protect The Innocents, who might accidentally mess up our worksheets in the Dead of Night.

Video: Block Changes in Excel Drop Down List

Watch this short video to see how this technique works, and see how to set up the data validation in both columns.

Or watch on YouTube: Block Changes in Excel Drop Down List

Download the Sample File

To download the sample file, please visit the Dependent Data Validation page on my Contextures website. The file has several sheets, with a variety of conditional drop down examples.

_______________

Dependent Drop Down List From a Row

There is a sample file on my Contextures site, which creates dependent drop down lists from items that are sorted in columns. You can see the lists in the screen shot below. On the data entry sheet, you select a region from the first drop down, and only the customers in that region appear in the second drop down.

datavaldependsort02b

Data in Rows

Sometimes life isn’t perfect though, and you might not be able to get your data in nicely sorted columns. In my new sample file, the dependent lists get their items from a row.

In the screen shot below, there is employee information in a table, and each person’s row can have up to 5 skills listed. This is a formatted table, named tblEmpLU.

dependentdropdownrow02

In the data entry sheet, there are drop down lists in columns B and C. First, select an employee name in column B, then see a list of their skills in column C. This is another formatted table, named tblAssign.

dependentdropdownrow01

Name the Lists

To create a drop down list of employees, I named cells B2:B5 in the employee lookup table, as EmpList. Then, I used that list as the source for a data validation drop down list.

dependentdropdownrow04

To create a lookup range, I selected all the data cells in the tblEmpLU table (B2:G5), and named that range EmpLookup. Because the range is based on a formatted table, it will automatically expand, if more rows are added to the table.

dependentdropdownrow02

Create the Dependent Drop Down List

The second data validation list has an OFFSET formula that finds all the skills for the selected employee. You can see its arguments in the screen shot below.

dependentdropdownrow07

Here’s how we’ll use those arguments:

  • Reference: This is the starting point for finding the selected employee’s skills list. We’ll use the EmpLookup range as the reference
  • Rows: Number of rows down from row 1 in the Reference, to get the employee’s data. We’ll use MATCH to get the row, and subtract 1. For example, if Lou’s info is in row 4 of the range, we need to go down 3 rows from row 1.
  • Cols: 1 – Number of columns to go across, to find the starting point for the skills data
  • Height: 1 – we want 1 row of data in the result
  • Width: Number of skills entered for the selected employee (we’ll use COUNTA to get this number)

Here is the formula in the dependent data validation drop down, entered in cell C2. It’s colour coded to show how each of the arguments is calculated.

=OFFSET(EmpLookup,MATCH(B2,EmpList,0)-1,1,1,
  COUNTA(OFFSET(EmpLookup,MATCH(B2,EmpList,0)-1,1,1,5)))

dependentdropdownrow05

Test the Dependent Drop Downs

After you select a name from the first drop down, click the drop down arrow in the second drop down cell. The list shows only the skills for the employee that you selected.

dependentdropdownrow01

Download the Sample File

To see the formulas, you can download the sample file from my Contextures website. Go to the Sample Files page, and in the Data Validation section, look for DV0060 – Dependent List From Row Items. The zipped file is in xlsx format, and there are no macros.

________________

Conditional Drop Down Lists in Excel

It’s fairly easy to make one drop down list show items based on what was selected in another drop down. But what about making a third drop down list? And how can you work with spaces, or other characters that can’t be used in Excel names?

Here is a nice, simple conditional list – if you select Red Fruit in cell A2, you can see a list of red fruit in cell B2.

dependentdropdownspace01

Using Named Ranges

There is a named range on the worksheet – RedFruit – and the data validation formula uses the INDIRECT function to create a reference to that range. The formula also removes any spaces in the text, by using the SUBSTITUTE function, because the range names can’t use space characters.

=INDIRECT(SUBSTITUTE(A2," ",""))

dependentdropdownspace02

Lookup Tables for Complex Lists

It’s simple to use the SUBSTITUTE function to get rid of any spaces, but your list of items might contain other characters that can’t be used in range names, such as an ampersand (&) or asterisk (*).

Instead of trying to substitute every illegal character, you can set up a lookup table. Put the list items in the first column, and a short code for each item in the second column. The range with items and codes is named ProductLookup.

The data validation formula will look in that list, to find the code, and show the items from a list with that name. In this example, all the list names end with “List”, so that is added in the formula.

=INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"List")

dependentdropdownillegal06

Add a Third Dependent Drop Down

You could even add a third drop down list that is dependent on the selections in the first two.

dependentdropdownillegal01

Just create a lookup table for each of the secondary lists, and an item list for each of those secondary codes.

dependentdropdownillegal02

Here is the data validation formula in cell C2

=INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)&"List")

It finds the product lookup table,

  • VLOOKUP(A2,ProductLookup,2,0)&"Lookup"

the product code in that table,

  • VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)

and adds “List” to create a reference to the list name.

=INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)&"List")

Download the Sample File

You can see the details on my Contextures website, Dependent Lists page, and download the sample file.

_________

Block Duplicate Entries in Excel Table

Data validation is a great feature in Excel, and I often use it to create a drop down list in a cell. That helps prevent data entry errors, and limits what people can input.
You can use data validation rules in other ways too. For example, you can prevent duplicate entries in an range or cells, or in a table column.
In this example, employee data is being entered in a formatted table, and each employee must have a unique ID number. The COUNTIF function can check the cells for identical entries, and warn you, or stop you, if an ID number is already in use.
preventduplicatestable02

Use COUNTIF to Check a Range of Cells

To create this data validation rule, I named the data cells in the EmpID column as EmpIDs. This step is necessary, because data validation can’t use the table column name directly.
The good news is that named range is based on a formatted Excel table, so it is dynamic — it will automatically grow or shrink if the number of rows changes. In older versions of Excel, you can use a formula to create a dynamic range.
Then, I used that range name, EmpIDs, in a COUNTIF formula.
=COUNTIF(EmpIDs,A2)<=1
To prevent duplicates, the count must be 1 or zero, and if not, a warning will appear when you enter a duplicate number.
preventduplicatestable01

Add an Error Message

If you enter a duplicate number, the data validation rule will show a default message, telling you to try again, or cancel your entry. You can personalize that message, to help people figure out what they’ve done wrong.
In the screen shot below, the message explains that the entered number is already in use, so you can cancel that, and try a different number.
preventduplicatestable02
You can read the detailed instructions on my Contextures website – Prevent Duplicates in an Excel Table – and you can see the steps in the video below.

Video: Prevent Duplicate Entries

This video shows you the steps for creating a formatted table, naming one of the columns, and setting up the data validation.

______________

Missing Drop Down Arrows in Excel 2013

You can create drop down lists on a worksheet with Excel’s data validation feature, and they make data entry much easier – usually! In the screen shot below, there is a drop down in cell B3, and you can select Yes or No from the list.
datavalidationarrow03
However, data validation does have its quirks. In fact, it has so many quirks that I’ve got a whole page on my website explaining what they are, and how to work around them.
And recently, I found another oddity to add to that list – missing arrows in Excel 2013.
“Missing arrows” is a fairly common problem, and usually they can be fixed by using the keyboard shortcut to show objects — Ctrl + 6
Unfortunately, that trick didn’t work this time. There are a few more fixes for missing data validation arrows on my website, and none of those worked either.

Paste a Linked Picture

Here’s how I discovered the new problem, and added a new workaround to that missing arrows page.
Last week, I was updating a file in which you can select a company name from a drop down list. Based on the company name that was selected, a company logo appears on an invoice sheet in the workbook.
The logo is in a linked picture:

  1. Copy a cell that contains a logo, or another shape
  2. Select another cell in the workbook
  3. On the Ribbon’s Home tab, click Paste, then click Linked Picture

datavalidationarrow04

Linked Pictures and Arrows Don’t Mix

As soon as I pasted that linked picture, the drop down arrow disappeared. I thought the workbook might be corrupted, so I tried the same thing in a different workbook. The drop down arrow disappeared there too.
The arrow appeared briefly if I pressed the mouse button when selecting the cell, but vanished as soon as I let go. The same file worked without problems in Excel 2010.
datavalidationarrow01

Vanishing Arrow Workaround

I’m using Excel 2013, on Window 8, so if you’re using a different operating system, it might not be a problem. However, if you run into this issue, here’s a kludgy workaround. If you find a better solution, please let me know.

  1. Select the cell with the data validation list
  2. Click outside of the Excel window (e.g. click on the Desktop, or click in your browser window)
  3. Click on the Excel window, and the arrow will appear, and you can select an item from the list.

datavalidationarrow02
_____________
 

Excel Crashing After Combo Box Click

Last week, I was updating my web page that shows how to show a combo box in a cell that has a drop down list.
datavalcombosheet14
The combo box has several advantages over the default data validation list. You can:

  • change the font size,
  • increase the number of visible rows
  • start typing an entry, and it autocompletes, if a match is found

I’ve used this technique in several versions of Excel, and never had problems – until now.

Click and Crash

Because you can set the font size in a combo box, it should work better if the worksheet is zoomed below 100%. At lower zoom settings, it can be hard to read those tiny letters in the data validation list, so a larger font helps.
To show the difference in font sizes, I zoomed the worksheet to 70%, then clicked on the arrow in the combo box. Excel 2013 crashed. I tried it a few more times, and it crashed again and again.
datavalcombosheet16
I tried the same thing in Excel 2010, where that old familiar message appeared – “Not enough system resources to display correctly”. And then Excel 2010 crashed.

The Problem Was Fixed in 2000?

After a lengthy Google search, I found a couple of forum posts that linked to an MSKB article about this problem. Apparently this article listed the conditions that led to the error, and had a workaround.
Unfortunately, that article is retired, because the problem was solved in Excel 2000. Well, it seems to be back, on my machine, at least. I’m on a Windows 8 computer, using Excel 2013, so that’s well past the version in which the problem was “solved”.

The Conditions That Cause the Problem

Fortunately, you can find all kinds of things in the WayBack Machine, and that’s where I found an archive of the missing article. You can see it at the end of this article.
What conditions can create this error message, or in my case, an Excel crash?

  • The Input range of the control is linked to a second worksheet.
  • You zoom both worksheets to percentages other than 100 percent.
  • The zoom percentage of the two sheets are not equal.

And that’s how my workbook was set up:

  • My combo box gets its list from a named range on another worksheet
  • Both sheets were at 80% zoom
  • I changed the combo box sheet to 70%, to show the difference in font size

The Workaround

Here are three workarounds for the problem:

  • Change the zoom setting of either worksheet to 100 percent.
  • Change the zoom setting of both sheets to the same percentage.
  • Select an input range that is on the sheet with the list box, drop-down list box, or combo box.

I chose option 1, and set the zoom level for the sheet with the named ranges to 100%. I will add a big message on that sheet, warning people to leave the zoom alone.
With that zoom setting, there were no more crashes. But, when I changed it to 90%, and tested the combo box, it crashed again. Don’t do that!

How the Combo Box Should Work

In this video, you can see how the combo box works, when it isn’t crashing. You can download the sample file, and get the setup instructions, on my Contextures website: Data Validation Combo Box using Named Ranges

The Missing MSKB Article

Here is the retired MSKB article that I found in the Internet Archive:
________________

“Not Enough System Resources” with Controls on Zoomed Sheets

Article ID: 183503
Retired KB Content Disclaimer
This article was previously published under Q183503
SYMPTOMS
If you click the following items in a worksheet multiple times or if you move a vertical toolbar over a worksheet with any of the following items

  • list box
  • drop-down list box (Microsoft Excel versions 5.0 or 7.0 only)
  • combo box (Microsoft Excel 97 only)

the following message may appear:
Not enough system resources to display completely
CAUSE
This problem may occur when the following conditions are true.

  • The Input range of the control is linked to a second worksheet.
    -and-
  • You zoom both worksheets to percentages other than 100 percent.
    -and-
  • The zoom percentage of the two sheets are not equal.
    -and-
  • You move a toolbar over the control, click the control and choose values, or you alternately select each of the two worksheets.

NOTE: Sometimes an action does not result in the message, but repeated actions do. The message only occurs when the worksheet that contains the control is active.
WORKAROUND
To work around this problem, do any of the following:

  • Change the zoom setting of either worksheet to 100 percent. Click Zoom on the View menu, click 100% and click OK.
    -or-
  • Change the zoom setting of both sheets to the same percentage. Click Zoom on the View menu to make this change.
    -or-
  • Select an input range that is on the sheet with the list box, drop-down list box, or combo box.

Changing the Input Range of a Control
To change the input range of a control, follow these steps:

  1. Hold down the CONTROL key and click the form control to select the control.
  2. On the Format menu, click Control.
  3. Click the Control tab and type a range on the active worksheet.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000.

Combo Box Crash Survey


________________________

Dependent Drop Down Lists in Excel

In Excel, you can set up drop down lists that are dependent on the selection made in another cell. In this example, you select a region in column B, and only the customers in that region are in the drop down list in column C.

datavaldependsort

Use OFFSET not INDIRECT

One way to accomplish this is with named ranges and the INDIRECT function, as explained here: Data Validation — Create Dependent Lists. That method works well if there are only a few options in the first column.

For a longer list of items, it might be difficult, or impossible, to set up all the named ranges that you need, and to maintain all those lists.

To make things easier, this tutorial uses the OFFSET function, to extract related items from a sorted list.

datavaldependsort07

The only restriction is that the main column has to be sorted, so that all the items are grouped together.

datavaldependsort02

Updated for Excel Tables

The original version of this tutorial used lists on the worksheet, and I have updated it to use named Excel tables. Now, when you name the ranges, they are connected to the tables, so they adjust automatically if the table size changes.

datavaldependsort03

Cross Validation

The Region column uses a simple list as the source for its drop down, as long as no customer has been selected. However, if a customer name is in column C, the Region drop down only shows the region for that customer.

If you want to start over, clear out both cells in the row, and select a region, then a customer.

datavaldependsort04

Download the Sample File

To see the detailed instructions, and to download the sample file, please visit my Contextures website: Dependent Drop Downs from a Sorted List. The sample file is in xlsx format, and does not contain macros.

You can find the Excel 2003 version here: Dependent Drop Downs 2003 Sorted List

__________________

Create a Drop Down List With Symbols

To make data entry easier, you can create a drop down list in an Excel cell, using data validation.
symbollist03
This works well with a list of words, but the list doesn’t show symbols if you create the list in a font such as Webdings. In the example below, I entered 5, 0, and 6 in cells B2:B4 and formatted the cells with Webdings.
The drop down list shows the number values, instead of the formatted symbols.
symbollist04

Use Built-In Symbols

You can’t create a drop down list of symbols based on formatted cells, but you can use some well-hidden symbols instead.
In the screen shot below, cells B2:B4 are formatted in Tahoma font, which is the same font used in the drop down list.
symbollist05

  • To create the Up Arrow in B2, I pressed the Alt key, and typed 30  on the number keypad.
    • NOTE: You have to use the number keypad – not the numbers at the top of the keyboard.
  • Then, use the same technique in cells B3 and B4, with numbers 29 and 31.

Watch the Video

This video shows you the steps for creating a drop down list, to show a selection of symbols.

Download the Sample File

To download the sample file, please visit my Contextures website: Data Validation Tips and Quirks.
______________