Hi! Im new to VBA so please be kind..
I am currently doing a report wherein I identify what should be the scope (column B) and who should be the action owner (column A) in "data" tab, depending on the conditions. The conditions are in the "Instructions" tab. There a multiple conditions, if the 1st condition isn't met, i will proceed to the 2nd condition, so on and so forth, until i met the condition - so i can identify the owner and scope.
For example, the first condition is = if the document number (column S) in "Data" tab can be looked up in "Raw Data - Infosys In Scope" then the owner will be Indirect CO Owned and scope will be Freeze Items. But if that didn't meet the 1st condition, then i will go to the next condition and so on and forth.
Here's the link to the file: https://www.dropbox.com/s/z1sbnqm62yn9gt9/Working File.xlsx?dl=0
I've already tried below formula but it won't work. Some are returning FALSE instead of the right owner and scope..
=IFERROR(VLOOKUP(S5,'Raw Data - Infosys In Scope'!U:V,2,),IF(OR(LEFT(AI5,7)="HEWLETT",AI5="INFOSYS BPO LTD"),"Infosys / HP Vendor",IF(AND(E5="Logistics",F5<>"CBL"),"Outbound Logistics",IF(AND(F5="CBL",E5<>"Logistics"),"CBL Vendor",IF(AND(OR(LEFT(AI5,7)<>"HEWLETT",AI5<>"INFOSYS BPO LTD"),E5<>"Logistics",F5<>"CBL",OR(LEFT(J5,3)="A6P",LEFT(J5,3)="F6P")),"Direct Items",IF(OR(AND(OR(I5="SAUDI ARABIA",I5="EGYPT",I5="PAKISTAN",I5="DUBAI"),OR(Y5="110",Y5="#-P"),OR(LEFT(J5,3)="F7P",LEFT(J5,3)="A7P"),OR(O5="#",O5="Expense",O5="Storeroom"),OR(D5="Invoice Overdue",AND(D5="Invoice Near Due",C5="Infosys OOSLA"))),(AND(OR(I5="SAUDI ARABIA",I5="EGYPT",I5="PAKISTAN",I5="DUBAI"),H5="Infosys - Governance",OR(D5="Invoice Overdue",AND(D5="Invoice Near Due",C5="Infosys OOSLA")))),(AND(F5<>"CBL",G5="Central Ops"))),"LD - Indirect CO",IF(OR(AND(OR(I5="SAUDI ARABIA",I5="EGYPT",I5="PAKISTAN",I5="DUBAI"),OR(LEFT(J5,3)="F7P",LEFT(J5,3)="A7P"),OR(O5="#",O5="Expense",O5="Storeroom"),OR(AND(D5="Invoice Near Due",D5="Infosys w/ in SLA"),D5="Invoice Not Yet Due"),X5<>"#-Q"),(AND(OR(I5="SAUDI ARABIA",I5="EGYPT",I5="PAKISTAN",I5="DUBAI"),H5="Infosys - Governance",OR(AND(D5="Invoice Near Due",D5="Infosys w/ in SLA"),D5="Invoice Not Yet Due"),X5<>"#-Q"))),"LD - Infosys",IF(AND(OR(I5="SAUDI ARABIA",I5="EGYPT",I5="PAKISTAN",I5="DUBAI"),OR(LEFT(J5,3)="F7P",LEFT(J5,3)="A7P"),OR(O5="#",O5="Expense",O5="Storeroom"),X5="#-Q"),"Language Dependent Market"))))))))
I am currently doing a report wherein I identify what should be the scope (column B) and who should be the action owner (column A) in "data" tab, depending on the conditions. The conditions are in the "Instructions" tab. There a multiple conditions, if the 1st condition isn't met, i will proceed to the 2nd condition, so on and so forth, until i met the condition - so i can identify the owner and scope.
For example, the first condition is = if the document number (column S) in "Data" tab can be looked up in "Raw Data - Infosys In Scope" then the owner will be Indirect CO Owned and scope will be Freeze Items. But if that didn't meet the 1st condition, then i will go to the next condition and so on and forth.
Here's the link to the file: https://www.dropbox.com/s/z1sbnqm62yn9gt9/Working File.xlsx?dl=0
I've already tried below formula but it won't work. Some are returning FALSE instead of the right owner and scope..
=IFERROR(VLOOKUP(S5,'Raw Data - Infosys In Scope'!U:V,2,),IF(OR(LEFT(AI5,7)="HEWLETT",AI5="INFOSYS BPO LTD"),"Infosys / HP Vendor",IF(AND(E5="Logistics",F5<>"CBL"),"Outbound Logistics",IF(AND(F5="CBL",E5<>"Logistics"),"CBL Vendor",IF(AND(OR(LEFT(AI5,7)<>"HEWLETT",AI5<>"INFOSYS BPO LTD"),E5<>"Logistics",F5<>"CBL",OR(LEFT(J5,3)="A6P",LEFT(J5,3)="F6P")),"Direct Items",IF(OR(AND(OR(I5="SAUDI ARABIA",I5="EGYPT",I5="PAKISTAN",I5="DUBAI"),OR(Y5="110",Y5="#-P"),OR(LEFT(J5,3)="F7P",LEFT(J5,3)="A7P"),OR(O5="#",O5="Expense",O5="Storeroom"),OR(D5="Invoice Overdue",AND(D5="Invoice Near Due",C5="Infosys OOSLA"))),(AND(OR(I5="SAUDI ARABIA",I5="EGYPT",I5="PAKISTAN",I5="DUBAI"),H5="Infosys - Governance",OR(D5="Invoice Overdue",AND(D5="Invoice Near Due",C5="Infosys OOSLA")))),(AND(F5<>"CBL",G5="Central Ops"))),"LD - Indirect CO",IF(OR(AND(OR(I5="SAUDI ARABIA",I5="EGYPT",I5="PAKISTAN",I5="DUBAI"),OR(LEFT(J5,3)="F7P",LEFT(J5,3)="A7P"),OR(O5="#",O5="Expense",O5="Storeroom"),OR(AND(D5="Invoice Near Due",D5="Infosys w/ in SLA"),D5="Invoice Not Yet Due"),X5<>"#-Q"),(AND(OR(I5="SAUDI ARABIA",I5="EGYPT",I5="PAKISTAN",I5="DUBAI"),H5="Infosys - Governance",OR(AND(D5="Invoice Near Due",D5="Infosys w/ in SLA"),D5="Invoice Not Yet Due"),X5<>"#-Q"))),"LD - Infosys",IF(AND(OR(I5="SAUDI ARABIA",I5="EGYPT",I5="PAKISTAN",I5="DUBAI"),OR(LEFT(J5,3)="F7P",LEFT(J5,3)="A7P"),OR(O5="#",O5="Expense",O5="Storeroom"),X5="#-Q"),"Language Dependent Market"))))))))
