I need a conditional formatting to highlight column Q. Depending on the columns E (Type of Leave), it will correspond to the available balances in column R(Provincial Leave / Family Responsibility), S(Medical Waiver), T (Bereavement). If column Q is > the available balances, it would highlight Red.
| PL_WIP v4.5 (Make Copy).xlsb | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| L | M | N | O | P | Q | R | S | T | |||
| 1 | Type of Leave | Approver Comments | EID | Comments | Total Days Requested | PL/FRL | MW | BER | |||
| 2 | 0 | Not Used | Not Used | Not Used | |||||||
| 3 | Medical Waiver | 1 | Not Used | Not Used | Not Used | ||||||
| 4 | Family Responsibility | 0.5 | Not Used | Not Used | Not Used | ||||||
| 5 | Family Responsibility | 1 | Not Used | Not Used | Not Used | ||||||
WIP | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| Q2:Q5 | Q2 | =SUMIFS($G:$G,$C:$C,$C2,$L:$L,$L2) |
| R2:R5 | R2 | =IFERROR(INDEX('PL Balances Converted'!$B$2:$F$10000,MATCH($C2,'PL Balances Converted'!$A$2:$A$10000,0),MATCH($L2,'PL Balances Converted'!$B$1:$F$1,0)),"Not Used") |
