Compare formula
 

I have a spreadsheets with 3 worksheets
Sheet 1 holds all the data, calculations etc etc and is 1000 rows x 30 columns
Sheet2 is a library which picks up calc1 from sheet1 - colG (1000 rows x 2
columns)
Sheet3 is the same library which picks up calc2 from sheet1 - ColH (100 rows
x 2 columns)

I need to check that the formula on sheet2, column B link to colG in in
sheet1 & on sheet2,ColB link to colH

The results may sometimes be the same so I cannot simply compare the answers

Ideally I want =if(formula!sheet2!B2 = formual!sheet3!b2,"ERROR")

Any ideas?

Compare formula
 

I don't think you can do this using formulas. You need a macro.

You can get the formula in a cell (B1 on Sheet 1) by using
Range("Sheet1!B1").Formula

Try this macro..
Sub Loop6()

' This loop repeats for a fixed number of times determined by the number of
rows

' in the range
' Ideally I want =if(formula!sheet2!B2 = formual!sheet3!b2,"ERROR")
Dim i As Integer
Sheets("Sheet2").Activate
Range("Sheet2!B2").Select
'LastRow = 20
LastRow = Selection.CurrentRegion.Rows.Count
For i = 1 To LastRow - 1
celladdr1 = "Sheet1!B" & i
celladdr2 = "Sheet2!B" & i
celladdr3 = "Sheet3!B" & i
If (Sheets("Sheet1").Range(celladdr1).Formula =
Sheets("Sheet2").Range(celladdr2).Formula) Then
Range(celladdr3).Value = "'" & Range(celladdr1).Formula
Else
Range(celladdr3).Value = "ERROR"
End If
Next i
End Sub

"Saintsman" wrote:

I have a spreadsheets with 3 worksheets
Sheet 1 holds all the data, calculations etc etc and is 1000 rows x 30 columns
Sheet2 is a library which picks up calc1 from sheet1 - colG (1000 rows x 2
columns)
Sheet3 is the same library which picks up calc2 from sheet1 - ColH (100 rows
x 2 columns)

I need to check that the formula on sheet2, column B link to colG in in
sheet1 & on sheet2,ColB link to colH

The results may sometimes be the same so I cannot simply compare the answers

Ideally I want =if(formula!sheet2!B2 = formual!sheet3!b2,"ERROR")

Any ideas?

Compare formula
 

Thanks for the quick response

"Sheeloo" wrote:

I don't think you can do this using formulas. You need a macro.

You can get the formula in a cell (B1 on Sheet 1) by using
Range("Sheet1!B1").Formula

Try this macro..
Sub Loop6()

' This loop repeats for a fixed number of times determined by the number of
rows

' in the range
' Ideally I want =if(formula!sheet2!B2 = formual!sheet3!b2,"ERROR")
Dim i As Integer
Sheets("Sheet2").Activate
Range("Sheet2!B2").Select
'LastRow = 20
LastRow = Selection.CurrentRegion.Rows.Count
For i = 1 To LastRow - 1
celladdr1 = "Sheet1!B" & i
celladdr2 = "Sheet2!B" & i
celladdr3 = "Sheet3!B" & i
If (Sheets("Sheet1").Range(celladdr1).Formula =
Sheets("Sheet2").Range(celladdr2).Formula) Then
Range(celladdr3).Value = "'" & Range(celladdr1).Formula
Else
Range(celladdr3).Value = "ERROR"
End If
Next i
End Sub

"Saintsman" wrote:

I have a spreadsheets with 3 worksheets
Sheet 1 holds all the data, calculations etc etc and is 1000 rows x 30 columns
Sheet2 is a library which picks up calc1 from sheet1 - colG (1000 rows x 2
columns)
Sheet3 is the same library which picks up calc2 from sheet1 - ColH (100 rows
x 2 columns)

I need to check that the formula on sheet2, column B link to colG in in
sheet1 & on sheet2,ColB link to colH

The results may sometimes be the same so I cannot simply compare the answers

Ideally I want =if(formula!sheet2!B2 = formual!sheet3!b2,"ERROR")

Any ideas?