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 21:12
Herbert, I was able adjust the formula in the indicator cell to match
my needs.
=IF([Exp]>Target+7,1,IF([Exp]<Target+1,-1,0))
Its a shame you have to manipulate the sheet so much to make it work.
But whatever it takes...
Thank You very much
Robert

On Nov 20, 3:11 pm, Herbert Seidenberg <herbds7-ms...(a)yahoo.com>
wrote:
> Excel 2007
> Conditional Formatting, Icons
> Red traffic light in the middle.http://www.mediafire.com/file/ymqyqzjmvnn/11_20_09a.xlsx

From: Squeaky on 23 Nov 2009 11:50
Hi Robert,

When you open the Conditional Formatting Rules Manager, In the line where
you see "New Rule, Edit Rule, Delete Rule, if you go to the right just a bit
more you will see some arrows. Highlight a rule, then use the arrows to move
that rule up or down to give it preference.

Squeaky.

"Robert H" wrote:

>
> 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 -
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Autofiltr w Tabeli z formułą w kolumnie funkcjonuje błędnie
Next: Copy sheet to different workbook sizing error message