Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
McKenna
 
Posts: n/a
Default Allowing only one entry in a range

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.
  #2   Report Post  
JulieD
 
Posts: n/a
Default

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

"McKenna" wrote in message
...
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.



  #3   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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.

In article ,
"McKenna" wrote:

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.

  #4   Report Post  
Bernard Liengme
 
Posts: n/a
Default

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", "")
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"McKenna" wrote in message
...
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.



  #5   Report Post  
McKenna
 
Posts: n/a
Default

Many thanks for your help.

"JE McGimpsey" wrote:

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.

In article ,
"McKenna" wrote:

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.




  #6   Report Post  
McKenna
 
Posts: n/a
Default

Many thanks for taking the time to reply, much appreciated.

"JulieD" wrote:

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

"McKenna" wrote in message
...
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.




  #7   Report Post  
McKenna
 
Posts: n/a
Default

Many thanks for your reply, really appreciated.

"Bernard Liengme" wrote:

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", "")
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"McKenna" wrote in message
...
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.




  #8   Report Post  
JulieD
 
Posts: n/a
Default

Hi

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

Cheers
JulieD

"McKenna" wrote in message
...
Many thanks for taking the time to reply, much appreciated.

"JulieD" wrote:

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

"McKenna" wrote in message
...
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.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
create range bar graph Aussie1497 Charts and Charting in Excel 2 April 26th 23 11:47 AM
prevent duplicate entry for range of numbers 00001 to 99999 in ex. TK Excel Discussion (Misc queries) 4 March 6th 05 04:35 AM
Dynamic Print Range Help waxwing Excel Worksheet Functions 2 February 21st 05 04:47 PM
Define a range based on another named range Basil Excel Worksheet Functions 2 February 21st 05 02:47 PM
named range refers to: in a chart Spencer Hutton Excel Discussion (Misc queries) 1 December 14th 04 11:15 PM


All times are GMT +1. The time now is 12:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"