Excel: Conditional formatting in ROWS section of pivot table based on result in Values field

Question

I am trying add conditional formatting to the Rows section of a pivot table based on the results found in the values section of the pivot. I have already set the conditional formatting in the values section where if a field is blank, it gets highlighted yellow. My need is to be able to highlight the Employee name in yellow if the last Month showing on the pivot is blank ... which means the employee has been terminated. The attached picture is what the report would ideally look like when looking at results through April and through March. In both cases I highlighted the Employee names manually.

[2

Sample data:

Employee,Title,Dept,Month,Value
Amy Johnson,Manager,Sales,January,1
Amy Johnson,Manager,Sales,February,1
Amy Johnson,Manager,Sales,March,1
Dane Jones,Director,Operations,March,1
Dane Jones,Director,Operations,April,1
Dennis Cage,Manager,Marketing,January,1
Dennis Cage,Manager,Marketing,February,1
Erick Smith,VP,Sales,January,1
Erick Smith,VP,Sales,February,1
Erick Smith,VP,Sales,March,1
Erick Smith,VP,Sales,April,1

Answer 1

This should get you started in the right direction:

=OFFSET($H3,0,2+XLOOKUP(0,INT($K$2:$V$2<>""),SEQUENCE(1,12),"")-1)=""

INT($K$2:$V$2<>"") is returning a 12 column array of integers, containing either a 1 if row 2 has a value in that column (starting with K, which is where the first column header of my pivot table is), or zero, if it doesn't have a value in that column (i.e. it's gone past the last column header). This relies on you not having any other data or objects to the right of your pivot table such that there's enough space to display 12 months, which I assume you must need anyway otherwise you'll get an error message when you try to refresh the pivot table and there's no room for it to grow.

XLOOKUP is searching for the first '0' in the array returned by INT and returning the corresponding position as indicated by SEQUENCE(1,12), which is just a 12-column array of increasing integers. So, if the pivot table is display four months, then the XLOOKUP searches for zero, finds that the first zero is in the 5th position, and so returns 5 from the SEQUENCE. We are then subtracting 1 to get 4.

In addition to this, we know that there are two columns between the Employee name and the first Month column. So, OFFSET is just saying, "give me the value that is zero rows below this row and 2+4 columns to the right of this column". If the cell it finds is empty, then the rule formula returns TRUE and colors the name yellow.

You may need to further modify the formula to account for when the number of names in the pivot table drops below the number of rows specified in the AppliesTo range in order to avoid empty cells below the pivot table turning yellow.