Search with Multiple Criteria and Validation of Data in Multiple Tabs (Worksheet) in a Excel Workbook

[JRamirez]

Hi Guys, I am here as a new member of the Mr. Excel and I need anyone here that can offer any help with a function I need to develop for an excel report I am building:

The purpose of Excel Workbook Report I’m trying to develop is to be able to compare two different progress period in our project from our Oracle Database. The Data has been extracted and parse out of our oracle data base into an Excel Workbook. For this function been developed the lookup values will only be located in the two tab in the working excel workbook. These two tabs are called the “Task Current Update Period”, the second tab is called the “Task Previous Update PRD.” And the third tab is titled “Added Or Deleted Activities” tab.

The tab titled “Task Current Update Period” Contains the latest bi-weekly progress data of the project, each of task (i.e. lookup Value) in the “Task Current Update Period” tab contains all the general data for each task (activities) of project. This general data consist of dates, budgets units, progress percentage, manhours, etc. Each Task is an independent activity in our database which are uniquely identified in the column titled “task_id”. This unique number is randomly generated number from the Oracle Database.

The objective of this function is for it to have 4 outputs. Outputs here are define as “Criteria”.
The tab titled “Task Current Update Period” will be the working Tab:
The Formula will be inputted into Column BN Cell BN2
Lookup Value will located in Column titled “Task_Code”
Lookup Value is task code is: “WIN-IN-6-170” For this example

The Function will have four Following Outputs “Criteria”

1. 1^st Criteria is for Lookup Value “WIN-IN-6-170” is found, Identify and located in second tab is called the “Task Previous Update PRD.” If the results are true, the function will return the lookup value “WIN-IN-6-170” as the output of the function.
2. 2^nd Criteria is if the Lookup Value “WIN-IN-6-170” is not located (i.e. N/A, IF(ISERROR( ---) in the second tab which is called the “Task Previous Update PRD.” Then the resultant or output of the function will be a text value "Activity Not in The Previous Updated File “Activity Was Added To Current Progress Update"
3. 3^rd Criteria is if the Lookup Value “WIN-IN-6-170” is not in the current working tab “Task Current Update Period” but the Lookup Value is in the “Task Previous Update PRD.” Then the resultant or output of the function will be a text value "Activity Was in The Previous Updated File “Activity Was DELETED On The Current Progress Update"
4. 4^th Criteria will only occur only when all 3 previous output are met. The Resultant of the Lookup Value “WIN-IN-6-170” will now search the Lookup Value on the “Added Or Deleted Activities” tab. This Tab is the Report I am developing.
   1. On the “Added or Deleted Activities” tab the Lookup Value Will Located in Column F Rows F7:F9558.
   2. Once the Lookup Value “WIN-IN-6-170” is located or found the resultant output data of Criteria 1-3, will be Column Q7 of the “Added or Deleted Activities” tab where the Lookup Value “WIN-IN-6-170” was located.

The Function I have currently developed (see below): Only meets two out the four criteria, I have not been very successful on this task:

= IF(ISERROR(VLOOKUP($D2,'TASK PREVIOUS UPDATE PRD.'!$B:$B,1,0)),"Activity Not in The Previous Updated File",VLOOKUP($D2,'TASK PREVIOUS UPDATE PRD.'!$B:$B,1,0))

Please Let me know If i need to upload the workbook bc I am not sure how someone here can upload an excel workbook.

Thank you in advance to all members of the Mr. Excel Forum, who will offere any type of idea or support.

Thank you

Jose V. Ramirez