Comparing intersecting cell on two different sheets - one non-pivot to a pivot table

[ExcelHelp9012]

Hello all,

Sorry for the crazy thread title. I'm not sure how to best word this. I've got some data that I need to be able to compare between two different worksheets. One worksheet has the data going in the wrong direction, so I've used a pivot table to change the way the data is laid out.

I've got the two sheets pretty close now in terms of the way the data is laid out but the first sheet will not be a pivot table and the other sheet to compare against will be a pivot table if that matters.

On Sheet 1 columns I have

Name | Date1 | Date2 | Date 3| etc

For Sheet 1 rows I have

Value for name(ex John) | A numerical value (ex 8)


On Sheet 2 (the pivot table) I have basically the same exact column/rows.

What I need to do is find the intersection of Name/Date in Sheet 1 and find the value in the intersecting cell (always a number, let's say 8) - then I need to go into Sheet 2 and find the same Name/Date intersection and compare to the value in that cell. I want to highlight RED if the values to not match.

An example could be on Sheet 1:
* John has 8 for 1/1/2023
On Sheet 2 (the pivot table)
* John has 0 for 1/1/2023

I would want this to be highlighted in red since they do not match.

I'm sorry for the clunky description here and I'm more than happy to explain further if need.

Thank you so much for your time and guidance here.

[ExcelHelp9012]

Here is an example sheet for illustration.

In sheet 1 we have a name column followed by dates. For each date there is a numerical value representing hours logged.

In sheet 2 we have a name column, an hour column and a date column. Notice how the data is listed vertically rather than horizontally

So, in the third sheet, named 'PivotSheet2' I'm making the data in sheet2 match the formatting of the data in sheet 1.

What I now need is the ability to do a lookup on a name/date combination (between Sheet 1 and PivotSheet2) and highlight any values in the PivotSheet2 where the numerical value doesn't match Sheet 1 for that same name/date intersection.

In this example Tim has 7 hours listed on Sheet 1 for Dec 2nd 2022 but on PivotSheet2 Tim has 8 hours on Dec 2nd 2022. That means that the cell C6 should be red because it does not match the value in Sheet 1 for Tim/Dec 2nd intersection.

[JeteMc]

Hello ExcelHelp9012 and Welcome to Excel Forum.
To get conditional formatting to work in your pivot table, the applies to will need to be for All cells showing "Sum of Hours" values for "Name" and "Date".
The formula used is: =INDEX(Sheet1!$B$2:$Z$3,MATCH($A5,Sheet1!$A$2:$A$3,0),MATCH(B$4,Sheet1!$B$1:$Z$1,0))<>B5
Note that the reference to the data on Sheet1 will need to be changed if the data extends beyond column Z or row 3.
Note that the source data on Sheet2 is converted to an Excel table.
All of that said, Sheet2 is a proper data set and it is easier to compare two proper data sets than the data a displayed on Sheet1 and a pivot table.
As you are using the 365 edition of Excel you could use Get & Transform to produce a proper data set from the data on Sheet1.
If you are interested in doing that, let us know.
Also let us know if you have any questions.