Dynamic Related Lists

    Hi there,


    I have a vehicle intake-form in which users have to enter vehicle data.


    I also have a huge list containing make/model information.


    I would like to be able to use this database in the following way : I want the user to select the make in cell A1 , and then the model in cell B1. It must be possible to choose for example the make "BMW" in cell A1 and then cell B1 should only display the BMW models, and not all others that are present in the list...


    I tried to use Data Validation with a list of choices, this works fine for the make (A1) ; but how do I make the list used for Data Validation on B1 dynamically related to the value in cell A1 !?


    Any help would be much appreciated.


    PoloB12

    Re: Dynamic Related Lists Combine 2 Lists


    Thanks, that worked pretty good, never used that before !


    I ended up with a new small problem though:


    My longest list is 318 rows and the shortest 2.... is there any way of supressing the empty cells in it ? It doesnt look real pretty now, as the list contains many emty values.


    Also, is there any way to set the list to show it's 1st value ?


    Thanks,
    PoloB12

    Re: Dynamic Related Lists


    Thanks Charlie,


    I will have a look at that tonight, makes things a little more complicated, but I will figure out a way to get the results I want with your additional info, it could be a possible solution.


    Will let you know if it worked for me !


    PoloB12

    Re: Dynamic Related Lists


    So... we're back and I've been trying to get this figured out but honestly this goes a little above my head.. I've looked at many examples and I still can't implement it in my file...


    The problem is ( I think ) that I use multiple lists, and I really don't know how to make all these lists dynamic and skip blank cells.. Actually, I even want the nr. of lists (columns) to be dynamic too so I can add a list without having to adapt everything each time.


    I attached an example file, I hope someone is able to help me out with this.


    Many thanks in advance.


    PoloB12

    Re: Dynamic Related Lists


    Quote

    I really don't know how to make all these lists dynamic and skip blank cells


    It's really not that difficult and the thread your were directed to explains it well.
    Change this static named range from: =Validation_List!$A$2:$A$318
    To a dynamic named range like this: =OFFSET(Validation_List!$A$2,0,0,COUNTA($A:$A),1)


    This must be done for each dependent list and the column reference adjusted accordingly.

    Quote

    Actually, I even want the nr. of lists (columns) to be dynamic too so I can add a list without having to adapt everything each time.


    You can create a dynamic named range for "Make" that spans across the columns and the list will update if you add another make, but you will have to manually create the dependent list for the new make.
    Change your current static range to this: =OFFSET(Validation_List!$A$1,0,0,1,COUNTA(Validation_List!$1:$1))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login