Either cell but not both

  • Thread starter Thread starter Mr X Z
  • Start date Start date
M

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
 
1. Is it possible to restrict an entry to either one
of two cells but not both?
I had thought Conditional formatting
Click to expand...

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?
Click to expand...

Cell Value Is: Greater Than: =B2+100
 
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)
Click to expand...

Arguably easier to write and equivalent:

Formula Is: =OR(ISBLANK($B$1),ISBLANK($B$2))=FALSE
 
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
 
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
Click to expand...

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
 
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
Click to expand...

Hi David
It was indeed the one you have corrected.
Thank you and have a great weekend.

Mungo
 
Back
Top