Excel – Drop down of values in on column based on another column

microsoft excel

I have data that looks like this:

Category    ProductName    ABunchOfOtherFields....
--------------------------------------------------
Drink       Soda
Drink       Milk
Drink       Water
Fruit       Apple
Fruit       Orange
Fruit       Banana

I'd like to make 2 drop downs, one for the drinks and one for the fruits. So far I have been using the data validation tool, but I cannot figure out how to filter the values of one column based on another column.

EDIT:

There are more columns in this data and the sheet this data sits on is populated from a db connection, i.e. I cannot change the data here, and I will not know how many rows are in each category, so manually selecting them is out of the question.

I also am not looking for cascading drop downs. On another sheet, I have pre-selected categories and I want to generate a group of products with those categories.

Beverage: [Dropdown of all drinks] [Fields populated from the selected drink]
Snack:    [Dropdown of all fruits] [Fields populated from the selected fruit]
etc...

The names of the categories will be different than the names of the row headers for each selection, which is also out of my control. So exactly what I am looking for is how to generate each of these drop downs with a filtered list from the data sheet that requires looking at the category field.

Best Answer

If you mean you want to get only Apple, Orange and Banana when you pick Fruit in the first dropdown, then I think the simplest solution would be what follows. Note that the first dropdown should have been already created. Let's say that it's in cell C1:

  1. Create a named range for each catergory. To create a named range, you have two quick ways:

    • Go to "Formulas" > "Define Name", pick a name, and select the range. In your example, you would select Soda, Milk, Water and name the range as Drink. Repeat with Fruit for the next one.

    • Select the first range (Soda, Milk, Water) and in the name box (the box to the right of the formula bar), type in the name of the named range, for this instance Drink and repeat for the others.

  2. In the cell you want to place the second dropdown, use a data validation and pick "List" and in the 'Source', use the formula:

    =INDIRECT(C1)

    This will pick the appropriate named range and get only the Products that corresponds to the relevant category, of course, if the naming of ranges was properly done.

Related Question