Can we refresh the Data Validation result

[c_rahul001]

I am facing a issue regarding Data Validation list. After selecting any item from the list can we reset the value of the cell of the list to blank after some time or after certain process by any formula because after item selection the value in cell remains as it is till we change the value from the drop down list manually.


Or is there any other procedure where we can apply two rules or formula's to one cell.That is can we apply data validation to one cell and also a formula to same cell.

Which means if a certain condition does not satisfy then should display Data validation Drop down list which we have applied or else should display blank in same cell.

 

[kaushik03]

Hi Rahul,


First of all, welcome to this forum. I am also new to this forum but will try to answer your question at my level best.


Assume in cell "A1 to A3" you have three parameters-Manager (at A1),HR (atA2), Analyst(at A3).These three parameters you want to place in data validation drop down list.

Now at cell B1 you have the letter "X".Now the criteria is, IF B1 = "X" or "Y" THEN you want all the three parametrs to populate in the drop down list ELSE drop down list would reset to BLANK.


For doing this,place the cursor in "D1"(where you want the drop down to be placed)and try this formula as the source for the drop down list:

=IF(OR(B1="X",B1="Y"),$A$1:$A$3,$A$4).....(considering "A4" is the blank cell)

The above formula tells excel that "give me all three parameters in the drop down list IF B1="X" or B1="Y" (assuming this is your condition), ELSE for any other value give me the BLANK

The only challenge with this approach is, once you change the value of B1 to anything other than "X" or "Y", nothing will be populated in the drop down list (which you want excel to execute) but you have to manually select the blank list(the blue colored filled area) to reset or remove the previos selected item. In short, the previously selected item will not be removed automatically, it will require manual intervention to make the cell blank.


However, your requirement can easily be fulfilled by the use of simple macro.Since you wanted this to be done in excel,I tried to give you the solution in excel only. But I really do not know how we can do it in excel to remove the previous drop down selection automatically. But I am sure, somebody here in this forum will be able to help you with the exact solution.


Regards,

Kaushik

 

[c_rahul001]

Hi Kaushik,


Thanks for the help, really appreciate the effort. Even I would like to welcome you in this forum. It was great help and can use macro for same so got the right solution. Will try using macro. Thank you. Have a great day ahead.

 

[kaushik03]

Hi Rahul,


Its good to hear from you.


Let me know in case you need any help w.r.t. macro in solving the issue.


You have a great weekend ahead.