Expression Background Color

[Anonymous]

I'm hoping someone can help me out with the following problem. I have a pivot table that is updated weekly with the prior week's sales. This is fiscal-year-to-date, so each week the size of the table gets larger since the week column is pivoted across the top. This is all fine but here is what the user's need:

The weeks are sorted with the most recent week first. The background color of the expression cells need to be yellow for all weeks with a zero until a week with positive numbers is encountered.

In the image below, row 1 would have weeks 45, 44 and 43 with a yellow background. Rows 2, 3 and 4 would be yellow for all weeks. Row 5 would be yellow until week 41. Etc. etc.

I've tried using an IF statement with BEFORE and SUM but didn't have any luck getting that to work.

[swuehl · Accepted solution]

Try something like

=if(rangesum(before( YourExpression , 0, columno() )) = 0, yellow() )

[Anonymous]

That looks correct. I was using Sum instead of RangeSum. Thanks!!!!