Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to compare columns | Excel Discussion (Misc queries) | |||
Formula to compare 2 cells | New Users to Excel | |||
Help with Compare Date Formula | Excel Worksheet Functions | |||
compare formula | Excel Discussion (Misc queries) | |||
compare the value cell then use one formula if <= or another | Excel Discussion (Misc queries) |