FILTER and UNIQUE - dependent dropdown lists...

  • 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

  • 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

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


    See the yellow cells

  • 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

  • Hello Paul,


    Based on your very first sample file ...


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


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim

    Changed the title of the thread from “FILETR and UNIQUE - dependent dropdown lists...” to “FILTER and UNIQUE - dependent dropdown lists...”.
  • 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:)

  • Pleased to hear your dependent lists are now working :)


    Thanks a lot for your Thanks;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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.

  • This will count the number of entries in a Validation List


    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.

  • Hello Paul,


    Quite a tricky question ... !!!


    Attached is proposal ...;)


    Let me have your comments ...


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    Rather intrigued by your request ...;)


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


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)