How to make a Data Validation Dependent Drop Down List From the precedent Row?

Lacan · Feb 10, 2020

Hello Guys,

Have this massive extent List that would like to make a Data Validation Dependent Drop Down List from the precedent row.

Will give you a sample data with 4 examples to be very clear that what is the goal in order to choose from Data Validation Dependent Drop Down List from the precedent row.

Can you help me?

Thanks a lot.

Book1

B

C

D

E

F

G

H

I

J

K

2

Brick

Center

City

Name

Brick

Center

City

Name

3

299 Lx - Amadora (MÁgua - Sul)

CENTRO CLINICO AVENIDA

Amadora

Anna

Example 1

299 Lx - Amadora (MÁgua - Sul)

CENTRO CLINICO AVENIDA

Amadora

Anna

4

299 Lx - Amadora (MÁgua - Sul)

CENTRO CLINICO AVENIDA

Amadora

Anna Simon

Example 2

300 Lx - Amadora (MÁgua - Sul)

Clínica LAR MEDICO

Samouco

Anna

5

299 Lx - Amadora (MÁgua - Sul)

CENTRO CLINICO AVENIDA

Amadora

Aladin

Example 3

301 Lx - Amadora (MÁgua - Sul)

Clínica LAR JONH

Amadora

Luzia

6

299 Lx - Amadora (MÁgua - Sul)

CENTRO CLINICO AVENIDA

Amadora

Serge

Example 4

302 Lx - Amadora (MÁgua - Sul)

Clínica MEDICA SA

Lisboa

Pedro

7

299 Lx - Amadora (MÁgua - Sul)

CENTRO CLINICO AVENIDA

Amadora

Fernando

8

299 Lx - Amadora (MÁgua - Sul)

CENTRO CLINICO AVENIDA

Amadora

Blyde

9

299 Lx - Amadora (MÁgua - Sul)

CENTRO CLINICO AVENIDA

Amadora

Dynom

10

300 Lx - Amadora (MÁgua - Sul)

CENTRO CLINICO AVENIDA

Amadora

Venom

11

300 Lx - Amadora (MÁgua - Sul)

CENTRO CLINICO FRANK

Amadora

Yalan

12

300 Lx - Amadora (MÁgua - Sul)

Clínica LAR MEDICO

Samouco

Anna

13

300 Lx - Amadora (MÁgua - Sul)

Clínica LAR MEDICO

Samouco

Costa

14

300 Lx - Amadora (MÁgua - Sul)

Clínica LAR MEDICO

Samouco

Maria

15

300 Lx - Amadora (MÁgua - Sul)

Clínica LAR MEDICO

Samouco

Cesar

16

300 Lx - Amadora (MÁgua - Sul)

Clínica LAR MEDICO

Samouco

Carlos

17

301 Lx - Amadora (MÁgua - Sul)

Hospital

Amadora

Maria

18

301 Lx - Amadora (MÁgua - Sul)

Clínica LAR JONH

Amadora

Jesus

19

301 Lx - Amadora (MÁgua - Sul)

Clínica LAR JONH

Amadora

Maria

20

301 Lx - Amadora (MÁgua - Sul)

Clínica LAR JONH

Amadora

Luzia

21

301 Lx - Amadora (MÁgua - Sul)

Clínica MEDICA SA

Lisboa

Guilherme

22

302 Lx - Amadora (MÁgua - Sul)

Clínica MEDICA SA

Lisboa

Rogerio

23

302 Lx - Amadora (MÁgua - Sul)

Clínica MEDICA SA

Lisboa

Pedro

24

302 Lx - Amadora (MÁgua - Sul)

Dr Peter Sa

Samouco

Albino

25

302 Lx - Amadora (MÁgua - Sul)

Consultorio Dr King

Lisboa

Fernando

Folha6

Lacan · Feb 13, 2020

Akuini said:

Hi, Lacan
If you already have Dynamic Array function in your Office 365, then you can try this example of dependent data validation (but it's only 3 column data validation). It uses VBA + formula.
Let me know if you're interested in this method & need help to apply it for 4 column data validation in your workbook.
dhee - 3 data validation, dependent, dynamic array 365.xlsm

VBA Code:

'=================================================================================================
'=============== YOU MAY NEED TO ADJUST THE CODE IN THIS PART: ===================================

'sheet's name where the list for data validation is located. [in the sample: sheet "sheet2"]
Private Const sList As String = "sheet2"

'table's name where the list for data validation is located. [in the sample: "Table1"]
Private Const sTable As String = "Table1"

'the helper column
Private Const xH As String = "E"

'range where data validation is located
Private Const sDV1 As String = "B2:B10"  ' for data validation 1
Private Const sDV2 As String = "C2:C10"  ' for data validation 2
Private Const sDV3 As String = "D2:D10"  ' for data validation 2

'==================================================================================================
'==================================================================================================

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.CountLarge > 1 Then Exit Sub
'    On Error GoTo skip:
    If Not Intersect(Target, Union(Range(sDV1), Range(sDV2), Range(sDV3))) Is Nothing Then
        Dim f As Range
        Dim MN As String, tx1 As String, tx2 As String, tx3 As String
       
        Application.EnableEvents = False
        Sheets(sList).Columns(xH).ClearContents

        Set f = Sheets(sList).ListObjects(sTable).DataBodyRange
        tx1 = f.Columns(1).Address
        tx2 = f.Columns(2).Address
        tx3 = f.Columns(3).Address
        MN = Me.Name & "!"
       
        With Sheets(sList).Range(xH & 1)
            If Target.Column = Range(sDV1).Column Then
                .Value = "=UNIQUE(" & tx1 & ")"
                           
            ElseIf Target.Column = Range(sDV2).Column And Target.Offset(, -1) <> "" Then
                .Value = "=UNIQUE(FILTER(" & tx2 & "," & tx1 & "=" & MN & Target.Offset(, -1).Address & "))"
           
            ElseIf Target.Column = Range(sDV3).Column And Target.Offset(, -1) <> "" And Target.Offset(, -2) <> "" Then
                .Value = "=UNIQUE(FILTER(" & tx3 & ",(" & tx1 & "=" & _
                MN & Target.Offset(, -2).Address & ")*(" & tx2 & "=" & MN & Target.Offset(, -1).Address & ")))"

            End If
        End With
       
        Application.EnableEvents = True

    End If


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge <> 1 Then Exit Sub
    If Not Intersect(Target, Union(Range(sDV1), Range(sDV2))) Is Nothing Then
       
        Application.EnableEvents = False
            If Target.Column = Range(sDV1).Column Then
            Target.Offset(, 1).Resize(, 2).ClearContents
            ElseIf Target.Column = Range(sDV2).Column Then
            Target.Offset(, 1).ClearContents
            End If
        Application.EnableEvents = True
   
    End If
End Sub

Thank you ver much my Friend.

However to make crystal clear the goals I make another post thread with all data that everyone understands what want to achieve.

So please give your opinnion:

How to create a Data Validation Drop-down list based on another Data Validation Drop-down list in precedent Row? (Cascade Drop Down Menu)

Thanks again.

Akuini · Feb 13, 2020

Lacan said:
So please give your opinnion:

Sorry, I don't know how to do that without VBA.

Toadstool · Feb 14, 2020

I see the duplicated thread has been closed so I'll repost here my very slow (5 seconds per cycle on my laptop) response using Excel 2016 with CELL ADDRESS and INDIRECT functions.

Lacan.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

2

Area

Centre

City

Name

Area

Centre

City

Name

$G$5

$H$5

$I$5

3

299 Gotham

Adams Clinic

Gotham

John

302 Xanadu

Holby General

Gotham

Michael

Area

Centre

City

Name

4

299 Gotham

Adams Clinic

Gotham

Sarah

301 Sherwood

Faith Medical

Rome

Gillian

5

299 Gotham

Adams Clinic

Gotham

Simon

299 Gotham

Adams Clinic

Paris

James

6

299 Gotham

Adams Clinic

Paris

Margaret

302 Xanadu

7

299 Gotham

Adams Clinic

Paris

James

8

299 Gotham

Faith Medical

Paris

Peter

9

299 Gotham

Faith Medical

Gotham

Vanessa

10

301 Sherwood

Faith Medical

Gotham

Susan

11

301 Sherwood

Faith Medical

Rome

Gillian

12

301 Sherwood

Holby General

Rome

Alex

13

301 Sherwood

Holby General

Rome

James

14

302 Xanadu

Holby General

Paris

Gary

15

302 Xanadu

Holby General

Gotham

Michael

16

302 Xanadu

Clinic Osso

Paris

Thomas

17

302 Xanadu

Clinic Osso

Milan

Thomas

18

Sheet1

Cell Formulas
Range	Formula
L1:O1	L1	=CELL("address",L4)&":"&ADDRESS(COUNTIF(L$4:L$1000,"> ")+ROW(L$3),COLUMN())
M2:O2	M2	=ADDRESS(COUNTIF(G$3:G$10,"> ")+ROW(G$2),COLUMN(G$2))
L4:L18	L4	=INDEX(B$3:B$2500,MATCH(0,INDEX(COUNTIF(L$3:L3,B$3:B$2500),),0))&""
M4:M18	M4	=IFERROR(INDEX(C$3:C$2500,AGGREGATE(15,6,ROW(C$3:C$2500)-ROW($C$2)/((B$3:B$2500=INDIRECT(M$2))*(COUNTIF(M$3:M3,C$3:C$2500)=0)),1)),"")
N4:N18	N4	=IFERROR(INDEX(D$3:D$2500,AGGREGATE(15,6,ROW(D$3:D$2500)-ROW($C$2)/((C$3:C$2500=INDIRECT(N$2))(B$3:B$2500=INDIRECT(M$2))(COUNTIF(N$3:N3,D$3:D$2500)=0)),1)),"")
O4:O18	O4	=IFERROR(INDEX(E$3:E$2500,AGGREGATE(15,6,ROW(E$3:E$2500)-ROW($C$2)/((D$3:D$2500=INDIRECT(O$2))(C$3:C$2500=INDIRECT(N$2))(B$3:B$2500=INDIRECT(M$2))*(COUNTIF(O$3:O3,E$3:E$2500)=0)),1)),"")

Cells with Data Validation
Cell	Allow	Criteria
G3:J12	List	=INDIRECT(L$1)

Lacan · Feb 15, 2020

Dear Toadstool,

U are the man, thanks again pal.

However want make a last small change in the structure like this:

1. Want to make tables with validation drop menu each one correletad with the corresponding reference in the R column.

2. The reference cell in the R column its any value in data validation menu.

ALL DATA:

Book1

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

1

2

area

Centre

City

Name

Area

Centre

City

Name

$G$4

$H$3

$I$3

3

299 Gotham

Adams Clinic

Gotham

John

299 Gotham

Adams Clinic

Gotham

John

Area

Centre

City

Name

299 Gotham

Centre

City

Name

4

299 Gotham

Adams Clinic

Gotham

Sarah

299 Gotham

Adams Clinic

Gotham

John

Adams Clinic

Gotham

Sarah

5

299 Gotham

Adams Clinic

Gotham

Simon

6

299 Gotham

Adams Clinic

Paris

Margaret

302 Xanadu

Simon

7

299 Gotham

Adams Clinic

Paris

James

8

299 Gotham

Faith Medical

Paris

Peter

301 Sherwood

Centre

City

Name

9

299 Gotham

Faith Medical

Gotham

Vanessa

Holby General

Rome

James

10

301 Sherwood

Faith Medical

Gotham

Susan

11

301 Sherwood

Holby General

Rome

Gillian

12

301 Sherwood

Holby General

Rome

Alex

13

301 Sherwood

Holby General

Rome

James

302 Xanadu

Centre

City

Name

14

302 Xanadu

Holby General

Paris

Gary

Holby General

Paris

Gary

15

302 Xanadu

Holby General

Gotham

Michael

16

302 Xanadu

Clinic Osso

Paris

Thomas

17

302 Xanadu

Clinic Osso

Milan

Thomas

Folha4

Cell Formulas
Range	Formula
L1, M1:O1	L1	=CELL("endereço",L4)&":"&ADDRESS(COUNTIF(L$4:L$1000,"> ")+ROW(L$3),COLUMN())
M2:O2	M2	=ADDRESS(COUNTIF(G$3:G$10,"> ")+ROW(G$2),COLUMN(G$2))
L4:L15	L4	=INDEX(B$3:B$2500,MATCH(0,INDEX(COUNTIF(L$3:L3,B$3:B$2500),),0))&""
M4:M15	M4	=IFERROR(INDEX(C$3:C$2500,AGGREGATE(15,6,ROW(C$3:C$2500)-ROW($C$2)/((B$3:B$2500=INDIRECT(M$2))*(COUNTIF(M$3:M3,C$3:C$2500)=0)),1)),"")
N4:N15	N4	=IFERROR(INDEX(D$3:D$2500,AGGREGATE(15,6,ROW(D$3:D$2500)-ROW($C$2)/((C$3:C$2500=INDIRECT(N$2))(B$3:B$2500=INDIRECT(M$2))(COUNTIF(N$3:N3,D$3:D$2500)=0)),1)),"")
O4:O15	O4	=IFERROR(INDEX(E$3:E$2500,AGGREGATE(15,6,ROW(E$3:E$2500)-ROW($C$2)/((D$3:D$2500=INDIRECT(O$2))(C$3:C$2500=INDIRECT(N$2))(B$3:B$2500=INDIRECT(M$2))*(COUNTIF(O$3:O3,E$3:E$2500)=0)),1)),"")

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Folha4!$B$2:$E$17	L4:O15
area	=Folha4!$B$3:$B$17	L4:O15
Centre	=Folha4!$C$3:$C$17	M4:O15
City	=Folha4!$D$3:$D$17	N4:O15
Name	=Folha4!$E$3:$E$17	O4:O15

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:E17	Expression	=contar.ses($B$3:$B$17;$G1048570;$C$3:$C$17;$H1048570)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G3:J12	List	=INDIRETO(L$1)
T9:V11	List	=INDIRETO(Y$1)
T4:V6	List	=INDIRETO(Y$1)
T14:V16	List	=INDIRETO(Y$1)

Can you help with the formulas?

Again Toadstool, dont have words to express my gratitude.

Toadstool · Feb 15, 2020

Hi Lacan,

I'm not sure I understand the change. Are you saying you want to replace the input area format in columns G, H, I and J (all dropdown Data Validation lists) with a similar approach but using the format in columns R, S, T, U and V?

So column R would have a Data Validation list offering each unique Area, then the three rows for T, U and V would offer Centre, City and Name only under that Area?

At the moment I use the number of Areas entered to figure out which row is being populated but with the new style that probably would not work well. I guess I could use =CELL("Address") to see what was the last active cell, e.g. if it was R3 then column M would know to offer Centre from the R3 content, but that will take a while to figure out.

Lacan · Feb 16, 2020

Hi Toadstool,

To resume and to clarify even better:

1. Yes, want a similar approach - dropdown Data Validation lists only in this tables range T4:V6 ; T9:V11 and T14:V16 offering Centre, City and Name only under that Area (in column R).

2. Acording with data refence in column R (Area) should get the corresponding Centre, City and Name in this tables.

Examples:

R3 (Area) should only corresp Centre, City and Name in table range T4:V6;
R8 (Area) should only corresp Centre, City and Name in table T9:V11;
R13 (Area) should only corresp Centre, City and Name in table T14:V16;

3. Ask for not worry with data validation menu - unique values in column R.

Thanks again Pal.

Toadstool · Feb 16, 2020

Lacan,

Even slower to support this format. Each section must be completed left to right as it build the completed Data Validation list from the entries just made..

Lacan3.xlsx

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Active

Address

2

Area

Centre

City

Name

Area

Centre

City

Name

3

299 Gotham

Adams Clinic

Gotham

John

299 Gotham

Adams Clinic

Gotham

Sarah

Area

Centre

City

Name

4

299 Gotham

Adams Clinic

Gotham

Sarah

Faith Medical

Paris

Peter

5

299 Gotham

Adams Clinic

Gotham

Simon

Adams Clinic

Paris

Margaret

6

299 Gotham

Adams Clinic

Paris

Margaret

302 Xanadu

7

299 Gotham

Adams Clinic

Paris

James

Area

Centre

City

Name

8

299 Gotham

Faith Medical

Paris

Peter

301 Sherwood

Holby General

Rome

James

9

299 Gotham

Faith Medical

Gotham

Vanessa

Faith Medical

Rome

Gillian

10

301 Sherwood

Faith Medical

Gotham

Susan

11

301 Sherwood

Faith Medical

Rome

Gillian

12

301 Sherwood

Holby General

Rome

Alex

Area

Centre

City

Name

13

301 Sherwood

Holby General

Rome

James

302 Xanadu

Faith Medical

14

302 Xanadu

Holby General

Paris

Gary

15

302 Xanadu

Holby General

Gotham

Michael

Sheet1

Cell Formulas
Range	Formula
M1:P1	M1	=CELL($R$1,M4)&":"&ADDRESS(COUNTIF(M$4:M$1000,"> ")+ROW(M$3),COLUMN())
N2	N2	=R2
O2:P2	O2	=ADDRESS(ROW(INDIRECT($Q$2)),COLUMN(I$1))
Q2	Q2	=CELL($R$1)
R2	R2	=CHOOSE(MIN(INT(ROW(INDIRECT($Q$2))+4)/5,3),CELL($R$1,G3),CELL(R1,G8),CELL($R$1,G13))
M4:M15	M4	=INDEX(B$3:B$2500,MATCH(0,INDEX(COUNTIF(M$3:M3,B$3:B$2500),),0))&""
N4:N15	N4	=IFERROR(INDEX(C$3:C$2500,AGGREGATE(15,6,ROW(C$3:C$2500)-ROW($C$2)/((B$3:B$2500=INDIRECT(N$2))*(COUNTIF(N$3:N3,C$3:C$2500)=0)),1)),"")
O4:O15	O4	=IFERROR(INDEX(D$3:D$2500,AGGREGATE(15,6,ROW(D$3:D$2500)-ROW($C$2)/((C$3:C$2500=INDIRECT(O$2))(B$3:B$2500=INDIRECT(N$2))(COUNTIF(O$3:O3,D$3:D$2500)=0)),1)),"")
P4:P15	P4	=IFERROR(INDEX(E$3:E$2500,AGGREGATE(15,6,ROW(E$3:E$2500)-ROW($C$2)/((D$3:D$2500=INDIRECT(P$2))(C$3:C$2500=INDIRECT(O$2))(B$3:B$2500=INDIRECT(N$2))*(COUNTIF(P$3:P3,E$3:E$2500)=0)),1)),"")

Cells with Data Validation
Cell	Allow	Criteria
G3,G8,G13	List	=INDIRECT($M$1)
R1	List	endereço,Address
I3:I5,I8:I10,I13:I15	List	=INDIRECT($N$1)
J3:J5,J8:J10,J13:J15	List	=INDIRECT($O$1)
K3:K5,K8:K10,K13:K15	List	=INDIRECT($P$1)

Lacan · Feb 21, 2020

Dear Toadstool,

First thank you very much - helped a lot.

Im now making the first tests - so far so great.

Last question - want to add another Area, Centre, City Name in G17 cell order.

In that case what which formulas do I have to to update and how so they can work all together?

Thanks again my friend.

Toadstool · Feb 21, 2020

Select G12 to K15, copy then paste to G17.

Lacan · Feb 23, 2020

Dear Toadstool,

Just copy to another sheet but doesnt work.

What Im doing wrong?

Thanks again pal.

How to make a Data Validation Dependent Drop Down List From the precedent Row?

Lacan

Board Regular

Lacan

Board Regular

Excel Facts

Akuini

Well-known Member

Toadstool

Well-known Member

Lacan

Board Regular

Attachments

Toadstool

Well-known Member

Lacan

Board Regular

Toadstool

Well-known Member

Lacan

Board Regular

Toadstool

Well-known Member

Lacan

Board Regular

Similar threads

Forum statistics

Share this page

How to make a Data Validation Dependent Drop Down List From the precedent Row?

Board Regular

Board Regular

Excel Facts

Well-known Member

Well-known Member

Board Regular

Attachments

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock