Find and replace in drop down list

Talahjer - Jan 25, 2011 at 04:04 PM
 talahjer - Jan 28, 2011 at 05:33 PM

Column A Column D
Dog animals are nice Nice
cat animals are nice Nice
sheep not nice Not nice

Imagine I have this excel sheet and have Column D which is a drop down list that allows me to choose within its range; however, this example contains only two possible entries nice and not nice.

There is a formula =IF(ISERROR(SEARCH("*nice*",A:A,1)),"","A") that allows me to search in A and if there is a nice word it reflects it into D. is it possible to have the same function working in D despite of the fact it is a drop down list. Because it gives me an error saying restricted values can be entered into cell.

And how can I upgrade the search and place formula to look for more than one possible answer. For example search for nice, not nice, in between and more possible words.

many thankx in advance

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 27, 2011 at 09:05 AM
Could you please explian you problem again.
My regions and Districts are as follow:
Northern: Sadah, Amran, Al-Jawf, Marib, Hajjah and Al-Hudaydah
Central: Sanaa, Dhamar, Ibb, Raymah, Al-Bayda, Al-Mahwit and Taiz
Southern: Aden, Abyan, Dhale, Lahij, Shabwah, Hadramaut and Al-Mahrah

In the H:H i write the details of some incidents which come like this:
On 19 Jan 11, Sadah Governorate, Al-Hada District, etc.............
In C:C comes the Region, according to the above-mentioned incident, C1 should be Northern, because if you see that Sadah Governorate comes under the Northern region.

Let's imagine that I wrote this incident in H2:
On 19 Jan 11, Aden Governorate, Kreator District, etc.............
Then under C2, Northern should be written automatically.

i hope you understand me now.

thank you very much in advance.

once you create a datavalidation, and chosse a list, then it is impossible to add anything but what is in the list.
i tried to add a formula in a cell that contains a datavalidation but excel refused to allow me to enter the formula. is there a way that allow me to add a formula to a cell which is a list of datavalidation.