Excel tutorials

Displays a drop-down menu a country different from the associated name as a condition

Return the list of states in the drop-down menu for all cells that do not belong to John Smith

If you've ever had the need to create a 'DropDown Menu' or 'DropDown List'
(Data Validation) with one condition (1 criterion), you probably found a lot of solutions and examples on the Internet. This task that is shown in this Excel example has a specific requirement and this is the following:

How to display all states in column 'A', which are defined criteria "<> John" (different from the name) in the column 'B'. So which states do not belong to John. One of the solutions can be found in the text and pictures below.

The situation is the next. See the picture below.

In column "A" there are states. In column "B" there are names that belong to certain countries. The cell 'D1' contains a criterion (condition) according to which the cell 'C8' should show a drop-down menu showing the list of all countries that do not belong to 'John'.

list all country if match different criteria

For this Excel example tutorial I have created two auxiliary columns (helper columns 'E' and 'F'). (see picture below)

In the column 'E' in cell 'E2', place the following formula below. (This formula returns as a result all the states that do not belong to the name 'John' or, in other words return all the states of the row is different from the names 'John' (<> John)

=IF(B2<>$D$1;A2;"")

Copy it to the desired row. Note that the name of the state is the result, but there is an empty cell between the states (of course we do not want this because an empty item will be displayed in the drop-down menu).

In the cell 'F2' set the following ARRAY formula below. (This formula returns a list of all countries from the cell range 'E2:E5' but without empty cells. You can end the form with Ctrl + Shift + Enter).

=IFERROR(INDEX($E$1:$E$5;SMALL(IF($E$1:$E$5<>"";ROW($E$1:$E$5)-ROW(A$1)+1);ROWS($E$1:E2)));"")

Data validation drop down menu with one condition

The next step is naming cell ranges. We can work directly with the cell range, but I like to work with defined cell names and cell ranges because it is easier for me. To define the cell range name, refer to the relevant link. In this Excel example I have named the cell range 'F2:F5' with the name 'CountryWithoutBlank'. This cell range name will be used when creating Data Validation.

Named range in excel

How to create a Data Validation drop-down menu

In the 'C8' cell, set the Data Validation drop-down menu. On the 'DATA' tab in the 'Data Tools' group, click 'Data Validation' to open the dialog window as displayed below. In the "Allow" field, select 'List' and enter '=CountryWithoutBlank' in the 'Source' field. Click the 'OK' button to close the dialog window.

Creating data validation in excel

Now in cell 'C8' you have an active drop-down menu. Depending on the condition, ie the name entered in cell 'D1', you will see all countries that do not belong to 'John'. See the second image in the order at the beginning of this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.