Lookup validation data

[Guest]

I have a problem I thought would be simple!
Worksheet 1
A B C
MTRL-A Validation list VALUE1
MTRL-B
MTRL-C

Worksheet 2
MTRL-A Length
MTRL-A Diameter
MTRL-A Color
MTRL-B Length
MTRL-C Tensile Strength

This should be simple. On Worksheet 1, a material number is enterred. In
column B I want a list of the only the matching values in worksheet 2. So if
MTRL-A is enterred by the user, only the values Length, Diameter, and Color
are valid entries in column B. If they enter MTRL-C, only Tensile Strength
is a valid entry.

This is a piece of cake in Access. How do I do it in Excel? I tried using
VLookup in my validation list source but I only get the last valid value, not
all the valid values. Any suggestions?

Craig

 

[Guest]

Craig,
Try this. It creates names ranges("Attribute1", Attribute2" etx)
corresponding to th validation list for MTRL-A,MTRl-B etc. You need named
ranges as the validations are not on the same sheet as the list source.

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
attn = 1

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
material = .Cells(r, "A")

res = Application.Match(material, ws2.Range("a1:a10"), 0) '<===
change Range to suit your list in sheet2
n = Application.CountIf(ws2.Range("a1:a10"), material) '<=== change
Range to suit your list in sheet2

Set valrng = ws2.Range("B" & res & ":B" & res + n - 1)
ActiveWorkbook.Names.Add Name:="Attributes" & attn, RefersTo:=valrng

With .Range("B" & r).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=attributes" & attn
End With

attn = attn + 1

Next r

End With

HTH