Compare sheet utility that can compare formula too

  • Thread starter Thread starter Adam2046
  • Start date Start date
A

Adam2046

I am using MS Excel 2003 runs in vista.
I need a utility program that can compare two worksheet, not only just the
value but the formula as well.

For instance:
File 1: cell a = 5, b = 5, c = 5 , d = a + b = 10

File 2: cell a = 5, b = 5, c = 5 , d = a + c = 10 (note that d having same
value as file 1 but formula is different)

Will it be more reliable to use microsoft office product rather than 3rd
party program? Any free MS product available for download?

Regards,
 
Adam,

It is easy enough to do it quickly by hand.

Copy the workbooks to preserve the originals, then on the two sheets that
you want to compare, select all the cells and run this macro:

Sub SAFormulaToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myCell.Formula = "'" & myCell.Formula
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub

Copy the two sheets into a new workbook, add a third blank sheet, and use a
formula like this in A1 of the blank sheet:

=IF(Sheet1!A1=Sheet2!A1,"","Difference")

And then copy that formula to a block large enough to compare the sheets of
interest. "Difference" will appear in any cell whose constant value or
formula is not the same on the two sheets.


HTH,
Bernie
MS Excel MVP
 
Dear Bernie,

Thank you very much for sharing your code.
I will try it out and let you know if im in doubt.

Regards,
 
Dear Adam,

I have tried and your function did work.
I am thinking of creating another function that will tell me which cells are
difference, rather than having to look thru the whole cell array (using Find
command) in the 3rd sheet.

Thank you.
 
Back
Top