Hide Used Items in Excel Drop Down List

You can add a drop down list in a cell, to make it easy for people to enter data. It’s really easy to make a simple drop down. Then, after you’ve made a basic list, experiment with fancier features, like hiding items that have been previously selected.

Make a Basic Drop Down List

It’s easy to make a drop down list with Data Validation. In this example, people will pick the name of the printers that they use in the office.

Hide Used Items in Drop Down List http://blog.contextures.com/

List the Items

First, create a list of printers for the drop down

  • List the printers on a different sheet, in a single column
  • Then, to name the list, select all the cells, click in the Name Box, type a one-word name “PrimaryList”, and press Enter

Here is the name, “PrimaryList“, being typed in the Name Box.

datavalprinterlists01

Create the Drop Down List

Next, select the cell(s) where you want the drop down list to appear

  • On the Excel Ribbon, click the Data tab
  • Click the Data Validation command
  • From the Allow drop down, select List
  • Click in the Source box, and press the F3 key, to see a list of the names in the workbook
  • Click on the PrimaryList name, and click OK. That name is added to the Source box, with an equal sign in front of it
  • Click OK, to create the drop down list in the cell.

datavalprinterlists02

Select an Item

Now, when the cell is active, you can see an arrow at the right border. Click the arrow, and select your primary printer from the list.

That blue cell has also been named – PPSel (short for Primary Printer Selected)

datavalprinterlists03

Hide Used Items

Now it’s time to make the data entry sheet a little fancier. We’ll add two more drop down lists, where people can choose the names of the backup printers that they use.

The list of printers is almost the same, but we don’t want them to pick their primary printer again. That printer should be removed from the list.

datavalprinterlists04

Number the Unused Items

To check which printer was selected as the primary printer, we’ll add a formula in the column beside the PrimaryList.

NOTE: I’ve formatted the list as a named Excel Table, so when I enter the formula in the first row, it will automatically fill down to the last row.

  • The new column has the heading NotUsed.
  • The formula in cell C4 is:

=IF(COUNTIF(PPSel,[@Printer]),””,SUM(MAX(C$3:C3),1))

That numbers every row, except the one with the printer selected as the Primary Printer (PPSel)

datavalprinterlists05

List the Backup Printers

Next, create another named table, with a list of ID numbers from 1 to 10 (to match the number of printers)

In the column to the right, put the heading, “Backup”

Put this formula in the first data row in that column:

=IF(G4>MAX(Table1[NotUsed]),””, INDEX(PrimaryList, MATCH([@ID], Table1[NotUsed],0)))

The formula:

  • Checks the ID number, to see if it’s greater than the MAX number in the NotUsed column
  • If it is greater the result is an empty string
  • Otherwise, it uses MATCH to find the position of that ID number, in the NotUsed column
  • The INDEX function returns the printer name from that row.

datavalprinterlists06

Name the Backup List

Next, you’ll create a dynamic named range with the list of Backup Printers.

  • Select the cell F2, just above the “Backup” heading cell
  • On the Excel Ribbon, click the Formulas tab, and click Define Name
  • Type “BackupList” as the name
  • In the Refers to box, enter the following OFFSET formula, and click OK

=OFFSET(Lists!$F$3,1,0,MAX(Table1[NotUsed]),1)

The formula returns a range of cells, with the number of rows based on the maximum number in the NotUsed column. You can see that range outlined with a green dashed line, in the screenshot below.

datavalprinterlists07

Add the Backup Printer Drop Down Lists

As the final step, add two more drop down lists on the data entry sheet.

For these drop downs, use the named range that you just created – BackupList

After you select a Primary Printer, its name won’t appear in the list for the Backup Printer selection cells.

datavalprinterlists04

Get the Sample Workbook

There are other examples, and the sample workbook for this example, on the Hide Used Items page of my Contextures site.

Hide Used Items in Drop Down List http://blog.contextures.com/

_________________

Save

You may also like...

7 Responses

  1. miaousse says:

    Hello,
    for the following formula
    =IF(COUNTIF(PPSel,[@Printer]),””,SUM(MAX(C$3:C3),1))

    in case of named table it would have interresting to be consistent and use

    =IF(COUNTIF(PPSel,[@Printer]),””,SUM(MAX([Printer]),1))

  2. Firas Shahadi says:

    it is really great and out of the box way.
    I would like to suggest similar way.
    1. to have a table (has two columns: option, isSelected) with all the possible options that we need.
    2. isSelected field will have a formula to check if the option has already selected in our range return 0 if not selected and 1 if selected.
    3. create a pivot table based on the table and filter out the 1s.

    hope it make sense.
    Regards

  3. Randy says:

    I’m using Excel 2016 and I’m finding that the column to which I have assigned a validation list is automatically removing used items from the list and this is NOT the behavior I want. I want to be able to see the entire list for every cell where I have it enabled, but I am not seeing any sort of option where this can be changed. Any help?

    Thanks

    • Randy, are you using a named range as the source for your drop down list?
      Or are you using an address, such as D2:D10
      If you’re using an address, be sure to lock that range, or it will adjust as you move down the worksheet. Use dollar signs in the address, to lock it (an absolute reference): $D$2:$D$10

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *