Either cell but not both

[Mr X Z]

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

 

[joeu2004]

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.

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

 

[joeu2004]

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

 

[Mr X Z]

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

 

[David Biddulph]

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

 

[Mr X Z]

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