Only allowing 1 selection from the data validation list in a range of cells

[danbates]

Hi,

My data validation list only has 1 entry = "YES"
Is it possible to only allow 1 selection from a data validation list that covers 3 cells?

So for example:

if I had cells A1:A3 with the list in and I selected A1 to have the "YES" is it possible then to have the validation list empty for cells A2:A3 and then if "YES" was deleted from cell A1, it would then become available to all 3 cells again.

I hope this kind of makes sense and I really appreciated the help.

Thanks

Dan

 

[Anthony47]

In a free cell, for example K1, insert the formula

=IF(COUNTIF(A1:A3,"Yes")=0,"Yes","")

Now set the validation: chose Allow from List; in the Origin box insert =K$1

Bye

 

[danbates]

Hi,

Works perfectly.

I was just wondering if there was a way to change the "YES" to CHAR(252) so it shows a tick instead?

Thanks again

Dan

 

[Anthony47]

CHR(252) is character ü; it is displayed as a tick by formtting the cell using Wingdings font.
My suggestion:
-format the cells using Wingdings font
-add a personalized format:
--select A1:A3, right click, chose Format cells
--in the Number tab chose Personalized
--in the Type box, insert;;;ü

That should do the job

Bye