Michael
ActiveCell.FormulaR1C1 = _
"=IF(AND(LEN(RC[4])>2,IFERROR(MAX(VL OOKUP(RC1, Packing_Sl ip,11,FALS E),VLOOKUP (RC1,Packi ng_Slip,15 ,FALSE),VL OOKUP(RC1, Packing_Sl ip,19,FALS E)),0)>0), IF(AND(COU NTIFS('PO & SKU'!C4,""=MULTI*"")>0,ISN A(VLOOKUP( RC1,Packin g_Slip,8,F ALSE))=FAL SE),1,"""" ),IF(AND(R C10>0,RC[4 ]<>""MULTI SKU""),ROU NDUP((SUMI FS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=S"")+SUMIFS('P O & SKU" & _
"O & SKU'!C2,RC1,'PO & SKU'!C21,""=D"")+SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=P"")-SUMIFS('P O & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=O"")+RC10)/(SU MIF('PO & SKU'!C2,RC1,'PO & SKU'!C7)),2),IF(RC[4]=""MU LTISKU"",I F(COUNTIF( 'PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1)=COUNTIFS( 'PO & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1),1,(CO" & _
"O & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1)/COUNTIF('PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1))),""""))) "
=IF(AND(LEN(O2)>2,IFERROR(MAX(VLOOKU P($A2,Pack ing_Slip,1 1,FALSE),V LOOKUP($A2 ,Packing_S lip,15,FAL SE),VLOOKU P($A2,Pack ing_Slip,1 9,FALSE)), 0)>0),IF(A ND(COUNTIF S('PO & SKU'!$D:$D,"=MULTI*")>0,IS NA(VLOOKUP ($A2,Packi ng_Slip,8, FALSE))=FA LSE),1,"") ,IF(AND($J 2>0,O2<>"M ULTISKU"), ROUNDUP((S UMIFS('PO & SKU'!$V:$V,'PO & SKU'!$B:$B,$A2,'PO & SKU'!$U:$U,"=S")+SUMIFS('P O & SKU'!$V:$V,'PO & SKU'!$B:$B,$A2,'PO & SKU'!$U:$U,"=D")+SUMIFS('P O & SKU'!$V:$V,'PO & SKU'!$B:$B,$A2,'PO & SKU'!$U:$U,"=P")-SUMIFS('P O & SKU'!$V:$V,'PO & SKU'!$B:$B,$A2,'PO & SKU'!$U:$U,"=O")+$J2)/(SUM IF('PO & SKU'!$B:$B,$A2,'PO & SKU'!$G:$G)),2),IF(O2="MUL TISKU",IF( COUNTIF('P O & SKU'!$B:$B,'CONTAINER DATA'!$A4412)=COUNTIFS('PO & SKU'!$K:$K,"<>",'PO & SKU'!$B:$B,'CONTAINER DATA'!$A4412),1,(COUNTIFS( 'PO & SKU'!$K:$K,"<>",'PO & SKU'!$B:$B,'CONTAINER DATA'!$A4412)/COUNTIF('PO & SKU'!$B:$B,'CONTAINER DATA'!$A4412))),"")))
ActiveCell.FormulaR1C1 = _
"=IF(AND(LEN(RC[4])>2,IFERROR(MAX(VL OOKUP(RC1, Packing_Sl ip,11,FALS E),VLOOKUP (RC1,Packi ng_Slip,15 ,FALSE),VL OOKUP(RC1, Packing_Sl ip,19,FALS E)),0)>0), IF(AND(COU NTIFS('PO & SKU'!C4,""=MULTI*"")>0,ISN A(VLOOKUP( RC1,Packin g_Slip,8,F ALSE))=FAL SE),1,"""" ),IF(AND(R C10>0,RC[4 ]<>""MULTI SKU""),ROU NDUP((SUMI FS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=S"")+SUMIFS('P O & SKU" & _
"O & SKU'!C2,RC1,'PO & SKU'!C21,""=D"")+SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=P"")-SUMIFS('P O & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=O"")+RC10)/(SU MIF('PO & SKU'!C2,RC1,'PO & SKU'!C7)),2),IF(RC[4]=""MU LTISKU"",I F(COUNTIF( 'PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1)=COUNTIFS( 'PO & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1),1,(CO" & _
"O & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1)/COUNTIF('PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1))),""""))) "
ActiveCell.FormulaR1C1 = _
"=IF(AND(LEN(RC[4])>2,IFERROR(MAX(VL OOKUP(RC1, Packing_Sl ip,11,FALS E),VLOOKUP (RC1,Packi ng_Slip,15 ,FALSE),VL OOKUP(RC1, Packing_Sl ip,19,FALS E)),0)>0), " & _
"IF(AND(COUNTIFS('PO & SKU'!C4,""=MULTI*"")>0,ISNA(VLOOKUP( RC1,Packin g_Slip,8,F ALSE))=FAL SE),1,"""" ),IF(AND(R C10>0,RC[4 ]<>""MULTI SKU"")," & _
"ROUNDUP((SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=S"")+SUMIFS('PO & SKU'!C2,RC1,'PO & SKU'!C21,""=D"")+" & _
"SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=P"")-SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=O"")+RC10)/" & _
"(SUMIF('PO & SKU'!C2,RC1,'PO & SKU'!C7)),2),IF(RC[4]=""MULTISKU"",I F(COUNTIF( 'PO & SKU'!C2,'CONTAINER DATA'!R[2]C1)=" & _
"COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[2]C1),1,COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[2]C1)/" & _
"COUNTIF('PO & SKU'!C2,'CONTAINER DATA'!R[2]C1))),"""")))"
ActiveCell.FormulaR1C1 = "=IF(AND(LEN(RC[4])>2,IFERROR(MAX(VLOOKUP(RC1,packing_slip,11,FALSE),VLOOKUP(RC1,packing_slip,15,FALSE)," & _
"VLOOKUP(RC1,packing_slip,19,FALSE)),0)>0),IF(AND(COUNTIFS('PO & SKU'!C4,""=MULTI*"")>0," & _
"ISNA(VLOOKUP(RC1,packing_slip,8,FALSE))=FALSE),1,""""),IF(AND(RC10>0,RC[4]<>""MULTISKU"")," & _
"ROUNDUP((SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=S"")+SUMIFS('PO & SKU'!C22," & _
"'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=D"")+SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=P"")" & _
"-SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=O"")+RC10)/(SUMIF('PO & SKU'!C2,RC1,'PO & SKU'!C7)),2)," & _
"IF(RC[4]=""MULTISKU"",IF(COUNTIF('PO & SKU'!C2,'CONTAINER DATA'!R[2]C1)=COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2," & _
"'CONTAINER DATA'!R[2]C1),1,COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[2]C1)/" & _
"COUNTIF('PO & SKU'!C2,'CONTAINER DATA'!R[2]C1)),"""")))"
ActiveCell.FormulaR1C1 = "=IF(AND(LEN(RC[4])>2,IFERROR(MAX(VLOOKUP(RC1,packing_slip,11,FALSE),VLOOKUP(RC1,packing_slip,15,FALSE)," & _
"VLOOKUP(RC1,packing_slip,19,FALSE)),0)>0),IF(AND(COUNTIFS('PO & SKU'!C4,""=MULTI*"")>0," & _
"ISNA(VLOOKUP(RC1,packing_slip,8,FALSE))=FALSE),1,""""),IF(AND(RC10>0,RC[4]<>""MULTISKU"")," & _
"ROUNDUP((SUM(SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,{""S"",""D"",""P""}))" & _
"-SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=O"")+RC10)/(SUMIF('PO & SKU'!C2,RC1,'PO & SKU'!C7)),2)," & _
"IF(RC[4]=""MULTISKU"",IF(COUNTIF('PO & SKU'!C2,'CONTAINER DATA'!R[2]C1)=COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2," & _
"'CONTAINER DATA'!R[2]C1),1,COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[2]C1)/COUNTIF('PO & SKU'!C2," & _
"'CONTAINER DATA'!R[2]C1)),"""")))"
Title | # Comments | Views | Activity |
---|---|---|---|
How to convert JSON file to csv? | 7 | 48 | |
identifying alphanumerics in a column | 6 | 17 | |
TT Copy Formula | 3 | 15 | |
Excel 2016 - Method to delete all rows except those with specific color | 3 | 19 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
6 Experts available now in Live!