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

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)