Large Dependent Validation List Data

R

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



CarsColour 1Colour 2Colour 3Colour 4
VWBlueGreenRed
FordYellow
VauxhallRedPurpleBluePink

<tbody>
</tbody>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
With Data like this in sheet2:-
ABCDE
1CarsColour 1Colour 2Colour 3Colour 4
2VWBlueGreenRed
3FordYellow
4VauxhallRedPurpleBluePink
<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="101" style="width: 76pt;" span="5"> <tbody> </tbody>

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



MickG said:
With Data like this in sheet2:-
A
B
C
D
E
1
Cars
Colour 1
Colour 2
Colour 3
Colour 4
2
VW
Blue
Green
Red
3
Ford
Yellow
4
Vauxhall
Red
Purple
Blue
Pink

<tbody>
</tbody>

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
Click to expand...
 
Upvote 0
Try this...

Data Range
A​
B​
C​
D​
E​
1​
VW​
2​
3​
4​
5​
6​
7​
8​
9​
10​
Cars​
Colour 1​
Colour 2​
Colour 3​
Colour 4​
11​
VW​
Blue​
Green​
Red​
12​
Ford​
Yellow​
13​
Vauxhall​
Red​
Purple​
Blue​
Pink​

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
You must log in or register to reply here.

Similar threads

D
  • Question
Data Validation Lists
Replies
3
Views
258
etaf
etaf
N
  • Solved
Pulling certain row values if a number is matched
Replies
2
Views
191
Newbie73
N
M
  • Solved
Formula to find words in list of text data
Replies
9
Views
338
AhoyNC
A
C
  • Solved
Source Formatting from a Data Validation
2
Replies
10
Views
288
CantExcel123
C
A
  • Question
Show Data Validation Lists in Next Row of Table
Replies
0
Views
462
AndyPandy27
A

Forum statistics

Threads
1,215,459
Messages
6,124,944
Members
449,198
Latest member
MhammadishaqKhan

Share this page

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