Context sensitive lists using Data Validation

[Manosh]

Hello everyone.
I am trying to build a context sensitive list, hopefully without making a macro file.
In my first sheet, Assumptions, i have a list of Typologies (and this can extend, but will be a named range), for which they fall into a range of Land use (this may increase too).

On my second page, User Input, i'd like the user to select first the Land use (as this is a shorter list) and then within that the typology.

Based on the selction i can call back a series of plot related data, in this case the typical plot data.

I'd like the data validation (if this is the way to do it) to be dynamic to that it can pick up changes to both the lists.

Any ideas? (I tried searching on chandoo but i may be looking with the wrong key words (context sensitive list...?)

 

[Nebu]

Hi:

You may want to go through the following link
http://www.contextures.com/xlDataVal02.html
Thanks

 

[Manosh]

Hi:

You may want to go through the following link
http://www.contextures.com/xlDataVal02.html
Thanks

Thanks Nebu,
I've seen this and i have a question. If the information in the assumption sheet keeps extending in the format shown on my excel, how does the list get formed?
Would i have to copy this list across to another sheet and reorganise the data every time?

 

[Nebu]

Hi:

Use dynamic named ranges.

Thanks

 

[Manosh]

Thanks Nebu.
I've looked at this but i am not sure if it addresses my request entirely.
I am trying to get a list of Landuses that is distinct, and given that the only source at the moment is the table would i need to manually create the lists and update them each time?
Is there a way of creating a these dynamic lists from a non sorted table?

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[Manosh]

Hi:

Find the attached.

Thanks

Thanks Nebu.
This works when the main list is static, however if someone is adding to or subtracting to this list - for example if a new typology called Court and a new landuse called Government is added - the separate list from where the ranges are create would need to be manually updated.
Is there any way that the lists and named ranges themselves can be updated automatically?

 

[Nebu]

Hi:

Ideally you will have to add the new entries from columns I to P, Columns A,B & C are used only for looking up plot area, the drop downs are not depended on them.

Thanks

 

[Manosh]

Hi:

Ideally you will have to add the new entries from columns I to P, Columns A,B & C are used only for looking up plot area, the drop downs are not depended on them.

Thanks

Ok thanks!

 

[jeffreyweir]

@Manosh Here's a guest post I wrote some time back on the Chandoo forum where the named ranges are updated automatically as per your comment above.
http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

 

[Manosh]

@Manosh Here's a guest post I wrote some time back on the Chandoo forum where the named ranges are updated automatically as per your comment above.
http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

Thanks Jeff!
This looks very good, however my challenge is how my main list / sub list is organised.
I need to present it in a tabular format with entries that may be added/ deleted and with it some associated information (see the snapshot in excel attached earlier.. In addtion to what has been presented i will have to add more data like car parking rates, power consumption rates etc.
I want to avoid having to maintaining two lists esp as more than one group may be working on the sheet and could add data.
Any ideas on how to tackle this? I thought maybe creating a pivot and somehow reading of the main heading and sub headings might be a workaround but have no idea how to do this...