Leading the way in Microsoft Office Development

Step 1 - Highlight the cells

Data Validation can be used to restrict the entry of data within a column.

This example shows you how to prevent a user from selecting the same value more than once from a drop-down list.

Basically once the entry has been selected the item is automatically removed from the other drop-down lists.

Many thanks to Peo Sjoblom and Daniel M for this solution.

This solution is quite advanced and requires a good understanding of both functions and formulas.

Step 2 - Find the correct formula

First create a list of all the items you want to appear in the drop-down list.

In this example we have entered these as a list in column "D" in cells "D3:D8".

Next you need to add a formula that can indicate if the entry has already been selected.

The COUNTIF() function returns the number of non blank cells with a value that satisfies a condition.

The ROW() function returns the row number of the cell containing the formula.

In this example these formulas are going to be entered in column "E" in cells "E3:E8".

=IF(COUNTIF($B$3:$B$8,D3)>=1,"",ROW())

If this particular entry already exists in column B the cell will appear blank otherwise, the corresponding row number will be displayed.

Drag this formula down to the cells below ("E4:E8").

Using the formula that we have just entered we need to enter another formula.

This formula will create a list of "unused" items containing only the items that have not already been entered into column "B".

The COUNT() function returns the number of numeric values in a list or array of numbers.

The INDEX() function returns the value from a list based on an index number.

The SMALL() function returns the smallest value in a data set.

The INDIRECT() function returns a text string of the contents of a given cell reference.

The ROWS() function returns the number of rows in a cell range or reference.

In this example these formulas are going to be entered in column "E" in cells "E11:E16".

=IF(ROW(D3)+1-3>COUNT(E$3:E$8),"",INDEX(D:D,SMALL(E$3:E$8,ROW(D3)+1-3)))

The number 3 represents the row of the first item in the list from cell "D3".

Step 3 - Activate the Data Validation dialog box

Highlight the cells you want to apply the data validation to. In this case the whole of column B.

Press (Data > Validation) to display the Data Validation dialog box and select the Settings tab.

In the "Allow" drop-down box, select "List" (or Custom).

Enter the following formula as the Source:

=OFFSET($E$11,0,0,COUNTA($E$11:$E$16)-COUNTBLANK($E$11:$E$16),1)

For more details on these types of formulas, please refer to the Dynamic Named Ranges page.

Step 4 - Enter some values

Select cell "B3". Notice that the drop-down list displays all six items.

Pick "United Kingdom" from the list and then select "B4".

Notice that only the only five items are now displayed.