Excel - Drop down list values based on values in rows matching certain text

Question

I have a main table in one Excel worksheet:

And a Products table (named Products in Name Manager) in another worksheet:

What I want is for the values available in the drop-down boxes in the main table to be looked up from the Products table. For example, the cell that is selected in the first screenshot has the word 'model' next to it. Therefore, I want to go through the products table, looking for any row that has the word 'model' in the 'type' column. When we find it, I want the value in the 'name' column to be available in the drop-down. I want this for every row in the Products table with 'model' in the 'type' column.

After all this, we move to the next cell in the first range, which has 'graphics' next to it. This time, I want to go through, looking in the Products table for 'graphics.' Etc, etc.

In trying to do this, I used this formula in the name manager, and used it for the source of the cell drop-down lists:

=INDEX(Products[[#Data],[Name]],MATCH("Model",Products[[#Data],[Type]],0))

However, the problem with this is that only the first match appears in the list. In other words, it works, but the list is only one item long. I found this post which is pretty similar to what I'm trying to do.

MDChaara MDChaara · Accepted Answer · 2016-04-11T13:53:52

The way I would do it would involve helper columns and a simple macro, and will do what you want.

Part 1: VBA Macro

This macro will sort the items in the price list. You should run it every time you add a new item:

Sub Sorter()

    Sheets("Products").select
    Range("A:D").Select
    ActiveWorkbook.Worksheets("Products").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Products").Sort.SortFields.Add Key:=Range("B:B") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Products").Sort.SortFields.Add Key:=Range("C:C") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Products").Sort
        .SetRange Range("A:D")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Part 2: Products Helper Columns:

In the products sheet, in column F insert the following formula:

Column F (Cell F2): =B2&"-"&C2

This formula will be used for getting the price of the item in case there are multiple items with similar name under different categories.

Part 3: Drop down list Helper column

In the Drop Down list sheet, add a new helper column in column G with the following formula:

="Products!$C"&MATCH($A2,Products!$B:$B,0)&":$C"&SUM(COUNTIF(Products!$B:$B,$A2),MATCH($A2,Products!$B:$B,0)-1)

The Data Validation formula should be looking at the range shown in cells G using indirect (For example: =INDIRECT($G2)

A link to the file is attached here.

You can hide the helper columns but make sure that the formulas are dragged when new items are added.

To get the item price, use this formula:

=SUMIF(Products!$F:$F,'Drop Down'!$A2&"-"&'Drop Down'!$B2,Products!$D:$D)

This will make sure that if 2 categories has similar item name the price will be for the item intended.

I hope this helps.

Excel - Drop down list values based on values in rows matching certain text

1 Answers