Conditional Formatting!!!

[Vikki]

I have a spreadsheet which uses conditional formattting. The colour of a
particular cell changes colour, depending on the expiry date (shown in
another cell).

I have another column with wording in which says either, Quotes; Business
Case; Tender; EU. At the moment, cell A7 turns amber, when the expiry date is
3-months away, to alert me to begin renewal of a contract, however, some
contracts require upto 12-months of preparatory work, so is it possible, for
the formula in A1 to take into account, the type of renewal (ie. Quotes;
tender etc).

For example, if a contract expires 01.01.11, and the renewal cell states EU,
then I want the formula to pick this up, and turn amber 12-months prior to
the expiry date, ie on 01.01.10.

Does this make sense and can anyone please help??

Many thanks
Vikki

 

[Fred Smith]

It would help if you showed the formula you were using.

In general, you do something like:
=or(and(a1="EU",a2<today()+365),and(a1<>"EU",a2<today()+91))

Regards,
Fred.

 

[RagDyeR]

Try something like this:

=OR(AND(B7<>"",B7<=TODAY()+90,C7<>"EU"),AND(B7<>"",B7<=TODAY()+360,C7="EU"))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I have a spreadsheet which uses conditional formattting. The colour of a
particular cell changes colour, depending on the expiry date (shown in
another cell).

I have another column with wording in which says either, Quotes; Business
Case; Tender; EU. At the moment, cell A7 turns amber, when the expiry date
is
3-months away, to alert me to begin renewal of a contract, however, some
contracts require upto 12-months of preparatory work, so is it possible, for
the formula in A1 to take into account, the type of renewal (ie. Quotes;
tender etc).

For example, if a contract expires 01.01.11, and the renewal cell states EU,
then I want the formula to pick this up, and turn amber 12-months prior to
the expiry date, ie on 01.01.10.

Does this make sense and can anyone please help??

Many thanks
Vikki

 

[Vikki]

Hi Fred

I am currently using 3 seperate conditions as follows:

TO TURN CELL GREEN =DATEDIF(TODAY(),$N$162,"M")>=3

TO TURN CELL ORANGE =AND(DATEDIF(TODAY(),$N$162,"M")<3,TODAY()<$N$162)

TO TURN CELL RED=TODAY()>=$N$162

I want to modify these so it is not always based on 3 months, I need it be
12-months if H162 says EU, 6-months if it says TENDER, 3 months for
QUOTATIONS & 3 months for BUSINESS CASE.

I hope you can help me get this right!

Thanks
Vikki

 

[Fred Smith]

Here's what I would do.

1. Eliminate the first condition, and set the cell to green background. This
is the default background if the other conditions aren't met
2. Change the orange condition to:
=datedif(today(),$N$162,"M")<if(H162="EU",12,if(H162="TENDER",6,3))
3. Leave the red condition as is.

Other things I would look at:
-- Review the use of absolute addressing ($N$162). When you copy this
formula, this address won't change. Is this what you want?
-- Add a column called, say, lead time, calculated as the Datedif in months.
Now you can substitute this column for my if statement in the orange
condition, and you can select on this number. The problem with conditional
formatting is that you visually have to search the file. You're often better
off to calculate the lead time, then use can use Autofilter to display only
the records you are interested in.

Regards,
Fred.