FILTER and UNIQUE - dependent dropdown lists...

paulbiddis · Feb 5th 2021

Good evening,

Apologies for the poor subject heading... I don't really know how to phrase the problem

Attached is my working example.

What I am trying to do is to have dropdown lists (via data validation) change dependent on the initial list selection.

Using the example - I have already populated the first row in a table with the dropdown lists. B2 is a dropdown of country codes (listed in Col F). The dropdown list in C2 links to a filtered list (using the Filter and UNIQUE function) that returns those Regions (in tblLocation) that are in Country Code selected in B2 (which is GB).

The dropdown list in D2 links to a filtered list (using the Filter and UNIQUE function) that returns those Places listed (in tblLocation) that are in the Region selected in C2 (which is England).

This is great... but... I want, in the next row, to select different dropdown options. For example, I want to enter 'RU' in B3 and then select 'Southern Federal District’ (which would be one of the RU Region options if it worked). At the moment I cannot find a way of achieving this.

Any help greatly appreciated.

Thanks.

Kind regards,

Paul

**royUK** · Feb 6th 2021

Which version of Excel are you using?

paulbiddis · Feb 6th 2021

Hi Roy,

I am using excel for Mac under 365 subscription.

Cheers. Paul

**royUK** · Feb 6th 2021

In Excel for Windows it would be

=UNIQUE(FILTER(A1:A10,TRUE,""))

It would probably be the same in Mac, but I can't be sure.

paulbiddis · Feb 6th 2021

Hi Roy,

Sorry... but where would I put that formula?

Thanks.

**royUK** · Feb 6th 2021

It's only an example. Where is the data you want to use it on?

Can you attach a small example workbook.

paulbiddis · Feb 6th 2021

Hi Roy,

I attached it to the original post but for quick reference have re-attached. The original post explains what I am trying to achieve. As you will see I have already made use of the unique and filter functions but cannot find a way to make them flexible.

Many thanks.

Kind regards,

Paul

**royUK** · Feb 6th 2021

Can you use a cell with Data validation to refer to?

See the yellow cells

paulbiddis · Feb 6th 2021

Hi Roy,

I am not sure what you mean? I already have a look up cell (F2#). The system works for the first line of tblGPSLocation (A1:D60)... it is when I want to start populating rows below - i.e. B3,C3 and D3... then B4, C4 and D5, and so on. The problem is that the link to the lists are fixed by the validation so no matter what I enter in (e.g.) B3 I will always get GB regions (because C3 is linked to the original option in B2. Does that make sense?

Thanks.

Kind regards,

Paul

**Carim** · Feb 6th 2021

Hello Paul,

Regarding dependent lists, you can jump from Region to Country to Area to City ...

https://www.contextures.com/exceldatavaldependindextablesindirect.html

Hope this will help

**royUK** · Feb 6th 2021

Maybe this will help

Extract unique items for dynamic data validation drop down list

**Carim** · Feb 6th 2021

Hello Paul,

Based on your very first sample file ...

Attached is your test file... with your dependent validation lists ...

Hope this will help

paulbiddis · Feb 7th 2021

Roy/Carim,

Many thanks for your help with this one. I had hoped I would not have to have copious lists etc. but this, at least, does work.

Cheers and wishing you a pleasant Sunday.

Kind regards,

Paul

**Carim** · Feb 7th 2021

Pleased to hear your dependent lists are now working

Thanks a lot for your Thanks

paulbiddis · Feb 8th 2021

Good morning Carim, good morning Roy,

A question - is there any vba that will determine if the dependent list contains just one dropdown option/value and, if so, populate the cell with that one option/value bit, if not, then populate it with the dependent list. Using the example attached if I select GB (country) and then Wales in C2 only one value will be returned in D2. If, on the other hand, I select Northern Ireland in C2, multiple values are listed in D2. Rather than displaying a dropdown list where the list contains just one value is there any vba to recognise this single value and populate D2 accordingly - otherwise it will provide the (multiple value) dropdown list.

Many thanks.

Kind regards,

Paul

Ps. please let me know if I should post this in the vba forum - I have kept it here as you have background knowledge.

**royUK** · Feb 8th 2021

This will count the number of entries in a Validation List

Code

Sub CountValidationList()
    Dim lst As Variant
    Dim rCl As Range
    Dim str As String
    Dim iX As Integer, iCnt As Integer

    str = Range("B1").Validation.Formula1
    On Error GoTo exit_proc:
    If Left(str, 1) = "=" Then
        str = Right(str, Len(str) - 1)
        For Each rCl In Worksheets(Range(str).Parent.Name).Range(str).Cells
            iCnt = iCnt + 1
        Next rCl
    Else
        lst = Split(str, ",")
        For iX = 0 To UBound(lst)
             iCnt = iCnt + 1
        Next iX
    End If
    MsgBox iCnt
    Exit Sub
exit_proc:
    MsgBox "No validation list ", vbCritical, "Error"
End Sub

Display More

I think you would need to add the extra value directly to the source range, but this would probably mess up future uses of the drop down.

I'll see what I can come up with.

**Carim** · Feb 8th 2021

Hello Paul,

Quite a tricky question ... !!!

Attached is proposal ...

Let me have your comments ...

Hope this will help

**Carim** · Feb 8th 2021

Hello again,

Rather intrigued by your request ...

Attached is your somewhat ' improved ' Version 4 ...

Hope this will help

paulbiddis · Feb 9th 2021

Hi Chaps,

Many thanks. It works nicely

Paul

**Carim** · Feb 9th 2021

Thanks a lot for your Thanks

FILTER and UNIQUE - dependent dropdown lists...

Participate now!

Share