Disable options when other options have been selected - named ranges

[Jimmy202]

Hi

I kinda know how to do this with single cells but not with named ranges...everything I have googled so far seems to point to dependant or cascading lists but is not what I need....anyways

Basically

Say I have a drop down list in column A (in each cell) A drop down list in Column B (in each cell ) and the same in a few more columns.

Column A is headed say Cakes and their are 3 sub options in the drop list Cake type 1, cake type 2, cake type 3 and in Column B is headed Healthy Snacks with sub option healthy snack 1, healthy snack 2, healthy snack 3.

So I am asking people to select EITHER a cake OR a healthy snack but NOT both...

So I want if someone selects cake type 2 from a drop down list in column A then column B drop downs become disabled and vice versa (unless blank is selected then it becomes enabled again)...if that makes any sense

Thanks for any help...I know little about excel but can follow complex instruction ifnded.

Ta

David

 

[pgc01]

Hi David
Welcome to the board

You can just check if the other cell has a value.

For ex., if you have the cake options in F2:F4, you can use in A2:

=IF(B2="",$F$2:$F$4,"")

If B2 has a value the data validation dropdown is disabled, else it displays the options.

Copy the validation down column A

Similar to column B.

 

[Jimmy202]

Hiya - thanks for the response don't know if I was entirely clear but to simplify...both cells have drop down lists if something is selected in first cell drop down I need second cell drop down disabled and vice versa...will your solution still work?

Ta

David

 

[pgc01]

Yes, you were clear.

Did you have any problems with the solution?
Which ones?
Please post all the data relevant (where and which is the value of your data, what triggers errors and which are the messages, etc.)