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

danbates

Active Member
Joined
Oct 8, 2017
Messages
359
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,148
In a free cell, for example K1, insert the formula
Excel 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

Active Member
Joined
Oct 8, 2017
Messages
359
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 29, 2006
Messages
2,148
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,911
Messages
5,655,907
Members
418,250
Latest member
Jebacmakro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top