I am having an issue where my formula for this condition does not seem to work right.

if the columns T OR L has "Y" AND the column A is between 0 and 15 I want to color it. Unfortunately, the number range does not seem to be working. The numbers in A are generated by a formula and I am not sure if that is what is stopping this from working.

Complaints Tracker - FY21.xlsm | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | L | T | |||||||||||||

1 | Days Open | Counseling Days Remaining | ADR Days Remaining | iComplaints/Case # | Aggrieved Person (Name) | COMMISSARY/HQ | Employment Status | Date of Initial Contact | Counseling Extension (Y/N) | ADR Selected (Y/N)? | ||||||||||||

2 | 3 | 1 | DeCA-0001-2020 | Captain Ahab | dog | Applicant | 6/30/20 | N | Y | |||||||||||||

3 | 3 | 35 | DeCA-0002-2020 | George Jefferson | dope | Applicant | 10/2/20 | N | N | |||||||||||||

4 | 18 | 61 | DeCA-0003-2020 | James Brown | bottle | 10/28/20 | N | N | ||||||||||||||

5 | 176 | -62 | DeCA-0003-2021 | Jane Doe | war | Applicant | 6/27/20 | N | N | |||||||||||||

6 | 25 | -94 | DeCA-0003-2022 | JJ Walker | mat | Applicant | 5/26/20 | N | N | |||||||||||||

7 | 239 | -65 | DeCA-0003-2023 | Louise Jefferson | can | 4/25/20 | Y | N | ||||||||||||||

8 | 271 | -157 | DeCA-0003-2024 | Lucille Ball | care | 3/24/20 | N | N | ||||||||||||||

9 | 119 | 55 | DeCA-0003-2025 | Michael Jordan | shoe | Applicant | 8/23/20 | Y | Y | |||||||||||||

10 | 120 | -6 | DeCA-0003-2026 | Mickey Mouse | slipper | Applicant | 8/22/20 | N | ||||||||||||||

11 | 121 | 53 | 8/21/20 | N | Y | |||||||||||||||||

12 | Y | Y | ||||||||||||||||||||

Counselings |

Cell Formulas | ||
---|---|---|

Range | Formula | |

A2:A12 | A2 | =IFERROR(IF(X2="",TODAY()-H2,X2-H2),"") |

B2:B12 | B2 | =IFERROR(IF(T2="Y","",IF(OR(L2="",L2="N"),SUM((SUM(H2+30)-TODAY())),SUM((SUM(H2+90)-TODAY())))),"") |

C2:C12 | C2 | =IFERROR(IF(T2="","",IFS(AND(T2="Y",L2="N"),SUM((SUM(H2+90)-TODAY())),AND(L2="Y",T2="Y"),SUM((SUM(H2+90)-TODAY())))),"") |

F2:G12 | F2 | =IFERROR(VLOOKUP(E2,Contacts!B:N,3),"") |

H12 | H12 | =IFERROR(VLOOKUP(D12,Contacts!A:F,6,),"") |

Named Ranges | ||
---|---|---|

Name | Refers To | Cells |

Contacts!_FilterDatabase | =Contacts!$A$1:$AE$1 | H12 |

Cells with Conditional Formatting | ||||
---|---|---|---|---|

Cell | Condition | Cell Format | Stop If True | |

A1:A1000 | Expression | =AND(OR($L1="y",$T1="Y"),$A1<15) | text | NO |

Cells with Data Validation | ||
---|---|---|

Cell | Allow | Criteria |

G1,G562:G1048576 | List | =DropDown!$A$2:$A$5 |

L1:L12 | List | =DropDown!$C$2:$C$3 |