Dependent data validation

[Dani_R]

Hi All,

I am new to this forum and hoping to get some help. I know this has been asked before however I am looking to do something without having to use VBA or macros.
I currently have 2 columns (Column D and Column E), column D contains a data validation list of Cities and column E is a dependent data validation list that then provides a list of buildings (that my company has offices in) depending on what city is selected. I understand that in order to reset column E if column D is changed I need a VBA code, but im wondering if there is a way to create an error message instead that looks up from a table whether what is in column E matches what is in column D i.e. if city chosen is Sydney (column D) then Column E can only be Building A, Building B or Building C, if city chosen is Melbourne then Column E can only be Building D, Building E or Building F.

Does that make sense? And is that possible? Or is VBA my only option?

Thanks so much in advance!

 

[mfexcel]

check this out:
http://blog.contextures.com/archives/2013/10/10/dependent-drop-down-list-from-a-row/

Hi All,

I am new to this forum and hoping to get some help. I know this has been asked before however I am looking to do something without having to use VBA or macros.
I currently have 2 columns (Column D and Column E), column D contains a data validation list of Cities and column E is a dependent data validation list that then provides a list of buildings (that my company has offices in) depending on what city is selected. I understand that in order to reset column E if column D is changed I need a VBA code, but im wondering if there is a way to create an error message instead that looks up from a table whether what is in column E matches what is in column D i.e. if city chosen is Sydney (column D) then Column E can only be Building A, Building B or Building C, if city chosen is Melbourne then Column E can only be Building D, Building E or Building F.

Does that make sense? And is that possible? Or is VBA my only option?

Thanks so much in advance!

 

[Dani_R]

check this out:
http://blog.contextures.com/archives/2013/10/10/dependent-drop-down-list-from-a-row/

Thanks for the link but this only seems to create the dependent data validation (which I have already done), it doesnt help me create the error if for example BUilding D is selected which only relates to Melbourne, but then the city is changed to Sydney, BUilding D still remains in column E unless the user goes into the drop down and updates it. Therefore it is possible to have incorrect data in column E.

Is my only options to use VBA to reset the cell in column E if the coresponding cell in column D is changed?

Thanks

 

[mfexcel]

Thanks for the link but this only seems to create the dependent data validation (which I have already done), it doesnt help me create the error if for example BUilding D is selected which only relates to Melbourne, but then the city is changed to Sydney, BUilding D still remains in column E unless the user goes into the drop down and updates it. Therefore it is possible to have incorrect data in column E.

Is my only options to use VBA to reset the cell in column E if the coresponding cell in column D is changed?

Thanks

Search a bit in the contexture blog. There is a way to actually prohibit previous column from changing.
http://www.contextures.com/xlDataVal02.html

or this...
http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

 

[Dani_R]

Search a bit in the contexture blog. There is a way to actually prohibit previous column from changing.
http://www.contextures.com/xlDataVal02.html

or this...
Robust Dynamic (Cascading) Dropdowns Without VBA | Chandoo.org - Learn Microsoft Excel Online

Thats great! Thank you so much for your help

 

[mfexcel]

You are welcome. Glad it helps.
btw, the compliments should go to Contexture Blog where you find various useful and great Excel Tips.

Thats great! Thank you so much for your help

 

[pareshj]

You can do this by VBA also, but better option to do is by creating dependent drop down list in excel. Following is the example of how to create the same.

Check out the below link and follow the steps,


Create Dependent Drop-down lists in excel | S&P Infotech Blog