Formula based in Header

[gerald24]

Hi Guys,

So i have a very long formula with 1,269 characters. Basically, it is composed of multiple ifs, mathematical operations inside and outside of parenthesis.
I have set it up so long ago in specific columns.

I've even added this to vba so that it'll autopopulate in my worksheet.

A = 'HALF OF THE FORMULA
B = 'OTHER HALF OF THE FORMULA
C = A + B

Range("EL2").Formula = C

Problem = The template where I extract data changed. I know I can use Index Match to have my formula refer to the column where the specific header is. But it would be really really long.

Help Needed: Need suggestions on how I can do this efficiently and hopefully permanently regardless of the columns changing
(Take note that I am not to the level yet that I can setup this in VBA - I cannot do multiples IFs and multiple criteria)


Thanks in advance!!!!

 

[James006]

Hi,

Your question is rather mysterious ...

Would you post your very long mathematical formula, there are many contributors who would transform it a handy UDF for you ...

 

[gerald24]

Hi James,

Okay. give me some time to go thru the formula. I think I'll get fired if I published the actual formula. Let me just brush something from it.

 

[James006]

You are welcome

 

[gerald24]

Please find below formula

=IFERROR(IF(AND(Z2=0,EI2=0),IF(ABS((EC2*100)-ED2)<Source!$F$12,Source!$A$2,IF(ABS((EG2-EE2)/EG2)<Source!$F$13,Source!$A$3,"CAT A - CHECK")),IF(AND(Z2<>0,EI2=0),IF(AND(T2<>0,AND(X2<>"ComputerA",X2<>"ComputerB",X2<>"ComputerC",X2<>"ComputerD",X2<>"ComputerE",X2<>"ComputerF",X2<>"")),IF(AND(ABS((EG2-EE2)/EG2)<Source!$F$13,OR(OR(AND(EM2>T2,EM2<U2),AND(EM2<T2,EM2>U2)),OR(ABS((EM2-T2)/T2)<Source!$F$14,ABS((EM2-U2)/U2)<Source!$F$14))),Source!$A$4,"CAT AB - CHECK"),IF(AND(ABS((EG2-EE2)/EG2)<Source!$F$14,ABS((EM2-U2)/U2)<Source!$F$14),Source!$A$5,"CAT C - CHECK")),IF(AND(Z2<>0,EI2<>0),IF(AND(AB2<>0,AND(X2<>"ComputerA",X2<>"ComputerB",X2<>"ComputerC",X2<>"ComputerD",X2<>"ComputerE",X2<>"ComputerF",X2<>"")),IF(OR(AND(EM2>T2,EM2<U2),AND(EM2<T2,EM2>U2)),Source!$A$6,IF(OR(ABS((EM2-T2)/T2)<Source!$F$14,OR(ABS((EM2-U2)/U2)<Source!$F$14)),Source!$A$7,"Check Trades to HCPI")),IF(ABS((EM2-T2)/T2)<Source!$F$14,Source!$A$10,"CAT D - CHECK")),IF(AND(Z2=0,EI2<>0),IF(OR(AND(EM2>T2,EM2<U2),AND(EM2<T2,EM2>U2)),Source!$A$8,IF(OR(ABS((EM2-T2)/T2)<Source!$F$14,ABS((EM2-U2)/U2)<Source!$F$14),Source!$A$9,"CAT E - CHECK")),"Catergory not included-Check")))),"CAT CHECK - F")

 

[gerald24]

up

 

[gerald24]

up