Reset Dropdown Lists based on Other inputs / cells

Hi all,

Trying to create a dynamic costing spreadsheet, where the user has to make multiple selections from many dropdown lists, all of which end up showing a total cost.

So far I have managed to create relatively dynamic lists, so that the choice in one cell will dictate what options are available in the next.

But if a user selects an options form all the dropdowns, and then decides to go back and modify a previous option to something else, the effected dropdown lists do not change until you click on the drop down arrow again and see the options available have changed.

Let me try and simplify...
'Menu 1' has two option: 'A' and 'B'.
If 'A' is chosen, then the next menu, 'Menu 2', will have options '1' and '2'.
If 'B' is chosen from 'Menu 1', then the options in 'Menu 2' will be '3' and '4'.
Lets say the user chose 'A' from 'Menu 1' and then '1' from 'Menu 2'... AND THEN went back and changed their choice in 'Menu 1' to 'B'....
'Menu 2' will still be displaying '1', even though the options are only '3' and '4'.

Can I make the dependant menu's either reset or blank or highlight to show that a change made somewhere else has made them currently invalid?

Hope that makes sense.

Thanks in advance.

Jon

Anyone able to offer any ideas on this?

Perhaps something to do with 'onevent' or something... sorry not very clued up on vba. But this spreadsheet is going to grow, so the ability to highlight/reset incompatible cells is important to avoid errors.

Thanks again.

Well for now I have solved it in a more basic way using conditional formatting on the menu cells to change colour (i.e. to red) when their value is not in the source list. Using the following formula:

Please Login or Register  to view this content.

So that simply checks if the current value is in my dynamic list (which is based on other menu choices) if not, i.e. =0, then it colours red, to highlight attention to non-valid cells.

I then add a second conditional format:

Please Login or Register  to view this content.

Which is set to highlight green, if the value is within the named range. Therefore valid.

Cheers.

.Perhaps this link can be of help? A sample workbook download is available.
Data Validation List Based on the Value of Another



Sting:

I tried using the following code, but it makes my workbook 'shudder'. Any ideas how to get that to go away?

Please Login or Register  to view this content.

Thank you for your reply. I will give this link a try on Monday!

Here's a sample created based on the link given above.
modytrane

Thank you for posting the sample.

Originally Posted by modytrane

Here's a sample created based on the link given above.
modytrane

I must be doing something wrong - your sample makes sense, but i can't get it to work for me. am i supposed to type something into the indirect formula in the quotes??

stupid mistake...i just needed to change the "A1" to fit my spreadsheet!! THANK YOU ALL!