Conditional format that higlights differing data on two worksheets

  • Thread starter Thread starter jaggy
  • Start date Start date
J

jaggy

I have a workbook that contains a worksheet for a single week of any
given month and in the sheet I have an individual's time reported for
each day of the week. I have a second workbook that contains an
individual's time reported by each day for the entire month. I would
like to compare the two to determine if there is a mismatch and
highlight those cells.

The logic goes something like this:
(1) I need to match person A in column C of workbook1 to the same name
in column C in workbook2.
(2) I then need to match the date of the month on workbook 1 & 2 for
person A in step #1.
(3) I then want to lookup the values for person A on May X that they
reported on workbook 1&2 and highlight the time reported values that
do not match.

The point is to compare a weekly workbook against a monthly workbook
and see if person A has gone back after the week has ended and updated
his time in workbook2 (the monthly view).

Ex: Bob has reported 7 hrs on May 15 but goes back later in the month
to update the hrs worked to 8hr for May 15th.

Is there a formula that will do this calculation and allow me to
conditionally highlight the cells that do not match.

Thanks in advance,
jag
 
I have a workbook that contains a worksheet for a single week of any
given month and in the sheet I have an individual's time reported for
each day of the week.  I have a second workbook that contains an
individual's time reported by each day for the entire month.  I would
like to compare the two to determine if there is a mismatch and
highlight those cells.

The logic goes something like this:
(1) I need to match person A in column C of workbook1 to the same name
in column C in workbook2.
(2) I then need to match the date of the month on workbook 1 & 2 for
person A in step #1.
(3) I then want to lookup the values for person A on May X that they
reported on workbook 1&2 and highlight the time reported values that
do not match.

The point is to compare a weekly workbook against a monthly workbook
and see if person A has gone back after the week has ended and updated
his time in workbook2 (the monthly view).

Ex: Bob has reported 7 hrs on May 15 but goes back later in the month
to update the hrs worked to 8hr for May 15th.

Is there a formula that will do this calculation and allow me to
conditionally highlight the cells that do not match.

Thanks in advance,
jag
Click to expand...

Jag,

it would help to know where the dates are stored in your workbooks but
the formula below assumes DATE in column A,Hours in Column B and Name
in Column C of both sheets

=SUMPRODUCT(([Book1]Sheet1!$C$1:$C$3=C2)*([Book1]Sheet1!$A$1:$A$3=A2)*
[Book1]Sheet1!$B$1:$B$3)

it returns the number of hours reported in book1 with that name and
date.

HTH

Regards

David
 
I don't seem to be able to get either formulas to work.

Some additional info:

I am on Excel 2003. The format of the data is as follows.
Date: Rows G3:M3
Name: Column F4:F100
Hrs: G5:G100

=SUMPRODUCT((Wk_2!F4:F100=F4)*(Wk_2!G3:M3=G3)*(Wk_2!G5:G100)

Is there anything I missed?

Thanks again
jag
 
Back
Top