Search Contextures Sites

 

Contextures
Excel news
by email

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

 

Learn how to create Excel dashboards.

 

 

Learn how to create Excel dashboards.

 

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

Learn how to create Excel dashboards.

 

 

 

Hide Used Items in Drop Down List

Thanks to Excel MVP, Peo Sjoblom, who contributed the original formula for this technique, and to Daniel.M, who suggested the enhanced formulas.

Set up the Main Table
Create the List of Items
Calculate if a name has been used
Create the list of unused names
Name the Dynamic List of Unused Names
Apply the Excel Data Validation
Test the Excel Data Validation
Download the Sample File
Hidden Items with Dependent Data Validation
Hidden Items in Multiple Columns

You can limit the choices in a drop down list, hiding items that have been previously selected. For example, if you are assigning employees to a shift, you don't want to assign the same employee twice.

In the drop down list shown below, Fred and Joe have been assigned, so their names aren't in the list now. The instructions for this workbook are shown below.

Data Validation Hide Used

For another example, download the Assign Baseball Players per Inning file, from the Excel Sample Files page.

assign players per inning

Set up the Main Table

To set up the Employee Shift workbook, start by setting up the table in which you want to use the Excel Data Validation. In this example, the worksheet is named 'Schedule' and the range A1:C7 is being used.

Column B will have Data Validation applied.

Main table

Create the List of Items

Create a list which contains the items you want to see in the Excel data validation dropdown list. Here, the employee names have been entered in cells A1:A6, on a sheet named 'Employees'

List of names

Calculate if a name has been used

1. On the Employees sheet, in cell B1, enter the following formula:

=IF(COUNTIF(Schedule!$B$2:$B$7,A1)>=1,"",ROW())

2. Copy the formula down to cell B6.

Validation list formulas

This formula counts the occurrences of "Bert" in cells B2:B7 on the Schedule worksheet. If the count is greater than or equal to 1, the cell will appear blank. Otherwise, the row number will be displayed.

Create the list of unused names

The next step is to create a formula which will move any blank cells to the end of the list. For long lists, you can use the multi-cell array formula, and for smaller ranges (fewer than 200 cells), use the single-cell formula, which is easier to edit.

Option 1: Multi-Cell Formula

  1. Select cells C1:C6
  2. Enter the following array formula (the formula is long, and should be all on one line)

    =IF(ROW(A1:A6)-ROW(A1)+1>COUNT(B1:B6),"",
        INDEX(A:A,SMALL(B1:B6,ROW(INDIRECT("1:"&ROWS(A1:A6))))))

3. Press Ctrl+Shift+Enter to enter the array formula in cells C1:C6

Unused names list

Option 2: Single-Cell Formula

If you'd prefer a single-cell formula (easier to edit), you could use this formula, also by Daniel.M. He recommends it for small ranges (<=200 cells):

  1. Select cell C1
  2. Enter the following formula (the formula is long, and should be all on one line)

    =IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$6),"",
    INDEX(A:A,SMALL(B$1:B$6,1+ROW(A1)-ROW(A$1))))

  3. Press Enter
  4. Copy the formula down to row 6

Name the Dynamic List of Unused Names

1. Choose Insert>Name>Define
2. In the Names in workbook box, type a one-word name for the range, e.g. NameCheck.
3. In the Refers to box, type the following formula (all on one line):

=OFFSET(Employees!$C$1,0,0,COUNTA(Employees!$C$1:$C$6)-COUNTBLANK(Employees!$C$1:$C$6),1)

4. Click OK

Validation List Name

Apply the Data Validation

1. Select the cells in which you want to create a drop down list of names
2. From the Data menu, choose Validation.
3. From the Allow dropdown list, choose List
4. In the Source box, type an equal sign and the list name, for example: =NameCheck
5. Click OK.

Apply data validation

Test the Drop Down List

Select an employee name in cell B2, and another name in cell B3.

When you open the list in cell B4, the list shows only the names that have NOT been used. Other names have been removed from the list.

Test data validation

Download the Sample File

To download a zipped sample file, click here:
Excel Data Validation -- Hidden Items -- Sample

Hidden Items With Dependent Data Validation

This hidden items technique can be modified, and used with Dependent Data Validation, as in the zipped sample workbook found here: Hide Previously Used - Dependent

Multiple Column Data Validation

This technique for hiding used items can be modified, and used with multiple columns. For example, employee names are removed from data validation dropdown lists after they've been assigned to a daily task.

Test data validation

To download this example, go to the Excel Sample Spreadsheets page, and in the Data Validation section, look for DV0016 - Assign Employees to Single Task Per Day

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

 

Data Validation Tutorials

Data Validation Basics
Create Dependent Drop Down Lists
Create Dependent Drop Downs from a Sorted List  
Create Dependent Drop Downs With INDEX  
Hide Previously Used Items in a Dropdown List
Data Validation - Display Messages to the User
Data Validation - Display Input Messages in a Text Box 
Data Validation - Use a List from Another Workbook
Data Validation Criteria Examples
Data Validation Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation With Combo Box     
Data Validation With Combo Box - Named Ranges
Data Validation With Combo Box -- Click
Data Validation - Add New Items  

 

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: October 22, 2014 7:48 PM