Prev: Autofiltr w Tabeli z formułą w kolumnie funkcjonuje błędnie
Next: Copy sheet to different workbook sizing error message
From: Robert H on 20 Nov 2009 10:56
I’m trying to use the conditional formatting, traffic light feature
with dates and am finding the new Edit Formula Rule app to be rather
limiting. Hopefully it’s just my ignorance.

Cell E1 contains current date (updated when the workbook is opened)
several other cells have fixed expiration dates. In the expiration
date cells I want traffic lights indicating: red once the date matches
current, yellow seven days in advance, and green anytime the date in
the cell is greater than 7 days beyond the current date.

I can get red and green to work but getting yellow is a mystery.

Green value is set to “E1” and Type set to Number

Yellow says “when < formula and” then there is an option to select >
or “=>” and enter a value. In the value I have tried “=E1+7 just “=7”
etc but all I can accomplish is that yellow never works or will always
work when the date is exceeded with red never working.

I assume that yellows formula in “when < formula and” is referring to
the “value” for green even if number is selected for green instead of
“formula”. Makes me think that I could never create the condition I
want.

If I sound confused it’s because I am! The help feature does not
explain the feature that I can find
Any help will be appreciated.
Robert

From: Squeaky on 20 Nov 2009 12:51
Hi Robert,

Perhaps you need to change the order of preference within your conditional
formatting.

For my experiment I put =today() in cell a1

I then put 11/10/2009 in a2, and dragged it to k2 so it increments up to
today's date.

First, select all the cells you need to format, then go to conditional
formatting and create new rules.
I have the 3 following entries:

=a2<$a$1-7 Background is green

=a2=$a$1 Background is red

=a2<$a$1 Background is yellow

Make sure they in the order as above using the arrows. Apply.

Alter to fit your cells.

Squeaky


"Robert H" wrote:

> I'm trying to use the conditional formatting, traffic light feature
> with dates and am finding the new Edit Formula Rule app to be rather
> limiting. Hopefully it's just my ignorance.
>
> Cell E1 contains current date (updated when the workbook is opened)
> several other cells have fixed expiration dates. In the expiration
> date cells I want traffic lights indicating: red once the date matches
> current, yellow seven days in advance, and green anytime the date in
> the cell is greater than 7 days beyond the current date.
>
> I can get red and green to work but getting yellow is a mystery.
>
> Green value is set to “E1” and Type set to Number
>
> Yellow says “when < formula and” then there is an option to select >
> or “=>” and enter a value. In the value I have tried “=E1+7 just “=7”
> etc but all I can accomplish is that yellow never works or will always
> work when the date is exceeded with red never working.
>
> I assume that yellows formula in “when < formula and” is referring to
> the “value” for green even if number is selected for green instead of
> “formula”. Makes me think that I could never create the condition I
> want.
>
> If I sound confused it's because I am! The help feature does not
> explain the feature that I can find
> Any help will be appreciated.
> Robert
>
> .
>
From: Squeaky on 20 Nov 2009 13:09
Just an additional note:

If you change the order in conditional formatting, say, move the yellow to
the top of the list to give that one first preference, you will never get a
green background because the yellow formula overrides the green one
completely, whereas with the green formula on top it only overrides the
yellow when the dates are 7 days and older.


"Robert H" wrote:

> I'm trying to use the conditional formatting, traffic light feature
> with dates and am finding the new Edit Formula Rule app to be rather
> limiting. Hopefully it's just my ignorance.
>
> Cell E1 contains current date (updated when the workbook is opened)
> several other cells have fixed expiration dates. In the expiration
> date cells I want traffic lights indicating: red once the date matches
> current, yellow seven days in advance, and green anytime the date in
> the cell is greater than 7 days beyond the current date.
>
> I can get red and green to work but getting yellow is a mystery.
>
> Green value is set to “E1” and Type set to Number
>
> Yellow says “when < formula and” then there is an option to select >
> or “=>” and enter a value. In the value I have tried “=E1+7 just “=7”
> etc but all I can accomplish is that yellow never works or will always
> work when the date is exceeded with red never working.
>
> I assume that yellows formula in “when < formula and” is referring to
> the “value” for green even if number is selected for green instead of
> “formula”. Makes me think that I could never create the condition I
> want.
>
> If I sound confused it's because I am! The help feature does not
> explain the feature that I can find
> Any help will be appreciated.
> Robert
>
> .
>
From: Robert H on 20 Nov 2009 14:21

First, thanks for the response.
However, I dont think I can do what you are sugesting in the traffic
light ICON set.

There is no option that I can see to change the order of precidence in
a way that will place yellow at the top of the list, only revers the
order. yellow is always in the middle.

BTW I am using excel2007 getting to where Im with the following
steps: Home Tab, Style group, Conditional Formatting, Manage Rules,
New rule, Under format style select ICON sets, Under ICON style select
"3 Traffic Lights (Untrimmed)

Thanks
Robert

On Nov 20, 1:09 pm, Squeaky <Sque...(a)discussions.microsoft.com> wrote:
> Just an additional note:
>
> If you change the order in conditional formatting, say, move the yellow to
> the top of the list to give that one first preference, you will never get a
> green background because the yellow formula overrides the green one
> completely, whereas with the green formula on top it only overrides the
> yellow when the dates are 7 days and older.
>
>
>
> "Robert H" wrote:
> > I’m trying to use the conditional formatting, traffic light feature
> > with dates and am finding the new Edit Formula Rule app to be rather
> > limiting.  Hopefully it’s just my ignorance.
>
> >  Cell E1 contains current date (updated when the workbook is opened)
> > several other cells have fixed expiration dates.  In the expiration
> > date cells I want traffic lights indicating: red once the date matches
> > current, yellow seven days in advance, and green anytime the date in
> > the cell is greater than 7 days beyond the current date.
>
> > I can get red and green to work but getting yellow is a mystery.
>
> > Green value is set to “E1” and Type set to Number
>
> > Yellow says “when < formula and” then there is an option to select >
> > or “=>” and enter a value. In the value I have tried “=E1+7 just “=7”
> > etc but all I can accomplish is that yellow never works or will always
> > work when the date is exceeded with red never working.
>
> > I assume that yellows formula in “when < formula and” is referring to
> > the “value” for green even if number is selected for green instead of
> > “formula”. Makes me think that I could never create the condition I
> > want.
>
> > If I sound confused it’s because I am!  The help feature does not
> > explain the feature that I can find
> > Any help will be appreciated.
> > Robert
>
> > .- Hide quoted text -
>
> - Show quoted text -

From: Herbert Seidenberg on 20 Nov 2009 15:11
Excel 2007
Conditional Formatting, Icons
Red traffic light in the middle.
http://www.mediafire.com/file/ymqyqzjmvnn/11_20_09a.xlsx
 |  Next  |  Last
Pages: 1 2
Prev: Autofiltr w Tabeli z formułą w kolumnie funkcjonuje błędnie
Next: Copy sheet to different workbook sizing error message