Add or remove a data validation list based on a cell value

[yunusdoc]

Hi, I have a cell, say a1 which has the value "Validate". In cell a2 I want to include a data validation list ("yes,no") which is have named as Decision. Is there a way to include a drop down list only when cell a1="validate" and no list when a1 is blank. I tried IF(a1="Validate",Decision,""). This gives the yes,no option in a2 if a1=validate,and I am able to choose either yes or no. However my problem comes in when a1 changes to an empty cell, the last selected value remains in a2. is there a way when a1 is empty (ie blank) a2 is empty as well without any data validation.


Tks

 

[nazmul_muneer]

yunusdoc,

Welcome to Chandoo's Forum.

For example, In H1 Type Yes and in H2 type No

1.Select H1:H2 and name it YN

2. Select B1

3. Go to Data validation>List under Allow>type =IF(A1="Validate",YN,"") in source

4. Ok

Done!


--Muneer

 

[yunusdoc]

Hi Muneer,


Thanks for the reply. This is exactly what I have done - what I want is that if A1 is blank no list should be returned. The way you and I have it, the last selected value Yes or No remains in the cell when A1 reverts to a balsnk cell.


Thanks


Yunus

 

[nazmul_muneer]

Oooh! we have solved the problem


Muneer

 

[sdsurzh]

Hi,


Please download the below filw:


http://www.2shared.com/document/Vp3mXo_R/Book2.html


Thanks,

Suresh Kumar S

 

[sdsurzh]

Hi Yunusdoc,


If you Select the dropdown from Range "A3" (Validate or blank) for that corresponding the value will reflect in Range "B3". For Validate only Yes or No dropdown available in Range "B3" at the same time for Blank you will not able to select values from dropdown.


If already some value (Yes or No) you have del that value if you are selecting blank option in "A3".


Thanks,

Suresh Kumar S

 

[yunusdoc]

Hi Suresh,


In your last statement, therin lies the problem. What I am builing is a decision tree for use as a template. I do not want the user to have to physically delete (Yes or No)as these values keep the rest of the decision tree open. What I am aiming to get is that if the first cell "validate" is left blank, then the remainder of the decision tree is blank. Similarly along any step of the decision tree, if the "validate" cell is blank, an answer is returned and the rest of the decision steps become blank cells.


Hope I am making sense.


Thanks


Yunus

 

[yunusdoc]

Hi Suresh,


I have changed to a list box which provides for the cell link value. I have used to cell link value to swith on and off my decision tree. Although this is not the most complete solution, it is more elegant then trying to use data validation lists.


Thanks


Yunus