If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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 news 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 news 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 news 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 news 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 news 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Could I get some query theory clarification? | Dennis Snelgrove | Running & Setting Up Queries | 3 | November 27th, 2004 11:13 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |
Data Range | Patrik Karlstrom | Worksheet Functions | 2 | October 4th, 2004 04:54 PM |
Formulas with named ranges with 2 or more range areas | agarwaldvk | Worksheet Functions | 1 | September 7th, 2004 07:33 AM |