Blocks a second cell to have a value if the first has one already

[AA Arens]

In a range of cells (a row), I need a data validation that block a
second cell to be filled with a value. How to do that?

Sample:

A B C D
1 X

If Cell C1 has value X, another cell cannot have a velue, unless value
in C1 is deleted.

Bart
Excel 2003

 

[Earl Kiosterud]

AA,

Presumably, your X is a variable -- the first cell can have any value, and a second cell
should be blocked. Data - Validation - Custom:

=COUNTIF($A$1:$D$1,"*")<2

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[AA Arens]

Hi earl,

I am still allowed to have another cell filled, so far this cell come
after (right of) the first cell.
Make it possible that only one cell can be filled in from a row-array.

With x I meaned only x can be filled in, no other value.

Thank you for your help.

Bart

\

 

[Earl Kiosterud]

Bart,

Now you're saying that the cells must be filled left-to-right, starting with A1, and can
only be filled with "x". If that's the case, use this in the Data - Validation for cell A1:

=A1="x"

And use this in B11:

=AND(A1="x",B1="x")

Note that when you select B11, the active (white) cell of your selection must be B1. This
will allow either X or x. These will allow either x or X.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

 

[AA Arens]

Earl,

When I mentioned in my first posting is what I mean.

In a range of cells A1-E1 I am only allowed to fill one cell with x
(and only x or X).

Type from left to right: (X -> X etc.)
In your first solution, I was still able to fill another cell with x
if a cell left of the active cell has value x as well.

Typing from right to left: (X <- X etc.)
Your first solution worked, I could not fill in x if a cell right of
it has already value x


And: No other character should be allowed to be filled in at all.

Bart

 

[T. Valko]

In a range of cells A1-E1 I am only allowed to fill one cell with x

Try this:

Select the range A1:E1
Goto Data>Validation
Allow: Custom
Formula: =AND(A1="x",COUNTIF($A1:$E1,"x")<2)
OK

I'm assuming you want to allow "x" or "X" and *nothing else*.

Biff

 

[AA Arens]

Hi Biff,

Works partly. I am not allowed to add one to the left of the cell with
value x, but I am still able to add one to the right one cell with
value x.

Same result as the offer from Earl.

Bart

 

[Earl Kiosterud]

Bart,

You need to describe the problem more accurately. We can't determine if what you're saying
is what you want, or what is happening that you DON'T want. We're not struggling with a
solution -- we're struggling to understand the problem.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

 

[T. Valko]

I think I may understand what they want.

Assume the range is A1:E1

If they enter X in A1 then allow only one other X in B1:E1
If they enter X in B1 then allow only one other X in C1:E1 and do not allow
X in A1

Once the first X is entered don't allow another X to the left of that first
X.

I don't think it can be done *exactly* the way they want but you can
restrict X to no more than 2 cells in the range:

=AND(OR(A1="",A1="x"),COUNTIF($A1:$E1,"x")<=2)

Biff

 

[AA Arens]

Hi All,

I thought I was clear enough, but will explain again.

I have a range of cells A1-E1
I am only allowed to fill one of these cells with "x". If i try to
fill another cell with x, then I get the message (from data
validation): "Only value x is allowed or another cell has already a
value x. Clear the other cell first". And..., only value x is allowed,
so not other value, even when all cells are still empty.

So:
Allowed
ABCDE
x
x
x

Not allowed
ABCDE
y
x x
x z


Bart

 

[Roger Govier]

HI

Maybe
=AND(COUNTIF(A1:E1,CHAR(120))<=1,COUNTIF(A1:E1,"<>")=COUNTIF(A1:E1,CHAR(120)))

 

[AA Arens]

This formula still make it possible to have different characters
filled in more cells. More cells with only X is however not possible.
A solution will be very helpful for several users of excel, I think.


Bart

 

[Roger Govier]

Hi
Can you give me an example?
I tried it and couldn't enter other characters into the range A1:E1

 

[AA Arens]

You should still be able to type from left to right

A B C D E

x -> x -> x -> x

From right to left works well, e.g. I cannot fill two ore more cells

with x

A B C D E

x <- x <- x <- x