Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas Equal?
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. Im 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas Equal?
Try putting
=IF(SUM('[Dashboard Kent County.xls]Capacity Violations'!$A$1:$H$31)=SUM('Capacity Violations'!$A$1:$H$31),"Yes","No") "Richard" wrote: 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. Im 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas Equal?
I will try this later but what I am checking are the formulas and not the
values. I will try this thanks. -- Richard "AKphidelt" wrote: Try putting =IF(SUM('[Dashboard Kent County.xls]Capacity Violations'!$A$1:$H$31)=SUM('Capacity Violations'!$A$1:$H$31),"Yes","No") "Richard" wrote: 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. Im 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas Equal?
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 "Richard" wrote in message ... | 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas Equal?
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 InsertModule. 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 "Niek Otten" wrote in message ... | 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 | | "Richard" wrote in message ... || 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 | | |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas Equal?
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 "Richard" wrote: 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. Im 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can I make cell "yes" equal 1, "no" equal 0 | Excel Discussion (Misc queries) | |||
Is it possible to get formulas to equal certain object values in E | Excel Worksheet Functions | |||
Why do all my calculations/formulas equal zero? | Excel Discussion (Misc queries) | |||
Macro to copy range of formulas to equal data lines | Excel Worksheet Functions | |||
Getting 0 to equal 1 | Excel Worksheet Functions |