Data validation help

[000]

I have a list of part models and lists of part numbers according to those specific model types.

How can I get a drop down list with data validation to work so that if I choose one model from a drop down list, then only the specific part numbers that belong to that model will show in another drop down list?

Thanks!!

 

[seenfresh]

For example:

In Sheet1, range A1:A10 holds part numbers and range B1:B10 holds model types. Go-to Formulas on your tool bar and select define name. enter the following formula in the Refers To section: =Sheet1!$A$1:$A$10, name the range "MyRange".

In a separate worksheet, called "Summary", create a data validation drop down list in cell $A$1 by going to Data - Data Validation on your tool bar and under the Allow drop down select List type. Enter the following formula in the source section below: =MyRange

In cell $A$2 of your "Summary" worksheet, enter the following formula to be used as a dynamic reference to your list box: =Vlookup($A$1,Sheet1!A:B,2,0)

For each selection in your list box cell $A$2 will dynamically change to match the appropriate model type.

 

[000]

Thanks for the help. The only thing is that I would like the A2 cell to be a drop down list as well. Also, although there are a couple of different models, one specific model may have ten different part numbers associated with it. Will the vlookup function be able to pull those ten part numbers and put them in a drop down list?

 

[seenfresh]

I see what you mean...

Brainstorming, I would think the best way (maybe only way) is to use vba to accomplish this task. We will need to break it down into steps: Note, "Sheet1" is where your complete list of Part Numbers and Model Types exist.

Step 1: Part Number List Box source:= MyName1

Populate the first list box "Part Numbers" using vba, note list must include unique part numbers and exclude duplicates.... below is an example of how to do this:

Sub UniqueList()
'
' UniqueList Macro

Dim ws As Worksheet, ws1 As Worksheet
Dim lstrw As Long
Dim rng As Range
Set ws = Worksheets("Sheet1")
Set ws1 = Worksheets("Dump")


With ws
lstrw = .Range("A65536").End(xlUp).Row
Set rng = .Range("$A$1:$A$" & lstrw)
rng.Copy

End With

        With ws1
            .Range("$A$1").PasteSpecial (xlPasteValues)
            .Range("$A$1:$A$" & lstrw).RemoveDuplicates Columns:=1, Header:=xlNo
            Set rng1 = .Range("$A$1:$A$" & lstrw)
            ActiveWorkbook.Names.Add Name:="MyName1", RefersToR1C1:=rng1
        End With

Set rng = Nothing
Set rng1 = Nothing

End Sub

Second Part is a bit tricky and I'll have to brainstorm some more on this, but we need to some how assign a named range to the second list box "Model Types" that references the proper values based on the first list box selection... perhaps we can filter the list of Model Types by the Part Number selected in the first list box and apply the filtered range to the second list box... not sure if that will work, but worth a try I guess... Other option is to apply the correct values to the second list box without the use of name ranges.

 

[Peter_SSs]

Here are a couple of links that deal with Dependent Data Validation Lists.

http://www.mrexcel.com/forum/showthread.php?p=126

http://www.contextures.com/xlDataVal02.html