Search Contextures Sites

Excel Data Validation -- Hide Previously Used Items in Dropdown

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
Create the Excel Data Validation List
Apply the Excel Data Validation
Test the Excel Data Validation
Dependent Data Validation

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


You can limit the choices in an Excel Data Validation list, hiding items that have been previously selected. For example, if you are assigning employees to a shift, you want to avoid assigning the same employee twice.

In the dropdown list, the names that have been used are removed.
Data Validation Hide Used

Set up the Main Table

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'


Create the Validation List

A) Enter a formula to calculate if a name has been used.

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


List of names

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


Validation list formulas

2. Copy the formula down to cell B6.

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.

B) Create the list of unused names

The next step is to create a multi-cell array formula which will move any blank cells to the end of the list.

  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

Single-Cell Formula Alternative

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 Excel Data Validation List

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 Excel Data Validation

1. Select the cells in which you want to apply data validation using the Excel data validation list
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 Excel Data Validation

The dropdown list in column B shows only the names that haven't been used. Other names have been removed.

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

Dependent Data Validation

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

 

 

 

Test data validation
1. Excel Data Validation
2. Data Validation -- Create Dependent Lists
3. Hide Previously Used Items in a Dropdown List
4. Display Messages to the User
5. Use a List from Another Workbook
6. Validation Criteria Examples
7. Custom Validation Criteria Examples
8. Data Validation Tips
9. Data Validation Documentation   
10 Data Validation -- Combo box      
11. Data Validation -- Combo Box - Named Ranges
12. Data Validation -- Display Input Messages in a Text Box  
13. Data Validation -- Dependent Dropdowns from a Sorted List  
14. Data Validation -- Combo Box -- Click
15. Data Validation -- Create Dependent Lists With INDEX  

 

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright © 2009.
All rights reserved.

 

Last updated: October 13, 2009 11:23 PM