Large Dependent Validation List Data

Raj1313

Board Regular
Joined
Nov 20, 2014
Messages
125
hi I have a large set of data in rows - and need to create dependent validation lists. is there a quick way of doing this?

EG

Colum A list of Cars. Column B,C,D,E list of colours. So when I select VW in Sheet Cell A1 list of cars (I need to be able to select in B2 the possible colours available. Blue/Green/Red/Data Below



[TABLE="width: 500"]
<tbody>[TR]
[TD]Cars[/TD]
[TD]Colour 1[/TD]
[TD]Colour 2[/TD]
[TD]Colour 3[/TD]
[TD]Colour 4[/TD]
[/TR]
[TR]
[TD]VW[/TD]
[TD]Blue[/TD]
[TD]Green[/TD]
[TD]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ford[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vauxhall[/TD]
[TD]Red[/TD]
[TD]Purple[/TD]
[TD]Blue[/TD]
[TD]Pink[/TD]
[/TR]
</tbody>[/TABLE]
 
With Data like this in sheet2:-
[TABLE="width: 400"]
<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="101" style="width: 76pt;" span="5"> <tbody>[TR]
[TD="class: xl66, width: 27, bgcolor: #DAEEF3"] [/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]A[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]B[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]C[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]D[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]E[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]1[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Cars[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Colour 1[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Colour 2[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Colour 3[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Colour 4[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]2[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]VW[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Blue[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Green[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Red[/TD]
[TD="class: xl65, width: 101, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]3[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Ford[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Yellow[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"] [/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"] [/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]4[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Vauxhall[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Red[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Purple[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Blue[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Pink[/TD]
[/TR]
</tbody>[/TABLE]

Place this code in your "Validation list" Sheet
The code will Insert the validation in "A1" when you Select that "A1"l, and in "B1" when you Select a Make of Car from "A1".

Code:
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ray
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
        [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(Application.Transpose(Rng), ",")
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nstr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR="Navy"]Then[/COLOR]
   [COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
        [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Dn.Value = Target.Value [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = .Range(.Cells(Dn.Row, 2), .Cells(Dn.Row, Columns.Count).End(xlToLeft))
                 [COLOR="Navy"]If[/COLOR] nRng.Count = 1 [COLOR="Navy"]Then[/COLOR]
                    nstr = nRng.Value
                 [COLOR="Navy"]Else[/COLOR]
                     nstr = Join(Application.Transpose(Application.Transpose(nRng)), ",")
                 [COLOR="Navy"]End[/COLOR] If
                 [COLOR="Navy"]Exit[/COLOR] For
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
Range("B1").Value = ""
[COLOR="Navy"]With[/COLOR] Range("B1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nstr
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick


code
 
Upvote 0
Mick - Thanks for the code.

Is there anyway of doing this via Excel formula - as this will be loaded on a shared document with approx. 100 rows. and it will no always be A1



With Data like this in sheet2:-
[TABLE="width: 400"]
<tbody>[TR]
[TD="class: xl66, width: 27, bgcolor: #DAEEF3"][/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]A
[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]B
[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]C
[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]D
[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]E
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]1
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Cars
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Colour 1
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Colour 2
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Colour 3
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Colour 4
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]2
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]VW
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Blue
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Green
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Red
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]3
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Ford
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]Yellow
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"][/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"][/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]4
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Vauxhall
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Red
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Purple
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Blue
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]Pink
[/TD]
[/TR]
</tbody>[/TABLE]

Place this code in your "Validation list" Sheet
The code will Insert the validation in "A1" when you Select that "A1"l, and in "B1" when you Select a Make of Car from "A1".

Code:
Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] ray
[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=navy]Then[/COLOR]
    [COLOR=navy]With[/COLOR] Sheets("Sheet2")
        [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
    [COLOR=navy]End[/COLOR] With
[COLOR=navy]With[/COLOR] Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(Application.Transpose(Rng), ",")
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, nstr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] nRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=navy]Then[/COLOR]
   [COLOR=navy]With[/COLOR] Sheets("Sheet2")
        [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
            [COLOR=navy]If[/COLOR] Dn.Value = Target.Value [COLOR=navy]Then[/COLOR]
                [COLOR=navy]Set[/COLOR] nRng = .Range(.Cells(Dn.Row, 2), .Cells(Dn.Row, Columns.Count).End(xlToLeft))
                 [COLOR=navy]If[/COLOR] nRng.Count = 1 [COLOR=navy]Then[/COLOR]
                    nstr = nRng.Value
                 [COLOR=navy]Else[/COLOR]
                     nstr = Join(Application.Transpose(Application.Transpose(nRng)), ",")
                 [COLOR=navy]End[/COLOR] If
                 [COLOR=navy]Exit[/COLOR] For
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] With
Range("B1").Value = ""
[COLOR=navy]With[/COLOR] Range("B1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nstr
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick


code
 
Upvote 0
Try this...

Data Range
[Table="class: grid"][tr][td="bgcolor: #c0c0c0"][/td][td="bgcolor: #c0c0c0"]
A
[/td][td="bgcolor: #c0c0c0"]
B
[/td][td="bgcolor: #c0c0c0"]
C
[/td][td="bgcolor: #c0c0c0"]
D
[/td][td="bgcolor: #c0c0c0"]
E
[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]
1
[/td][td]
VW​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
2
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
3
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
4
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
5
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
6
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
7
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
8
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
9
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
10
[/td][td]
Cars​
[/td][td]
Colour 1​
[/td][td]
Colour 2​
[/td][td]
Colour 3​
[/td][td]
Colour 4​
[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
11
[/td][td]
VW​
[/td][td]
Blue​
[/td][td]
Green​
[/td][td]
Red​
[/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
12
[/td][td]
Ford​
[/td][td]
Yellow​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]
13
[/td][td]
Vauxhall​
[/td][td]
Red​
[/td][td]
Purple​
[/td][td]
Blue​
[/td][td]
Pink​
[/td][/tr]
[/table]


A1 is the drop down list of car brands.

As the source for the color drop down use this formula:

=OFFSET(A11,MATCH(A1,A11:A13,0)-1,1,1,COUNTA(INDEX(B11:E13,MATCH(A1,A11:A13,0),0)))
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top