I need to filter to a new workbook for exceptions of potential
mischarges, by comparing
a Table of Actual Charges Vs a Table of Allowed Charges by Employee
and Project.
The Table of Actual Charges may vary, plus or minus for Employees or
Projects.
..

Workbook A Sheet1 - Table of Allowed Charges.
Employee Project W Project X Project Y Project Z
Emp A Y Y
Emp B Y
Y <--- "Y" stands for allowed.
Emp C Y
Emp D
Y
..
Workbook B Sheet1 Table of Actual Charges [With a twist : One new
Employee, One new Project]
Employee Project V Project X Project Y Project Z
Emp A 140 20
Emp B 80 60
20
Emp C 70 90
Emp E 40
120
..

Worbook C Sheet1 Table of Potential Mischarges, resulting from
Workbook B Sheet1
Actual Charges, not matching Workbook A Sheet1 - Table of Allowed
Charges.
Employee Project V Project X Project Y Project Z
Emp B 80 60
Emp C 90
Emp E 40
120
..
From this result table, I would update Workbook A Sheet1, if charges
are justified.
Help appreciated
J.P.

I'm assuming that in all of your workbooks, your projects are along the top (Row1) , and all
employees along the side(column A).
In workbook C, enter all of your projects and all of your employees: Then in cell B2, enter the
formula

=IF(OR(ISERROR(MATCH(B$1,'[Workbook B.xls]Sheet1'!$1:$1,FALSE)),ISERROR(MATCH($A2,'[Workbook
B.xls]Sheet1'!$A:$A,FALSE))),"",IF(OR(ISERROR(MATCH(B$1, '[Workbook
A.xls]Sheet1'!$1:$1,FALSE)),ISERROR(MATCH($A2,'[Workbook
A.xls]Sheet1'!$A:$A,FALSE))),"",IF(INDEX('[Workbook A.xls]Sheet1'!$1:$10000,MATCH($A2,'[Workbook
A.xls]Sheet1'!$A:$A,FALSE),MATCH(B$1,'[Workbook A.xls]Sheet1'!$1:$1,FALSE))="Y","",INDEX('[Workbook
B.xls]Sheet1'!$1:$10000,MATCH($A2,'[Workbook B.xls]Sheet1'!$A:$A,FALSE),MATCH(B$1,'[Workbook
B.xls]Sheet1'!$1:$1,FALSE)))))

and copy it over and down to match your table. Increase the two references to $1:$10000 to match
your actual number of rows.

HTH,
Bernie
MS Excel MVP


"u473" wrote in message
...
I need to filter to a new workbook for exceptions of potential
mischarges, by comparing
a Table of Actual Charges Vs a Table of Allowed Charges by Employee
and Project.
The Table of Actual Charges may vary, plus or minus for Employees or
Projects.
.

Workbook A Sheet1 - Table of Allowed Charges.
Employee Project W Project X Project Y Project Z
Emp A Y Y
Emp B Y
Y <--- "Y" stands for allowed.
Emp C Y
Emp D
Y
.
Workbook B Sheet1 Table of Actual Charges [With a twist : One new
Employee, One new Project]
Employee Project V Project X Project Y Project Z
Emp A 140 20
Emp B 80 60
20
Emp C 70 90
Emp E 40
120
.

Worbook C Sheet1 Table of Potential Mischarges, resulting from
Workbook B Sheet1
Actual Charges, not matching Workbook A Sheet1 - Table of Allowed
Charges.
Employee Project V Project X Project Y Project Z
Emp B 80 60
Emp C 90
Emp E 40
120
.
From this result table, I would update Workbook A Sheet1, if charges
are justified.
Help appreciated
J.P.

Ok, it works fine, thank you. I was worried with that long formula. I
am impressed.
Of Course, in my Workbook C, before entering your formula, I had
entered all new Employees and Projects.
In actual practice, how would I filter all new Employees in Col. A
and new Projects in Row 1 in Workbook B to update Workbook A before I
read the resulting exceptions in Workbook C.
Thank you again,

J.P.

Perhaps, only put the employees and projects of interest into workbook C....

HTH,
Bernie
MS Excel MVP


"u473" wrote in message
...
Ok, it works fine, thank you. I was worried with that long formula. I
am impressed.
Of Course, in my Workbook C, before entering your formula, I had
entered all new Employees and Projects.
In actual practice, how would I filter all new Employees in Col. A
and new Projects in Row 1 in Workbook B to update Workbook A before I
read the resulting exceptions in Workbook C.
Thank you again,

J.P.