Contextures

Hide Used Items in Drop Down List

Limit the choices in an Excel drop down list, by hiding items that have been previously selected

Introduction

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

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.

names hidden if used

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.

Schedule worksheet

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 employee names

Calculate if a name has been used

Next, you'll add a formula beside each name, to check if that name has been used in the schedule.

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 the last name, in 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.

list of employee names

Create the list of unused names

The next step is to create a formula that will move any blank cells to the end of the list. There are two options:

1. For long lists, you can use the multi-cell array formula

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

array formula moves blank cells to end

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 List of Unused Names

Next, you'll create a named range, using the OFFSET function. Only the cells with names will be included, not the blank cells at the end of the list.

1. On the Ribbon's Formulas tab, click Define Name

2. In the Names in workbook box, type a one-word name for the range, e.g. NameCheck.

3. In the Scope box, leave Workbook as the selection

4. In the Refers to box, type the following formula (all on one line):

=OFFSET(Employees!$C$1,0,0,COUNT(Employees!$B$1:$B$6),1)

4. Click OK

The OFFSET formula creates a range that starts in cell C1. The number of rows in the range is based on the count of numbers in cells B1:B6.

dynamic formula for range with names

Add the Drop Down Lists

Next, you'll use Excel's Data Validation feature, to create drop down lists on the Schedule sheet

  1. On the Schedule sheet, select cells B2:B7, where the employee names will be entered.
  2. On the Ribbon's Data tab, click Data Validation.
  3. From the Allow drop down list, choose List
  4. In the Source box, type an equal sign and the list name: =NameCheck
  5. Click OK.

add the drop down lists

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.

names hidden if used

Other Examples

There are other examples of this technique, and you can download these workbooks too, using the links in the next section.

Baseball Players

Select a player for each position, for each of the nine innings of a baseball game. After you choose a name in an inning, it disappears from that inning's drop down list. The lists for the other innings are not affected.

In the screen shot below, Mike was selected as the pitcher, so his name is not in the drop down list. Players who have been picked for the second inning still show up in the list for the first inning.

assign players per inning

Dependent Drop Downs

This technique can also be adapted to work with dependent drop down lists. In the screen shot below, a Type can be selected multiple times in column A. However, in column B, each item can onlybe selected once. In cell B4, the SI-02 item is not in the SI list, because it was selected in cell B2.

dependent drop down list with hidden items

Selections Across a Row

In another example, the layout is different -- selections are made across a row, instead of down a column. In the screen shot below, Cam's name is removed from the lists in Row 2, after being assigned a task for that day.

selections made across a row

Get the Workbooks

1. Employee Schedule: Download the zipped workbook for this tutorials: Employee Schedule - Hide Used Items

2. Baseball Players: Download the zipped workbook to Assign Baseball Players per Inning.

3. Dependent Data Validation: Download the zipped workbook to Hide Used Items with Dependent Data Validation drop downs.

4. Selections Across a Row: Download the zipped workbook to hide items across a row with 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.

More Tutorials

Data Validation Basics

Dependent Drop Down Lists

Data Validation Tips

Search

Search Contextures Sites

 

Search Contextures Sites

 

pivot power premium

 

 

30 Excel Functions in 30 Days

 

Excel Data Entry Popup List

 

 

 

excel chart tools

 

Excel UserForms for Data Entry

 

Last updated: October 31, 2016 3:49 PM