Either cell but not both
Hello
My boss has set me a couple of problems and unfortunately my knowledge of Excel is lacking. So any one who can bail me out....... 1. Is it possible to restrict an entry to either one of two cells but not both? I had thought Conditional formatting but having searched around the net have not found an answer. Maybe there isn't one. 2. On the subject of conditional formatting, if B2 had a date in it what would the formula in C2 be for the cell to turn red after say 100 days from the date in B2? Forgive me if this is something easily done...... Many thanks for reading this though Mungo |
Either cell but not both
On Jun 8, 3:23*pm, Mr X Z wrote:
1. Is it possible to restrict an entry to either one of two cells but not both? I had thought Conditional formatting It depends on what you mean by "restrict". With Conditional Formatting, you can highlight the error, but you cannot prevent ("restrict") it. For example, if only one of B1 and B2 should be non-empty, select B1 and B2, and enter the following CF for both: Formula Is: =AND(ISBLANK($B$1)=FALSE,ISBLANK($B$2)=FALSE) and select an appropriate Pattern from Format. Alternatively, you can use Data Validation to actually prevent ("restrict") the use of both cells. For exmample, if only one of A1 and A2 should be non-empty, select A1 and A2, and enter the following DV for both: Allow: Custom Formula: =OR(IBLANK($A$1),ISBLANK($A$2)) Customize the Input and Error tabs if you wish. Mungo wrote: 2. On the subject of conditional formatting, if B2 had a date in it what would the formula in C2 be for the cell to turn red after say 100 days from the date in B2? Cell Value Is: Greater Than: =B2+100 |
Either cell but not both
On Jun 8, 3:56*pm, joeu2004 wrote:
With Conditional Formatting, you can highlight the error [...]. For example, if only one of B1 and B2 should be non-empty, select B1 and B2, and enter the following CF for both: Formula Is: *=AND(ISBLANK($B$1)=FALSE,ISBLANK($B$2)=FALSE) Arguably easier to write and equivalent: Formula Is: =OR(ISBLANK($B$1),ISBLANK($B$2))=FALSE |
Either cell but not both
Hi Joeu 2004
Many thanks for your response, am extremely grateful to you for taking the time to help me. The CF formulas worked like a charm but with the DV I got an error message - "A named range you specified cannot be found". Being next to useless I can't work out why it doesnt work. Can you please check it out? Cheers Mungo |
Either cell but not both
On 10/06/2011 00:41, Mr X Z wrote:
Hi Joeu 2004 Many thanks for your response, am extremely grateful to you for taking the time to help me. The CF formulas worked like a charm but with the DV I got an error message - "A named range you specified cannot be found". Being next to useless I can't work out why it doesnt work. Can you please check it out? Cheers Mungo You didn't quote the formula you were trying to use, but if it was: Allow: Custom Formula: =OR(IBLANK($A$1),ISBLANK($A$2)) there is a typo, and it ought to have been: Allow: Custom Formula: =OR(ISBLANK($A$1),ISBLANK($A$2)) David Biddulph |
Either cell but not both
On Jun 10, 4:42*am, David Biddulph wrote:
On 10/06/2011 00:41, Mr X Z wrote: Hi Joeu 2004 Many thanks for your response, am extremely grateful to you for taking the time to help me. The CF formulas worked like a charm but with the DV I got an error message - "A named range you specified cannot be found". Being next to useless I can't work out why it doesnt work. Can you please check it out? Cheers Mungo You didn't quote the formula you were trying to use, but if it was: Allow: Custom * *Formula: =OR(IBLANK($A$1),ISBLANK($A$2)) * there is a typo, and it ought to have been: Allow: Custom * *Formula: =OR(ISBLANK($A$1),ISBLANK($A$2)) David Biddulph Hi David It was indeed the one you have corrected. Thank you and have a great weekend. Mungo |
All times are GMT +1. The time now is 12:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com