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

[Lacan]

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

 

[kweaver]

Read this well-written information: Creating a Dependent Drop Down List in Excel [Step-by-Step Tutorial]

 

[Lacan]

Read this well-written information: Creating a Dependent Drop Down List in Excel [Step-by-Step Tutorial]

Thanks for the suggestion.

However according with this data still can´t get the main goal.

I´ve saw some explanations videos but neither reach main goal.

Can you try for youserlf please?

Thanks again.

 

[kweaver]

This is a fair amount to describe in these windows on Mr. Excel.
However, I would suggest rereading the dependent drop down info in the link I sent earlier.
To get you started, I would insert a column like this (because name ranges cannot have spaces, etc.):

Then I'd create these columns and use name ranges (e.g., the three rows for Center 302 are named: : CNTR302)

Then I created various Data Validations, such as this one which is in J6:

Hope this helps.

 

[Lacan]

This is a fair amount to describe in these windows on Mr. Excel.
However, I would suggest rereading the dependent drop down info in the link I sent earlier.
To get you started, I would insert a column like this (because name ranges cannot have spaces, etc.):

Then I'd create these columns and use name ranges (e.g., the three rows for Center 302 are named: : CNTR302)

Then I created various Data Validations, such as this one which is in J6:

Hope this helps.

Thanks for your help.

Can´t see the images

 

[kweaver]

 

[Lacan]

Thanks for your help.

Can´t see the images

Dear friend,

First, thank you very much.
However I have a massive extent data that it will take a lot of work to creat range names through 7 columns and more than 2300 rows.

Is there a more easy quick simple way?

Thanks again.

 

[kweaver]

Unfortunately, not that I know of. Maybe someone else does.

 

[Lacan]

Unfortunately, not that I know of. Maybe someone else does.

Thank you very much pal.

 

[Akuini]

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

'=================================================================================================
'=============== 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