Validation with criteria

  • Thread starter Thread starter KrisB_bacon
  • Start date Start date
K

KrisB_bacon

I am validating some cells that will contain either Yes or No. Is ther
a way the cells will only allow Yes or No. At present all I can limi
is the length of the text.

Thx

KrisB_baco
 
Hey again,

I'm aware of the list option, but I would prefer it if an error messag
was displayed if Yes or No wasn't entered, without any list box option
I want the user just to type in Yes or No, not select it from a box. I
this possible?

KrisB_baco
 
hi kris,

find a couple of blank cells some where on your worksheet where the
won't interfere with anything...way off to the side or bottom.

for this reply, i will use cell A1 and A2.

in cell A1 input: yes
in cell A2 input: no

next, select the cell which you want to use the yes/no validation
select Data>Validation. in the "allow" dialog box, select "list.

a "source" dialog box now appears. click the red, white and blue ico
at the boxes right side.

the dialog box will be relplace with another. left click and hold dow
on cell A1 on your worksheet, then drag down to cell A2. you will se
a marching line around your selection.

now click on the red, white and blue icon at the right of the dialo
box that appears on your screen and you will be taken back to th
original data validation dialog box, but cells a1 and a2 will be liste
in the source box. make sure the "in cell drop down" option i
selected.

click ok, the dialog box disappears. in your worksheet click in th
cell where you put the validation and a drop down arrow will appear.
click the arrow and yes and no options will appear.

with that cell again selected, go back to data validation and click o
the other two tabs to see what they contain. you can customize you
cell validation as you see fit...allow only values from list...allo
other values with a warning...display text if the cursor is moved ove
the cell. it's quite intuitive.

if you have lots of lists that will be used for cell validation,
recommend creating a new worksheet called lists and putting all you
lists there, and them name them. for example, you can name the rang
of cells A1:A2 "yes_no" then in the source box just type in: =yes_no
naming makes things much easier to keep track of.

good luck

kare
 
the list box option will do this, if you use the stop error alert. the
user can enter yes or no manually or pick from the list box. so if
they enter "xyz" manually, they will get an error message.
 
Thx Karen

Sorry, I didn't say, I already know about the error message boxes.
just want to know, WITHOUT USING A LIST BOX, if I can only allow Yes o
No in the cell.


KrisB_baco
 
Hi
choose 'custom' and enter the following formula in the data validation
dialog:
=OR(A1="Yes",A1="No")
if A1 is the cell for your validation
 
Hi KrisB

Yes uncheck <in cell-dropdown> in Data>Validation
You can fill in error text there also
 
You can use the Allow List option, and remove the check mark from
'In-cell dropdown'. Note -- if you type the values in the List box, the
data validation is case sensitive.

Or, choose Allow:Custom
In the Formula box, use an OR formula, and refer to the active cell:
=OR(C6="Yes",C6="No")
 
Back
Top