Hi,

I really thank you for guiding me in this. I managed to get it work BUT unfortunately I actually have additional formulas after "Tier 0" which I replaced with "TierUnknown" in earlier posts, just to test & understand how the syntax should be to get it work.

I thought if I could understand it, then I could try applying it to the rests of the formulas but unfortunately the initial error will pop-up again & again. Would appreciate your corrections on my trials below.

The full formula I need to enter:-

Code:

=IF(OR($D5="",$I5="",AND($E5="TL",$W5<>"-")),"",IF(OR(ISNUMBER(SEARCH("TAT",M$4)),ISNUMBER(SEARCH("Suspense",M$4)),OR(ISNUMBER(SEARCH("Pender",M$4)),ISNUMBER(SEARCH("Accuracy",M$4)))),IF(I5>(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 1"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 0",
IF(I5>(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 2"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 1",
IF(I5>(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 3"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 2","Tier 3"))),
IF(I5<(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 1"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 0",
IF(I5<(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 2"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 1",
IF(I5<(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 3"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 2","Tier 3")))))

Partially converted into vba format:-

Code:

Sub TieringAll_Test2()Dim FormulaPart1 As String
Dim FormulaPart2 As String
Dim FormulaPart3 As String
Dim FormulaPart4 As String
Dim FormulaPart5 As String
Dim FormulaPart6 As String
Dim FormulaPart7 As String
Dim FormulaPart8 As String
Dim FormulaPart8a As String
Dim FormulaPart8b As String
Dim FormulaPart9 As String
Dim FormulaPart10 As String
Dim FormulaPart11 As String
Dim FormulaPart12 As String
Dim MainFormula1 As String
Dim MainFormula1a As String
Dim MainFormula1b As String
Dim LRowA As Long
Dim LRowM As Long
Application.ScreenUpdating = False
LRowA = Range("A" & Rows.Count).End(xlUp).Row
LRowM = Range("M" & Rows.Count).End(xlUp).Row + 1
FormulaPart1 = "=IF(OR($D65="""",$I65="""",AND($E65=""TL"",$W65<>""-"")),"""","
'FormulaPart1 = "=IF(OR(INDIRECT(""D""&ROW())="""",INDIRECT(""I""&ROW())="""",AND(INDIRECT(""E""&ROW())=""TL"",INDIRECT(""W""&ROW())<>""-"")),"""","
'FormulaPart1a = "INDIRECT(""D""&ROW())="""""
'FormulaPart1b = "INDIRECT(""I""&ROW())="""""
'FormulaPart1c = "INDIRECT(""E""&ROW())=""TL"""
'FormulaPart1d = "INDIRECT(""W""&ROW())<>""-"""
FormulaPart2 = "ISNUMBER(SEARCH(""TAT"",N$4))"
FormulaPart3 = "ISNUMBER(SEARCH(""Suspense"",N$4))"
FormulaPart4 = "ISNUMBER(SEARCH(""Pender"",N$4))"
FormulaPart5 = "ISNUMBER(SEARCH(""Accuracy"",N$4))"
FormulaPart6 = "TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,N$4))"
FormulaPart7 = "TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B65))"
FormulaPart8 = """Tier 1""=PI_Chart_Details!$F$1:$F$189"
FormulaPart8a = """Tier 2""=PI_Chart_Details!$F$1:$F$189"
FormulaPart8b = """Tier 3""=PI_Chart_Details!$F$1:$F$189"
FormulaPart9 = "PI_Chart_Details!$E$1:$E$189"
FormulaPart10 = "PI_Chart_Details!$D$1:$D$189"
FormulaPart11 = "PI_Chart_Details!$B$1:$B$189"
FormulaPart12 = "PI_Chart_Details!$F$1:$F$189"
MainFormula1 = "IF(I65>INDEX(Range1,MATCH(1,(FPart10)*(FPart11)*(FPart12),0)),""Tier0"""
MainFormula1a = "IF(I65>INDEX(Range1,MATCH(1,(FPart10)*(FPart11)*(FPart12),0)),""Tier1"""
MainFormula1b = "IF(I65>INDEX(Range1,MATCH(1,(FPart10)*(FPart11)*(FPart12),0)),""Tier2"""
With ActiveSheet.Range("M" & LRowM)
.FormulaArray = FormulaPart1 & "IF(OR(criteria1,criteria2,AND(criteria3,criteria4)),IF(I5>INDEX(Range1,MATCH(1,(FPart10)*(FPart11)*(FPart12),0)),""Tier 0"",""TierUnknown"")))"
'.FormulaArray = "=IF(OR(FPart1a,FPart1b,AND(FPart1c,FPart1d)),"""",IF(OR(criteria1,criteria2,criteria3,criteria4),IF(I5>INDEX(Range1,MATCH(1,(FPart10)*(FPart11)*(FPart12),0)),""Tier 0"",""TierUnknown"")))"
'.FormulaArray = FormulaPart1 & "IF(OR(criteria1,criteria2,AND(criteria3,criteria4)),IF(I5>MFormula1,0)),""Tier 0"",IF(I5>MFormula1a,0)),""Tier 1"",IF(I5>MFormula1b,0)),""Tier 2"",""TierUnknown"")))"
'.FormulaArray = FormulaPart1 & "IF(OR(criteria1,criteria2,AND(criteria3,criteria4)),MFormula1,MFormula1a,MFormula1b,""TierUnknown"")))"
'.FormulaArray = FormulaPart1 & "IF(OR(criteria1,criteria2,AND(criteria3,criteria4))," & MainFormula1 & "," & MainFormula1a & "," & MainFormula1b & ",""TierUnknown"")))"
.Replace "FPart1a", FormulaPart1a, LookAt:=xlPart
.Replace "FPart1b", FormulaPart1b
.Replace "FPart1c", FormulaPart1c
.Replace "FPart1d", FormulaPart1d
.Replace "criteria1", FormulaPart2
.Replace "criteria2", FormulaPart3
.Replace "criteria3", FormulaPart4
.Replace "criteria4", FormulaPart5
.Replace "FPart10", FormulaPart6
.Replace "FPart11", FormulaPart7
.Replace "FPart12", FormulaPart8
.Replace "Range1", FormulaPart9
.Replace "Range2", FormulaPart10
.Replace "Range3", FormulaPart11
.Replace "Range4", FormulaPart12
.Replace "FPart13", FormulaPart8a
.Replace "FPart14", FormulaPart8b
.Replace "MFormula1", MainFormula1
.Replace "MFormula1a", MainFormula1a
.Replace "MFormula1b", MainFormula1b
End With
Range("M" & LRowM & ":M" & LRowA).FillDown
Range("M" & LRowM).Select
Application.ScreenUpdating = True
End Sub

I still didn't know how I should understand & respect the syntax as all my trials based on my understanding but still failed. Appreciate your kind help. Or whoever has a solution out there, please help.

## Like this thread? Share it with others