Conditional drop down menus

[NecRock]

Hi,

I have an excel file in which cells have drop down menus, created by data validation. I know how to make the lists dynamic, meaning I can change the list behind the drop down menu depending on circumstances. However, I want to make the whole presence of a drop down menu / data validation dynamic: depending on circumstances, I sometimes DON'T want there to be a drop down menu at all.

To make the issue a bit more concrete:

In row 1, I have data types, then in row 2 and beyond I want to enter values depending on these data types.
[TABLE="width: 500"]
<tbody>[TR]
[TD]List_currency[/TD]
[TD]yes/no[/TD]
[TD]String[/TD]
[TD]yes/no[/TD]
[TD]double[/TD]
[/TR]
[TR]
[TD]drop down with list of currencies[/TD]
[TD]drop down with yes and no[/TD]
[TD]no drop down[/TD]
[TD]drop down with yes and no[/TD]
[TD]no drop down[/TD]
[/TR]
</tbody>[/TABLE]

I am currently creating these drop downs with a list validation, with a formula as source (I don't think the specific formula matters, but in case it does: OFFSET(lists!$A$1;2;MATCH(A$1;lists!$A$1:$DI$1;0)-1;OFFSET(lists!$A$1;1;MATCH(A$1;lists!$A$1:$DI$1;0)-1;1;1);1)). However, I can't find a way to change the setting to use a drop down dynamically; nor something that allows me to switch off validation altogether in a dynamic fashion. With custom validation, I could split up between those data types that have a list attached and those that do no, but it does not seem to allow the drop down option at all.

I was looking for a way to do this without VBA code, but can't find a way. Is it impossible and should I either give up on the idea or start using some VBA, or are there possibilities yet unexplored?

 

[Worf]

Hi and welcome to the Board

I would do it with VBA which gives you a lot of flexibility. Post back if you accept the idea and need help with the coding.