Hello -
I have a pivot table with the following information and I am trying to set the Background color of the Status colum based on the Actual value compared to the Ceiling and Floor values. I've tried using Visual Cues which I seed the Status column with the Actual value and then use the Ceiling row value as the "Upper" threshold and the Floor column value as the "Lower" threshold for the Status column on the Visual Cues tab. No mattter what the Actual value is I am getting the "Normal" color return of Yellow in all Status cell backgrounds.
What I want is this:
| Ceiling | Floor | Projected | Actual | Status |
|---|
| .45 | .23 | .44 | .19 |
|
| 100 | 50 | 99 | 116 |
|
| .23 | .1 | .22 | .17 |
|
| 10 | 5 | 9 | 12 |
|
| 34 | 21 | 33 | 28 |
|
| 11 | 4 | 10 | 3 |
|
I've tried using the Expression's Background and Text settings and I can get it to work properly with IF statements for the rows but it also applies the formatting logic to the header row text so I get something like this:
| Ceiling | Floor
| Projected | Actual |
|
|---|
| .45 | .23 | .44 | .19 | |
| 100 | 50 | 99 | 116 |
|
| .23 | .1 | .22 | .17 |
|
| 10 | 5 | 9 | 12 |
|
| 34 | 21 | 33 | 28 |
|
| 11 | 4 | 10 | 3 | |
I've tried rowno()>0 and all sorts of other row testing but nothing seems to avoid impacting the header row cell. I've also tried 'Custom Format Cell" but that doesn't work either.
The most appropriate solution should be to use the "Visual Cues" but for some reason the [Ceiling]/[Floor] values are ignored and every cell displays a Yellow background.
Any help would be much appreciated.
Dave
