Formula for comparing two workbooks and highlighting the differenc

[Guest]

Hello,

I need some help. I run a daily report that generates a spreadsheet. I
would like to compare yesterdays spreadsheet with todays spreadsheet and have
the differences highlighted on todays spreadsheet.

The row and column format are exactly the same on the spreasheets. However,
data in the individual cells may or may not change.

Thanks!
Charles C.

 

[shail]

Hi Charles,

You need to use the Conditional Formating

1. On yesterday's sheet name the range by Insert/Name/Define (say
"myRange".
2. On the today's spreadsheet at A2, enter the function on the
Conditional Formating at "Formula Is" box as

=countif(myRange,A2)=0

3. Click Format button and at pattern select a colour to display (say
Red)
4. Click Ok
5. Copy A2, select the range and right click the mouse button to Paste
Special and click Formats.

Hope the steps are clear to you.

If there is any difference in Yesterday's data and Today's data you
will get Red coloured cell to tell you "here is the difference".

Hope this works for you.

Thanks,

Shail

 

[Guest]

Thank you Shail for the info...

I am having a little trouble. Do these sheets need to be worksheets within
the same workbook? Or can they be worksheets in different workbooks?

I tried both and it does not seem to work. What am I doing wrong???

 

[Guest]

Shail,

I think I found what the problem is. I do not think I am naming the cells
correctly. I I highlight the range of cells on yesterday's sheet
(=yesterday!$A$2:$R$128) then Insert/Name/Define "myRange". However, when I
click on each cell it shows the cell location e.g. A2, C3, etc.. as opposed
to myRange. Should it show myRange instead of the actual cell location?

 

[Guest]

Shail, never mind my earlier post....operator error. Your suggestion works
GREAT!!!! Thanks!!!!!

 

[shail]

Hi Charles,

That's great. I too did it for the first time for you and was searching
for the mistake I have done.

Thanks for the feedback

Shail