Allowing only 1 cell input in a cell range of 5

  • Thread starter Thread starter AMM
  • Start date Start date
A

AMM

How do I set up 5 separate cells to only allow entry into
1 of those cells?

For example, in Column A, rows 1 -5, I want to be able to
input an "X" into only one row, such as row 3. If I try
to input into row 1, 2, 4, or 5, I want a message to pop-
up saying that is not allowed.
 
Select A1:A5, do Data / Validation / Custom / Put in the Formula:-

=COUNTA($A$1:$A$5)<=1

Hit OK.
 
one way:

Select A1:A5. Choose Data/Validation.

Allow: Custom
Formula: =COUNTA($A$1:$A$5)<=1

You can input your own message in the Error Alert tab of the
Validation dialog.
 
If I enter that formula below. I get the error: "The
value you entered is not valid. A user has restricted
values that can be entered into this cell."

User needs to be able to select 1 out of the 5 cells to
enter "x". Once 1 of the 5 cells has an "x", the user can
not enter any more "x"s for that range.

For example, range is A14:A18.
User must put an "x" in one of those cells and only one
of those cells.

How can I do that??
 
-----Original Message-----
How do I set up 5 separate cells to only allow entry into
1 of those cells?

For example, in Column A, rows 1 -5, I want to be able to
input an "X" into only one row, such as row 3. If I try
to input into row 1, 2, 4, or 5, I want a message to pop-
up saying that is not allowed.


.
Use your validation option under data. It allows you to
Click to expand...
restrict entry and leave a message for your user.
 
Exactly as both JE and I have stated. You must have deviated somewhere. I'll happily
send you a small sample sheet to show you it working if you like. You would just need to
select A1:A5 and do data / validation to see the formula.
 
Adjust the formula to read

=COUNTA($A$14:$A$18)<=1

this allows only one entry in A14:A18.

Note that a space character counts as an entry.

You can change the error message in the Error Alert tab of the
Validation dialog.
 
I am unfamiliar with the "validation" option.

Is there a specific type of formula I need to use??
 
Ken - if you could send me a small sample. I have tried
this over and over. Now, keep in mind, that I am inputing
an alpha character into one of those cells. Should I be
entering a numeric character??

Thank you!!
(e-mail address removed)

-----Original Message-----
Exactly as both JE and I have stated. You must have
Click to expand...
deviated somewhere. I'll happily
send you a small sample sheet to show you it working if
Click to expand...
you like. You would just need to
 
Back
Top