Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With Conditional formatting
I am using 2007.
My spreadsheet contains sales figures for the sales staff and is auto summed for each sales person. I need to conditional format G:K based on the word Total in Col A and The cell values in Cols G:K. So that for each row in col A that contains the word "Total", Excel will format G:K based on their value. If A contains "Total" set conditional fomat of G:K as follows: If G:K < 100 Fill Color Green If G:K 150 fill color Yellow Can someone please help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With Conditional formatting
use sumif condition
=sumif(G:K,"<100) & =sumif(G:K,"150") On Apr 3, 10:45*pm, CB wrote: I am using 2007. My spreadsheet contains sales figures for the sales staff and is auto summed for each sales person. I need to conditional format G:K based on the word Total in Col A and The cell values in Cols G:K. So that for each row in col A that contains the word "Total", Excel will format G:K based on their value. * If A contains "Total" set conditional fomat of G:K as follows: If G:K < 100 Fill Color Green If G:K 150 fill color Yellow Can someone please help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With Conditional formatting
Try the following....
This is based on the conditional formatting for one particular cell (G1), but you can copy this formatting to all other cells in H, I, J, and K. Instead of choosing Cell Value Is in the drop down menu, choose Formula Is the formula should look like =AND($A1="Total",G1150), then change the format to fill yellow. There has to be another for the green fill, add another condition and use the same formual except changing G1150 to G1<100 and make the fill green. You can then copy and paste special this conditional format to any cell. Hope this helps!! "CB" wrote: I am using 2007. My spreadsheet contains sales figures for the sales staff and is auto summed for each sales person. I need to conditional format G:K based on the word Total in Col A and The cell values in Cols G:K. So that for each row in col A that contains the word "Total", Excel will format G:K based on their value. If A contains "Total" set conditional fomat of G:K as follows: If G:K < 100 Fill Color Green If G:K 150 fill color Yellow Can someone please help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With Conditional formatting
Select G:K
Conditional Formatting/Manage Rules/New Rule Select "Use a formula to determine which cells to format" enter =AND($A1="TOTAL",G1150) click Format, give it yellow fill. Click OK, then click New Rule enter =AND($A1="TOTAL",G1<100) click Format, give it green fill. You're done. Note - the "$" is important. Also assumes that G1 is the ACTIVE cell while applying these rules. Bob Umlas Excel MVP "CB" wrote: I am using 2007. My spreadsheet contains sales figures for the sales staff and is auto summed for each sales person. I need to conditional format G:K based on the word Total in Col A and The cell values in Cols G:K. So that for each row in col A that contains the word "Total", Excel will format G:K based on their value. If A contains "Total" set conditional fomat of G:K as follows: If G:K < 100 Fill Color Green If G:K 150 fill color Yellow Can someone please help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With Conditional formatting
Bob,
Thanks for your response. I tried this and discovered that I left out some information. I need to use this if Column A contained the text "Austin Total", "Dallas Total"....etc. Is there a way to tell it for $A1 for rows Containing "Total"? Thanks, CB "Bob Umlas, Excel MVP" wrote: Select G:K Conditional Formatting/Manage Rules/New Rule Select "Use a formula to determine which cells to format" enter =AND($A1="TOTAL",G1150) click Format, give it yellow fill. Click OK, then click New Rule enter =AND($A1="TOTAL",G1<100) click Format, give it green fill. You're done. Note - the "$" is important. Also assumes that G1 is the ACTIVE cell while applying these rules. Bob Umlas Excel MVP "CB" wrote: I am using 2007. My spreadsheet contains sales figures for the sales staff and is auto summed for each sales person. I need to conditional format G:K based on the word Total in Col A and The cell values in Cols G:K. So that for each row in col A that contains the word "Total", Excel will format G:K based on their value. If A contains "Total" set conditional fomat of G:K as follows: If G:K < 100 Fill Color Green If G:K 150 fill color Yellow Can someone please help? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With Conditional formatting
Sure. change formulas to:
=AND(RIGHT($A1,5)="Total",G1150) =AND(RIGHT($A1,5)="Total",G1<100) "CB" wrote: Bob, Thanks for your response. I tried this and discovered that I left out some information. I need to use this if Column A contained the text "Austin Total", "Dallas Total"....etc. Is there a way to tell it for $A1 for rows Containing "Total"? Thanks, CB "Bob Umlas, Excel MVP" wrote: Select G:K Conditional Formatting/Manage Rules/New Rule Select "Use a formula to determine which cells to format" enter =AND($A1="TOTAL",G1150) click Format, give it yellow fill. Click OK, then click New Rule enter =AND($A1="TOTAL",G1<100) click Format, give it green fill. You're done. Note - the "$" is important. Also assumes that G1 is the ACTIVE cell while applying these rules. Bob Umlas Excel MVP "CB" wrote: I am using 2007. My spreadsheet contains sales figures for the sales staff and is auto summed for each sales person. I need to conditional format G:K based on the word Total in Col A and The cell values in Cols G:K. So that for each row in col A that contains the word "Total", Excel will format G:K based on their value. If A contains "Total" set conditional fomat of G:K as follows: If G:K < 100 Fill Color Green If G:K 150 fill color Yellow Can someone please help? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With Conditional formatting
Bob,
This works great! Thanks so much. CB "Bob Umlas, Excel MVP" wrote: Sure. change formulas to: =AND(RIGHT($A1,5)="Total",G1150) =AND(RIGHT($A1,5)="Total",G1<100) "CB" wrote: Bob, Thanks for your response. I tried this and discovered that I left out some information. I need to use this if Column A contained the text "Austin Total", "Dallas Total"....etc. Is there a way to tell it for $A1 for rows Containing "Total"? Thanks, CB "Bob Umlas, Excel MVP" wrote: Select G:K Conditional Formatting/Manage Rules/New Rule Select "Use a formula to determine which cells to format" enter =AND($A1="TOTAL",G1150) click Format, give it yellow fill. Click OK, then click New Rule enter =AND($A1="TOTAL",G1<100) click Format, give it green fill. You're done. Note - the "$" is important. Also assumes that G1 is the ACTIVE cell while applying these rules. Bob Umlas Excel MVP "CB" wrote: I am using 2007. My spreadsheet contains sales figures for the sales staff and is auto summed for each sales person. I need to conditional format G:K based on the word Total in Col A and The cell values in Cols G:K. So that for each row in col A that contains the word "Total", Excel will format G:K based on their value. If A contains "Total" set conditional fomat of G:K as follows: If G:K < 100 Fill Color Green If G:K 150 fill color Yellow Can someone please help? |
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 that will display conditional data | Excel Worksheet Functions | |||
Conditional formatting | Excel Worksheet Functions |