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.

List_currency	yes/no	String	yes/no	double
drop down with list of currencies	drop down with yes and no	no drop down	drop down with yes and no	no drop down

<tbody>
</tbody>

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.