Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Pivot Table Rows in Excel 2007
I have pivot table where I want to highilight an entire row if the Avg Price
column(G) falls between two price points that listed in cells at the top of the pivot table. Cell O1 has the low price point and Cell P1 has the high price point. If the Avg price in any row falls between these two price points I want the background to fill the entire row to. Can only get to work on the one cell not the row. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Pivot Table Rows in Excel 2007
When you select the desired area (rows 4:1000, for example) and setup your
Conditional Format fomrula, are you using absolute references ($O$1 and $P$1) for the lower/upper bounds and a mixed reference ($G4) for the row's price? If you leave out the dollar signs, that will cause the Conditional Format to point to the wrong cells as you go across and down. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: I have pivot table where I want to highilight an entire row if the Avg Price column(G) falls between two price points that listed in cells at the top of the pivot table. Cell O1 has the low price point and Cell P1 has the high price point. If the Avg price in any row falls between these two price points I want the background to fill the entire row to. Can only get to work on the one cell not the row. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Pivot Table Rows in Excel 2007
The $ are there for O1 and P1. Tthe conditional formatting does work for the
cells that fall in the price range that I choose in those two celss, but I want it to format the entire row. "KC Rippstein" wrote: When you select the desired area (rows 4:1000, for example) and setup your Conditional Format fomrula, are you using absolute references ($O$1 and $P$1) for the lower/upper bounds and a mixed reference ($G4) for the row's price? If you leave out the dollar signs, that will cause the Conditional Format to point to the wrong cells as you go across and down. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: I have pivot table where I want to highilight an entire row if the Avg Price column(G) falls between two price points that listed in cells at the top of the pivot table. Cell O1 has the low price point and Cell P1 has the high price point. If the Avg price in any row falls between these two price points I want the background to fill the entire row to. Can only get to work on the one cell not the row. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Pivot Table Rows in Excel 2007
If you selected the entire range of rows (rows 4:100, for example) and go to
the Conditional Format, then the conditional format formula should be as follows: =AND($G4=$O$1,$G4<=$P$1) -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: The $ are there for O1 and P1. Tthe conditional formatting does work for the cells that fall in the price range that I choose in those two celss, but I want it to format the entire row. "KC Rippstein" wrote: When you select the desired area (rows 4:1000, for example) and setup your Conditional Format fomrula, are you using absolute references ($O$1 and $P$1) for the lower/upper bounds and a mixed reference ($G4) for the row's price? If you leave out the dollar signs, that will cause the Conditional Format to point to the wrong cells as you go across and down. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: I have pivot table where I want to highilight an entire row if the Avg Price column(G) falls between two price points that listed in cells at the top of the pivot table. Cell O1 has the low price point and Cell P1 has the high price point. If the Avg price in any row falls between these two price points I want the background to fill the entire row to. Can only get to work on the one cell not the row. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Pivot Table Rows in Excel 2007
Only the date column filled. I assume because it is in a pivot table as I
did a copy and paste special of the values onto a new worksheet and it did highlight the entire row then. Is it possible to do on a pivot table? "KC" wrote: If you selected the entire range of rows (rows 4:100, for example) and go to the Conditional Format, then the conditional format formula should be as follows: =AND($G4=$O$1,$G4<=$P$1) -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: The $ are there for O1 and P1. Tthe conditional formatting does work for the cells that fall in the price range that I choose in those two celss, but I want it to format the entire row. "KC Rippstein" wrote: When you select the desired area (rows 4:1000, for example) and setup your Conditional Format fomrula, are you using absolute references ($O$1 and $P$1) for the lower/upper bounds and a mixed reference ($G4) for the row's price? If you leave out the dollar signs, that will cause the Conditional Format to point to the wrong cells as you go across and down. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: I have pivot table where I want to highilight an entire row if the Avg Price column(G) falls between two price points that listed in cells at the top of the pivot table. Cell O1 has the low price point and Cell P1 has the high price point. If the Avg price in any row falls between these two price points I want the background to fill the entire row to. Can only get to work on the one cell not the row. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Pivot Table Rows in Excel 2007
It works fine for me, whether the pivot table uses one of the available pivot
table formats or is just a plain old pivot table, I can conditional format entire rows without any trouble. Sorry, don't know what else to tell you. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: Only the date column filled. I assume because it is in a pivot table as I did a copy and paste special of the values onto a new worksheet and it did highlight the entire row then. Is it possible to do on a pivot table? "KC" wrote: If you selected the entire range of rows (rows 4:100, for example) and go to the Conditional Format, then the conditional format formula should be as follows: =AND($G4=$O$1,$G4<=$P$1) -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: The $ are there for O1 and P1. Tthe conditional formatting does work for the cells that fall in the price range that I choose in those two celss, but I want it to format the entire row. "KC Rippstein" wrote: When you select the desired area (rows 4:1000, for example) and setup your Conditional Format fomrula, are you using absolute references ($O$1 and $P$1) for the lower/upper bounds and a mixed reference ($G4) for the row's price? If you leave out the dollar signs, that will cause the Conditional Format to point to the wrong cells as you go across and down. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: I have pivot table where I want to highilight an entire row if the Avg Price column(G) falls between two price points that listed in cells at the top of the pivot table. Cell O1 has the low price point and Cell P1 has the high price point. If the Avg price in any row falls between these two price points I want the background to fill the entire row to. Can only get to work on the one cell not the row. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Pivot Table Rows in Excel 2007
I did get it to work. Range was set up incorrectly. Thanks.
"KC" wrote: It works fine for me, whether the pivot table uses one of the available pivot table formats or is just a plain old pivot table, I can conditional format entire rows without any trouble. Sorry, don't know what else to tell you. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: Only the date column filled. I assume because it is in a pivot table as I did a copy and paste special of the values onto a new worksheet and it did highlight the entire row then. Is it possible to do on a pivot table? "KC" wrote: If you selected the entire range of rows (rows 4:100, for example) and go to the Conditional Format, then the conditional format formula should be as follows: =AND($G4=$O$1,$G4<=$P$1) -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: The $ are there for O1 and P1. Tthe conditional formatting does work for the cells that fall in the price range that I choose in those two celss, but I want it to format the entire row. "KC Rippstein" wrote: When you select the desired area (rows 4:1000, for example) and setup your Conditional Format fomrula, are you using absolute references ($O$1 and $P$1) for the lower/upper bounds and a mixed reference ($G4) for the row's price? If you leave out the dollar signs, that will cause the Conditional Format to point to the wrong cells as you go across and down. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: I have pivot table where I want to highilight an entire row if the Avg Price column(G) falls between two price points that listed in cells at the top of the pivot table. Cell O1 has the low price point and Cell P1 has the high price point. If the Avg price in any row falls between these two price points I want the background to fill the entire row to. Can only get to work on the one cell not the row. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Pivot Table Rows in Excel 2007
I spoke too soon. It worked fine once I got the range of =$A$6:$N$110 set up
in my rule. However, whenever I refresh my date my range changes to =$A$6:$A$110 so it only fills that first coulmn??? "KC" wrote: It works fine for me, whether the pivot table uses one of the available pivot table formats or is just a plain old pivot table, I can conditional format entire rows without any trouble. Sorry, don't know what else to tell you. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: Only the date column filled. I assume because it is in a pivot table as I did a copy and paste special of the values onto a new worksheet and it did highlight the entire row then. Is it possible to do on a pivot table? "KC" wrote: If you selected the entire range of rows (rows 4:100, for example) and go to the Conditional Format, then the conditional format formula should be as follows: =AND($G4=$O$1,$G4<=$P$1) -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: The $ are there for O1 and P1. Tthe conditional formatting does work for the cells that fall in the price range that I choose in those two celss, but I want it to format the entire row. "KC Rippstein" wrote: When you select the desired area (rows 4:1000, for example) and setup your Conditional Format fomrula, are you using absolute references ($O$1 and $P$1) for the lower/upper bounds and a mixed reference ($G4) for the row's price? If you leave out the dollar signs, that will cause the Conditional Format to point to the wrong cells as you go across and down. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: I have pivot table where I want to highilight an entire row if the Avg Price column(G) falls between two price points that listed in cells at the top of the pivot table. Cell O1 has the low price point and Cell P1 has the high price point. If the Avg price in any row falls between these two price points I want the background to fill the entire row to. Can only get to work on the one cell not the row. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Pivot Table Rows in Excel 2007
I'm not following your problem. I was under the impression you were doing
the following: - selecting entire rows (highlighting 6:110, not A6:N110) - entering this conditional format formula =AND($G4=$O$1,$G4<=$P$1) Where does this range $A$6:$N$110 or $A$6:$A$110 come into play? Conditional format should work just fine after a PT refresh. "Supe" wrote: I spoke too soon. It worked fine once I got the range of =$A$6:$N$110 set up in my rule. However, whenever I refresh my date my range changes to =$A$6:$A$110 so it only fills that first coulmn??? "KC" wrote: It works fine for me, whether the pivot table uses one of the available pivot table formats or is just a plain old pivot table, I can conditional format entire rows without any trouble. Sorry, don't know what else to tell you. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: Only the date column filled. I assume because it is in a pivot table as I did a copy and paste special of the values onto a new worksheet and it did highlight the entire row then. Is it possible to do on a pivot table? "KC" wrote: If you selected the entire range of rows (rows 4:100, for example) and go to the Conditional Format, then the conditional format formula should be as follows: =AND($G4=$O$1,$G4<=$P$1) -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: The $ are there for O1 and P1. Tthe conditional formatting does work for the cells that fall in the price range that I choose in those two celss, but I want it to format the entire row. "KC Rippstein" wrote: When you select the desired area (rows 4:1000, for example) and setup your Conditional Format fomrula, are you using absolute references ($O$1 and $P$1) for the lower/upper bounds and a mixed reference ($G4) for the row's price? If you leave out the dollar signs, that will cause the Conditional Format to point to the wrong cells as you go across and down. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: I have pivot table where I want to highilight an entire row if the Avg Price column(G) falls between two price points that listed in cells at the top of the pivot table. Cell O1 has the low price point and Cell P1 has the high price point. If the Avg price in any row falls between these two price points I want the background to fill the entire row to. Can only get to work on the one cell not the row. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Pivot Table Rows in Excel 2007
Thanks. I was highlighting the cells within pivot instead of the entire row.
"KC" wrote: I'm not following your problem. I was under the impression you were doing the following: - selecting entire rows (highlighting 6:110, not A6:N110) - entering this conditional format formula =AND($G4=$O$1,$G4<=$P$1) Where does this range $A$6:$N$110 or $A$6:$A$110 come into play? Conditional format should work just fine after a PT refresh. "Supe" wrote: I spoke too soon. It worked fine once I got the range of =$A$6:$N$110 set up in my rule. However, whenever I refresh my date my range changes to =$A$6:$A$110 so it only fills that first coulmn??? "KC" wrote: It works fine for me, whether the pivot table uses one of the available pivot table formats or is just a plain old pivot table, I can conditional format entire rows without any trouble. Sorry, don't know what else to tell you. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: Only the date column filled. I assume because it is in a pivot table as I did a copy and paste special of the values onto a new worksheet and it did highlight the entire row then. Is it possible to do on a pivot table? "KC" wrote: If you selected the entire range of rows (rows 4:100, for example) and go to the Conditional Format, then the conditional format formula should be as follows: =AND($G4=$O$1,$G4<=$P$1) -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: The $ are there for O1 and P1. Tthe conditional formatting does work for the cells that fall in the price range that I choose in those two celss, but I want it to format the entire row. "KC Rippstein" wrote: When you select the desired area (rows 4:1000, for example) and setup your Conditional Format fomrula, are you using absolute references ($O$1 and $P$1) for the lower/upper bounds and a mixed reference ($G4) for the row's price? If you leave out the dollar signs, that will cause the Conditional Format to point to the wrong cells as you go across and down. -- Please remember to indicate when the post is answered so others can benefit from it later. "Supe" wrote: I have pivot table where I want to highilight an entire row if the Avg Price column(G) falls between two price points that listed in cells at the top of the pivot table. Cell O1 has the low price point and Cell P1 has the high price point. If the Avg price in any row falls between these two price points I want the background to fill the entire row to. Can only get to work on the one cell not the row. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Rows Pivot Table-Excel 2007 | Excel Discussion (Misc queries) | |||
Can you set conditional formatting in pivot table? | Excel Discussion (Misc queries) | |||
How to select different rows in Pivot table excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 - Conditional Formatting applied to Pivot Tables | Excel Discussion (Misc queries) | |||
Conditional Formatting in Pivot Table | Excel Worksheet Functions |