Conditional formatting

[ellinor2304]

I use Excel 2007. I have a spreadsheet of sales. Each salesman has three
rows assigned: sales, payments and commissions. These are listed under the
months of the trade year, April to March as a heading row. I don't want to
pay monthly commission until the salesman has submitted the payments for the
particular month. I have a separate section showing what payments are due.
So what I want is to change the colour of the box in the separate section, so
that if the payment has not been received in a particular month, the box is
one colour, if the payment is not received it is a different colour. That
way I will be alerted not to pay commission if no payment has been received.
But it is more complicated than that, as I want it to update automatically.
So for example cell S23 would have to have something like: in the row C2 to
N2, take the cell that is in the column for the current month and if that
cell >0, color S23 green; if not, color S23 red.
Is this possible with conditional formatting? If not, is there a way I
could do it?
I have several salesman to do this for.

 

[Max]

.. So for example cell S23 would have to have something like:
in the row C2 to N2, take the cell that is in the column
for the current month and if that cell >0,
color S23 green; if not, color S23 red.
Is this possible with conditional formatting? ..

Re above description, you could try this

Assume C1:N1 contains the text: April, May, ... March
(ie the 12 calendar months in "mmmm" format)

First, just normally format S23 with red fill (via Format>Cells)

Then select S23, apply conditional formatting using Formula Is:
=OFFSET(B2,,MATCH(TEXT(TODAY(),"mmmm"),$C$1:$N$1,0))>0
Format > Green fill > Ok out

The above will return the required CF for S23. If cell K2 -- ie the cell
within C2:N2 just below the label "December", the current month -- contains a
value other than zero, then S23 will be filled green as desired. Otherwise,
the normal format applied, ie red, will display.

 

[Max]

Line:

.. contains a value other than zero

should have read as:

 

[T. Valko]

I use Excel 2007.
There is no Formula Is option in Excel 2007. It's been renamed to make it
easier to understand!

From a post by Niek Otten:

On the Home tab, choose Conditional Formatting>Use a formula to determine
which cells to format>Format values where this formula is true

I don't have Excel 2007 but I trust Niek!

 

[ellinor2304]

Thanks all, so, so helpful. Awesome.

So what formula should I use?

What I am trying is some thing like this:

(SUM(D2:O2)/12)<P2/(MONTH(D1)-3)

Explanation:
D2;O2 are the totals for the twelve months the particular saleman works
I divide by 12 to get his average for the year to date
P2 is the target figure to be achieved
MONTH(D1) -3 is the thing I think I am getting wrong. What I want is the
number of months passed since the start of the trade year, taking into
account that the trade year starts in April and so will still be the same
trade year when passing from December to January.

Any other thoughts?

 

[Max]

Assume D1:O1 contains the text: April, May, ... March
(ie the 12 calendar months in "mmmm" format)

D2:O2, D3:O3, D4:O4, etc will contain the monthly sales achieved by
respective salesmen

P2, P3, P4, etc contains the target sales (total) for the full 12 months for
each salesman

Let's assume you want to conditionally format all the target cells P2, P3, etc
to auto-reflect whether the average monthly sales to-date
is on target (green) or not (red)

First, just normally format P2x with red fill/white font (via Format>Cells)

Then select P2x, apply conditional formatting using Formula Is**:
=AVERAGE(OFFSET(D2,,,,MATCH(TEXT(TODAY(),"mmmm"),$D$1:$O$1,0)))>=P2/12
Format > Green fill/automatic "black" font > Ok out

**or via equivalent CF options in xl2007, as pointed out by Biff
(I don't know xl2007 as well)