Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
"ellinor2304" wrote:
.. 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 FormatCells) 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 Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
Line:
.. contains a value other than zero should have read as: .. contains a value greater than zero -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
I use Excel 2007.
apply conditional formatting using Formula Is: 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 FormattingUse a formula to determine which cells to formatFormat values where this formula is true I don't have Excel 2007 but I trust Niek! -- Biff Microsoft Excel MVP "Max" wrote in message ... "ellinor2304" wrote: .. 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 FormatCells) 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 Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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? "T. Valko" wrote: I use Excel 2007. apply conditional formatting using Formula Is: 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 FormattingUse a formula to determine which cells to formatFormat values where this formula is true I don't have Excel 2007 but I trust Niek! -- Biff Microsoft Excel MVP "Max" wrote in message ... "ellinor2304" wrote: .. 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 FormatCells) 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 Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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 P2:Px with red fill/white font (via FormatCells) Then select P2:Px, 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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ellinor2304" wrote: (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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |