Condition using formula

Dee Dee

New Member
Joined
Sep 18, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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.

Complaints Tracker - FY21.xlsm
ABCDEFGHLT
1Days OpenCounseling Days RemainingADR Days RemainingiComplaints/Case #Aggrieved Person (Name)COMMISSARY/HQEmployment StatusDate of Initial ContactCounseling Extension (Y/N)ADR Selected (Y/N)?
23 1DeCA-0001-2020Captain AhabdogApplicant6/30/20NY
3335 DeCA-0002-2020George JeffersondopeApplicant10/2/20NN
41861 DeCA-0003-2020James Brownbottle 10/28/20NN
5176-62 DeCA-0003-2021Jane DoewarApplicant6/27/20NN
625-94 DeCA-0003-2022JJ WalkermatApplicant5/26/20NN
7239-65 DeCA-0003-2023Louise Jeffersoncan 4/25/20YN
8271-157 DeCA-0003-2024Lucille Ballcare 3/24/20NN
9119 55DeCA-0003-2025Michael JordanshoeApplicant8/23/20YY
10120-6 DeCA-0003-2026Mickey MouseslipperApplicant8/22/20N
11121 53  8/21/20NY
12      YY
Counselings
Cell Formulas
RangeFormula
A2:A12A2=IFERROR(IF(X2="",TODAY()-H2,X2-H2),"")
B2:B12B2=IFERROR(IF(T2="Y","",IF(OR(L2="",L2="N"),SUM((SUM(H2+30)-TODAY())),SUM((SUM(H2+90)-TODAY())))),"")
C2:C12C2=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:G12F2=IFERROR(VLOOKUP(E2,Contacts!B:N,3),"")
H12H12=IFERROR(VLOOKUP(D12,Contacts!A:F,6,),"")
Named Ranges
NameRefers ToCells
Contacts!_FilterDatabase=Contacts!$A$1:$AE$1H12
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A1000Expression=AND(OR($L1="y",$T1="Y"),$A1<15)textNO
Cells with Data Validation
CellAllowCriteria
G1,G562:G1048576List=DropDown!$A$2:$A$5
L1:L12List=DropDown!$C$2:$C$3
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,619
Office Version
  1. 365
Platform
  1. Windows
In that data only A2 fits your criteria & it has been coloured, so not sure what your problem is.
 

Dee Dee

New Member
Joined
Sep 18, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
In that data only A2 fits your criteria & it has been coloured, so not sure what your problem is.
Does my formula specifically ask about that one cell alone? I was intending on making the condition for the whole row, in which case A3 should be colored as well.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,619
Office Version
  1. 365
Platform
  1. Windows
Why would A3 be coloured, both L3 & T3 are N?
 

Dee Dee

New Member
Joined
Sep 18, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Does my formula specifically ask about that one cell alone? I was intending on making the condition for the whole row, in which case A3 should be colored as well.
In that data only A2 fits your criteria & it has been coloured, so not sure what your problem is.
Nevermind. I see it. Thanks a bunch!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,619
Office Version
  1. 365
Platform
  1. Windows
Do you want the whole row formatted or just col A?
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Check "$" =AND(OR($L$2="Y",$T$2="Y"),$A$2<15)

Book1
ABCDEFGHLTX
1Days OpenCounseling Days RemainingADR Days RemainingiComplaints/Case #Aggrieved Person (Name)COMMISSARY/HQEmployment StatusDate of Initial ContactCounseling Extension (Y/N)ADR Selected (Y/N)?
29 1DeCA-0001-2020Captain Ahab  6/30/2020NY7/9/2020
3-535 DeCA-0002-2020George Jefferson  10/2/2020NN
4-3161 DeCA-0003-2020James Brown  10/28/2020NN
592-62 DeCA-0003-2021Jane Doe  6/27/2020NN
6124-94 DeCA-0003-2022JJ Walker  5/26/2020NN
7155-65 DeCA-0003-2023Louise Jefferson  4/25/2020YN
Sheet5
Cell Formulas
RangeFormula
A2:A7A2=IFERROR(IF(X2="",TODAY()-H2,X2-H2),"")
B2:B7B2=IFERROR(IF(T2="Y","",IF(OR(L2="",L2="N"),SUM((SUM(H2+30)-TODAY())),SUM((SUM(H2+90)-TODAY())))),"")
C2:C7C2=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:G7F2=IFERROR(VLOOKUP(E2,Contacts!B:N,3),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A1000Expression=AND(OR($L$2="Y",$T$2="Y"),$A$2<15)textNO
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
as you Like if you need to apply it for Individual Cell In Col "A" With the same Row in col "T" & "L"

VBA Code:
Sub Condition_using_formula()
   
    Dim Rng As Range
    Columns("A:A").FormatConditions.Delete
    For Each Rng In ActiveSheet.Range("A2:A1000")
    With Rng
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(OR($L$" & Rng.Row & "=""Y"",$T$" & Rng.Row & "=""Y""),$A$" & Rng.Row & "<15)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5296274
            .TintAndShade = 0
        End With
    .FormatConditions(1).StopIfTrue = False
    End With
    Next
End Sub


Book1
ABCDEFGHLT
1Days OpenCounseling Days RemainingADR Days RemainingiComplaints/Case #Aggrieved Person (Name)COMMISSARY/HQEmployment StatusDate of Initial ContactCounseling Extension (Y/N)ADR Selected (Y/N)?
29 1DeCA-0001-2020Captain Ahab  6/30/2020NY
3435 DeCA-0002-2020George Jefferson  10/2/2020NN
4-3161 DeCA-0003-2020James Brown  10/28/2020NN
592-62 DeCA-0003-2021Jane Doe  6/27/2020NN
6124-94 DeCA-0003-2022JJ Walker  5/26/2020NN
714 -65DeCA-0003-2023Louise Jefferson  4/25/2020YY
8187-157 DeCA-0003-2024Lucille Ball  3/24/2020NN
910 55DeCA-0003-2025Michael Jordan  8/23/2020YY
Sheet5
Cell Formulas
RangeFormula
A2,A8,A4:A6A2=IFERROR(IF(X2="",TODAY()-H2,X2-H2),"")
B2:B9B2=IFERROR(IF(T2="Y","",IF(OR(L2="",L2="N"),SUM((SUM(H2+30)-TODAY())),SUM((SUM(H2+90)-TODAY())))),"")
C2:C9C2=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:G9F2=IFERROR(VLOOKUP(E2,Contacts!B:N,3),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A9Expression=AND(OR($L$9="Y",$T$9="Y"),$A$9<15)textNO
A8Expression=AND(OR($L$8="Y",$T$8="Y"),$A$8<15)textNO
A7Expression=AND(OR($L$7="Y",$T$7="Y"),$A$7<15)textNO
A6Expression=AND(OR($L$6="Y",$T$6="Y"),$A$6<15)textNO
A5Expression=AND(OR($L$5="Y",$T$5="Y"),$A$5<15)textNO
A4Expression=AND(OR($L$4="Y",$T$4="Y"),$A$4<15)textNO
A3Expression=AND(OR($L$3="Y",$T$3="Y"),$A$3<15)textNO
A2Expression=AND(OR($L$2="Y",$T$2="Y"),$A$2<15)textNO
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,619
Office Version
  1. 365
Platform
  1. Windows
Why on earth are you suggesting setting 999 individual CF rules, when you can use just one?
Namely the rule that the OP is already using. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,719
Messages
5,626,469
Members
416,187
Latest member
L_D18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top