how to apply conditional formatting of an adiacent cell in a pivotable when the cell is empty

Question

I have a table of raw events to be summarized in a pivotable. Events are registered every 10 minutes: in the following way:

• if a device has changed its status, a row with value 0 or 1 is registered
• else an empty row is registered

NOTE: this is an exemplification of a more complex situation and the empty value cannot be filled with the previous device status.

NOTE: because I do not have enought reputation to publish images nor links, I have loaded my images at this link please refer to image names above (sorry for that)

This is how my table of fatcs looks like: (image facts.JPG)

and this is the pivottable I'm trying to build: (image pivot1.JPG)

as you can see, pivot values are calculated as MIN, so that if only one event among all in an hour is 0, then the device is marked as RED. this is obtained using conditional formatting rule.

OK, now let's expand the timeline: (image pivot2.JPG)

as you can see, the empty events are represented with unfilled color, instead they should be filled with the color of the last valid state

This is what I want to obtain: (image pivot3.JPG)

Answer 1

Here it comes to help a particular technique described in this post: how to apply format of an adiacent row in Excel when the cell is empty

I have applied that formula to the pivotable in this way: (image pivot4.JPG)

note: be careful to select the option 'all cells showing...' and to specify a mixed range reference that starts from the leftmost column of sheet so that the pivot table can apply the formula correctly to each cell.

=INDEX($A6:B6;MAX(IF(ISBLANK($A6:B6);"";COLUMN($A6:B6))))=1

and finally the result appliead to a real case: (image pivot5.JPG)