Allowing only one entry in a range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have just come back to excel after a couple of year away, and fustrated
that I can't remember how to do things!

I have three cells C6(provisional),D6(confirmed),E6(rejected), I want to be
able to input a value to one cell, but only if the other two are blank. A
user will need to change the value according to the progression of the order,
but I want the safeguard of not having a value in more than one cell. I've
tried an IF statement, and a IF AND statement, but still can't get it. I
would welcome any help.
 
Hi

one option is to use conditional formatting that makes the three cells go
red or something if data is entered into more than one of them ... select
the cells and choose format / conditional formatting from the menu
choose formula is
type
=COUNTIF($C6:$E6,"<>"&"")>1
click on format and i would personally set a pattern (fill colour, e.g. red)
click OK twice
and test

Cheers
JulieD
 
one way:

Select C6:E6. Choose Data/Validation. Choose Custom from the dropdown,
and enter

=COUNTA($C$6:$E$6)<=1

in the textbox. Enter a prompt/error message, or just click OK.
 
You cannot have formulas in these cells since you want users to input data.
Suggestion: in another cell (F6) use =IF(COUNTA(C6:E6)>1,"Error", "")
 
Many thanks for your help.

JE McGimpsey said:
one way:

Select C6:E6. Choose Data/Validation. Choose Custom from the dropdown,
and enter

=COUNTA($C$6:$E$6)<=1

in the textbox. Enter a prompt/error message, or just click OK.
Click to expand...
 
Many thanks for your reply, really appreciated.

Bernard Liengme said:
You cannot have formulas in these cells since you want users to input data.
Suggestion: in another cell (F6) use =IF(COUNTA(C6:E6)>1,"Error", "")
Click to expand...
 
Hi

just interested in which suggestion you decided to go with as you ended up
with three quite different approaches.

Cheers
JulieD
 
Back
Top