Data Validation List

[Studk22]

Hello,

I have a fairly large worksheet with many options. I am trying to create a tool to populate an answer based on multiple criteria which can be selected from 3 data validation lists. The first selection is the type of surface finish called "Commodities" (fabrics, paints, laminates,etc...6 options total). The second selection is colors available by commodity (over 20 options per commodity). The third selection is the products available in the selected color (averages about 15-20 products per color).

The first data validation list (Commodity Selection) is set and won't change in the future. I would like the second data validation selection list (Colors) to populate from the table based on the commodity selections. Then I would like the third data validation list to populate from the main table of information based on the color selected.

Is this possible to do without creating multiple lists of products by colors by commodities to allow for indirect data validation lists?

 

[Sthrncali]

You will need 3 named lists to accomplish this.

The first would be your static list commodity selection.

The second would be a temoporary list. You can create a dynamic named list that will expand/contract automatically based on how many cells have data in a col. So all you need do do is have a macro that fires when your commodity lists is changed..

i.e. user changes the selected commodity.

VBA:

Private Sub Worksheet Change()
If Target.Address = YourCommodity Then
'Populate Temp List with Possible Color Combos for the selected commidity.
End if

If Target.Address = YourColor Then
'Populate Temp List with Available Products for the selected Color/Commodity combination.
End If
End Sub


To get the available colors/products, simply loop through your table like so..

Dim myRange as Range
Dim myCommodity as String
Set myRange = YourSheet.Range("A:A") 'The column in your table that contains the colors available.
myCommodity = CommodityDropDown.Value

For each cell in myRange
If cell.offset(0,2).value = myCommodity Then 'Tell excel which direction from the Color Column Commodities are stored in.
'Add cell.value to Temp List
End If
Next

'Repeat for available products for each color/commodity combo.

Hopefully this gives you an idea on how to accomplish... If you still need help writing the exact code for your application, please provide the details of your master table, i.e. dimensions of the table, and the columns of interest (commodity, color, products).

 

[Taul]

alternative approach using data validation
http://www.contextures.com/xldataval02.html

 

[Studk22]

Thanks for your help. I'm not quite understanding the proposed solution but I think what might work is creating pivot tables that filter the color names but I need the filter to change based on another cell value.

My data validation list is on worksheet "Tool" and the cell with the data validation list is range E2:F2.

My pivot table name is "Colors", my pivot field name is "Color Names" and the filter is the color available for each commodity which is in cell AJ7.

Do you know how I could link the selection from the data validation list on the "Tool" worksheet to filter the pivot table "Color?" Also, if a macro is the answer do I put this macro in the Worksheet "Tool"?

Thanks for your help!

- Excel Novice

 

[Sthrncali]

You can set the filter of a Pivot Table with VBA like this:

Sheet3.PivotTables("PivotTable1").PivotFields("Call Center").CurrentPage = "Criteria"

Where PivotTable1 is the name of your PivotTable and "Call Center" is the label on your filter.

"Criteria" can be a variable, so in your case if you wanted to use a sell value it would be something like:

Dim Criteria as String
Criteria = Sheet1.Range("A1").value
Sheet3.PivotTables("PivotTable1").PivotFields("Call Center").CurrentPage = Criteria

You can tie this macro to a WS Change event and tie it to the change event of a specific cell or range (where your user selects a color for example).

Singe Cell

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
'Set Filter
End If

Range of Cells

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
'Set Filter
End If
 
End Sub

 

[Studk22]

So how would I input all of that into a worksheet macro?

Thanks!