Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create range bar graph | Charts and Charting in Excel | |||
prevent duplicate entry for range of numbers 00001 to 99999 in ex. | Excel Discussion (Misc queries) | |||
Dynamic Print Range Help | Excel Worksheet Functions | |||
Define a range based on another named range | Excel Worksheet Functions | |||
named range refers to: in a chart | Excel Discussion (Misc queries) |