compare two excel cells with same formula

April 26, 2011 at 08:04:32
Specs: Windows 7
I would like to compare the formulas of two exactly the same work sheets in one file.( The results of the work sheets will differ because of different input.) If there are non equal of omitted formulas a message (e.g. "fault") must show.

See More: compare two excel cells with same formula

Report •

April 26, 2011 at 09:34:57
=IF(Sheet1!A1 <> Sheet2!A1, "Fault", "")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 27, 2011 at 02:37:50
Thank you for your reply. Can you please do this test? Enter a simple formula (e.g. =C1+D1) in Sheet1!A1now enter on Sheet1 a number 5 in C1 and a 6 in D1, the result in A1 will be 11. Enter the same simple formula (=C1+D1) on Sheet2!A1and now enter on Sheet2 a number 7 in C1 and a 8 in D1which will give a result of 15 in Sheet2!A1. Now write your formula (=IF(Sheet1!A1 <> Sheet2!A1, "Fault", "") in Sheet3!A1 and it will give a message of "fault" because 11 does not equal 15 which is correct, even though the formulas (=C1+D1) in cell A1 on both Sheets 1 and 2 are exactly the same. How can I compare the formula in Sheet1!A1 to Sheet2!A1? so that my answer on sheet3!A1 will be "same" and should it differ (say, =C1+E1) then "fault"
Regards Chris27

Report •

April 27, 2011 at 04:28:51
I'm confused.

Are you saying that you want a way to compare the 2 formulas themselves and not the results of the 2 formulas?

The only way to do that is with a User Defined Function written in VBA.

Place this code in a Standard VBA module and then enter =FormulaCheck() in any cell.

Function FormulaCheck()
  If Sheets(1).Range("A1").Formula <> _
     Sheets(2).Range("A1").Formula Then
   FormulaCheck = "Fault"
   FormulaCheck = "Same"
  End If
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

May 2, 2011 at 06:05:53
Thank you for the code to check/compare a formula on sheet1 and sheet2. I have entered the "=FormulaCheck" in cell A1 on sheet3
The code do check cell A1 of sheet 1 and 2 correct. Can you please modify/extend the code "FormulaCheck" to also check/compare ALL the cells on sheets 1 and 2.

Report •

May 2, 2011 at 09:35:38
If you wanted to compare all of the cells in the 2 worksheets, you should have asked for that. Since we can't read minds, all we can do is answer the question that is asked, which was to compare a single cell.

Comparing all of the formulas in a worksheet is a bit more difficult and doesn't lend itself to using a UDF. I suggest a macro can be run against Sheets 1 & 2, comparing the sheets cell by cell over the UsedRange and checking the formulas.

Since I assume that some cells will contain formulas and others will not, the code only returns Fault or Same if at least one of the 2 cells it is comparing contains a formula. For example:

- If Sheet1!B3 and Sheet2!B3 both contain constants or text or anything else that is not a formula, the code will return "No Formula".

- If both cells contain a matching formula the code will return "Same".

- If both cells contain formulas but they don't match, or if only one of the 2 cells contains a formula, then the code will return "Fault".

The code also assumes that that the UsedRange in Sheet1 is at least as big as the UsedRange in Sheet2 since it uses the UsedRange in Sheet1 to determine how many cells to compare.

Note: The first line of this code will clear the contents of Sheet3 so that you get fresh data each time you run it. Make sure that you don't have anything in Sheet3 that you want to keep.

As always, I suggest that you test this code in a backup copy of your workbook since macros can not easily be undone.

Sub ChkFormulas()
  For Each myCell In Sheets(1).UsedRange
    If Sheets(1).Range(myCell.Address).HasFormula Or _
       Sheets(2).Range(myCell.Address).HasFormula Then
     If Sheets(1).Range(myCell.Address).Formula <> _
       Sheets(2).Range(myCell.Address).Formula Then
       Sheets(3).Range(myCell.Address) = "Fault"
       Sheets(3).Range(myCell.Address) = "Same"
     End If
       Sheets(3).Range(myCell.Address) = "No Formula"
    End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 4, 2011 at 10:05:13
Thank you for your help, I really appreciated it, I have successfully compared the formula on two sheets. My problem is solved.
My VBE is set to option explicit and I had to insert a Dim Mycell as Variant to declare the variable, I dont know if this is correct, but i got it to work with this.
Can you modify the "chkformula" to use the sheet names that I have eg the sheet name with the correct formula is April-10 and I want to compare my other sheets Jun-10 ,Jul-10, Aug-10 ect. to April-10 in succession?

Report •

May 4, 2011 at 13:28:27
Since you keep adding requirements to this task, I'm going to politely decline your request to modify the code (again).

As I mentioned in my last response, it's cumbersome to have to keep modifying code, setting up test workbooks, testing the code, posting it, explaining it, etc.

Here's a suggestion: The next time you post a question is a Help forum such as this one, don't post your requirements in little pieces. Put all of your requirements in one post so that a complete answer can be provided the first time.

Imagine if you were a carpenter and someone asked you to build them a cabinet. Then, after you cleaned up the work area and put all your tools away, they asked you add drawers to the cabinet. Then, after you cleaned up the work area and put all your tools away, they asked you add glass in the doors. That's kind of what you are doing when you tell us your requirements bit by bit after a bunch of work has already been done.

I think this would be a great learning opportunity for you and a chance to expand your VBA knowledge. All you need to do add a For-Next loop in the code to check each sheet in sequential order.

Good Luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 5, 2011 at 01:55:52
Apology for any irritation, I have learned a lot.
Thank you so much for your time and effort, your patience, advice and even still a proposal, I really appreciate it.
I will work on the For-Next loop and will get it right.

Report •

Ask Question