Hi,
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.
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 |