Formulas Equal?

[Richard]

OS: XP
Excel 2000

I need to check (2) two different spreadsheets in (2) two different
workbooks to see if all the formulas are equal.

I’m using an IF statement.

=IF('[Dashboard Kent County.xls]Capacity Violations'!$A$1:$H$31='Capacity
Violations'!$A$1:$H$31,"Yes","No")

Even if I change the formulas the statement still returns Yes.

Please Help.

Thanks in Advance

 

[AKphidelt]

Try putting

=IF(SUM('[Dashboard Kent County.xls]Capacity
Violations'!$A$1:$H$31)=SUM('Capacity
Violations'!$A$1:$H$31),"Yes","No")

 

[Richard]

I will try this later but what I am checking are the formulas and not the
values.

I will try this thanks.
--
Richard

Try putting

=IF(SUM('[Dashboard Kent County.xls]Capacity
Violations'!$A$1:$H$31)=SUM('Capacity
Violations'!$A$1:$H$31),"Yes","No")

OS: XP
Excel 2000

I need to check (2) two different spreadsheets in (2) two different
workbooks to see if all the formulas are equal.

I’m using an IF statement.

=IF('[Dashboard Kent County.xls]Capacity Violations'!$A$1:$H$31='Capacity
Violations'!$A$1:$H$31,"Yes","No")

Even if I change the formulas the statement still returns Yes.

Please Help.

Thanks in Advance

 

[Niek Otten]

Hi Richard,

Your formula will check if the results of the formulas in A1 on both sheets are equal.
It will not check the whole range and it will not check the formulas. You need VBA for that.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| OS: XP
| Excel 2000
|
| I need to check (2) two different spreadsheets in (2) two different
| workbooks to see if all the formulas are equal.
|
| I'm using an IF statement.
|
| =IF('[Dashboard Kent County.xls]Capacity Violations'!$A$1:$H$31='Capacity
| Violations'!$A$1:$H$31,"Yes","No")
|
| Even if I change the formulas the statement still returns Yes.
|
| Please Help.
|
| Thanks in Advance
|
| --
| Richard

 

[Niek Otten]

A simple way to do that:

Use this UDF. How to implement a UDF follows.

' ==========================
Function ShowFormula(a As Range)
If Application.ReferenceStyle = xlR1C1 _
Then ShowFormula = a.FormulaR1C1 _
Else: ShowFormula = a.Formula
End Function
' ==========================

================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hi Richard,
|
| Your formula will check if the results of the formulas in A1 on both sheets are equal.
| It will not check the whole range and it will not check the formulas. You need VBA for that.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| OS: XP
|| Excel 2000
||
|| I need to check (2) two different spreadsheets in (2) two different
|| workbooks to see if all the formulas are equal.
||
|| I'm using an IF statement.
||
|| =IF('[Dashboard Kent County.xls]Capacity Violations'!$A$1:$H$31='Capacity
|| Violations'!$A$1:$H$31,"Yes","No")
||
|| Even if I change the formulas the statement still returns Yes.
||
|| Please Help.
||
|| Thanks in Advance
||
|| --
|| Richard
|
|

 

[bj]

you will need a macro to do this type of check
what do you want for a result?

a brute force one would be something like
if J1 is empty and you are in theworkbook which is not dashboard etc.

Sub ckform()
d = 0
For r = 1 To 31
For c = 1 To 8
wb1 = "Dashboard Kent County"
Sh = "Capacity Violations"
If Workbooks(wb1).Sheets(Sh).Cells(r, c) <> Sheets(Sh).Cells(r, c) Then d =
d + 1
Next c
Next r
If d = 0 Then Sheets(Sh).Cells(1, 10) = "All formulas the same" Else
Sheets(Sh).Cells(1, 10) = d & " Formulas are different"
End Sub

You could add steps to tell you which but it sounds like you are only
interested whether they are different