how to validate formulas faster?

[bluefrog]

I get a excel book from a colleague
and I need to validate all the formulas,quite a lot of them
is there any fast way of doing it?

 

[Firefly2012]

Please define what you mean by "validate formulas" - what do you actually do to validate them? What kind of formulas are they?

 

[bluefrog]

I mean to check whether the logic of the formula in each cell is correct or not--to validate. Formula such as

=IF(B$58>0,+(-B$58*Overheads!$B$61)/12,0); =+Assets!C12+Assets!C29; =(C22-C24)*'Contract Costings'!$C$15; and a lot of such. <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[Firefly2012]

You can use Ctrl+` to toggle between cells displaying values/their underlying formulas. That might assist you in your work?

 

[Firefly2012]

One particular issue that I have dealt with previously is formulas that contain constants. I developed some code to help me identify such cells so that they could be checked more closely - perhaps it may be useful to you too.

If you have a workbook and you want to identify formulas where a constant value has been added/subtracted/multiplied/divided in to the formula of the form:

=SUM(A1:A10)+18.5

=23000 - VLOOKUP(A1,C1:D10,2,0)

then you can use the following code. It will create a new worksheet in the workbook of interest with a hyperlink to the formula(s) in column A and the respective formula string in column B.

You need to copy the code into a standard module and run it whilst the workbook of interest (ie the one with the formulas) is active. The code does not have to be in a module within the workbook of interest eg you could place it in your personal macro workbook.

Sub IdentifyConst()
Dim cell As Range, rng As Range
Dim regex
Dim i As Long
Dim wsNew As Worksheet
Dim xlCalc As XlCalculation

With Application
  xlCalc = .Calculation
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
  .EnableEvents = False
End With
Set wsNew = Worksheets.Add(before:=Sheets(1))
wsNew.Range("A1:B1").Value = Array("Cell", "Formula")
Set regex = CreateObject("vbscript.regexp")
For i = 2 To Worksheets.Count
  On Error Resume Next
  Set rng = Worksheets(i).UsedRange.SpecialCells(xlCellTypeFormulas)
  On Error GoTo 0
  If Not rng Is Nothing Then
    With regex
      .Pattern = "([\+\-\*\/]\d+\b)|(\=\d+\b)"
      For Each cell In rng
         If .test(cell.Formula) Then
           With wsNew
             .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Formula = "=HYPERLINK(""#'"" & """ & Worksheets(i).Name & _
             "'!" & cell.Address & """,""'" & Worksheets(i).Name & "'!" & cell.Address & """)"
            .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = "'" & cell.Formula
           End With
          End If
       Next cell
     End With
     Set rng = Nothing
  End If
Next i
With Application
  .Calculation = xlCalc
  .EnableEvents = True
End With
End Sub

 

[bluefrog]

Hi, I think the formula in my sheets don't mostly contain constant, but rather formula type, could help think of a way?BTW, pls regard your inbox message.

 

[bluefrog]

That option I have already put in use, somehow it doesn't allow me to go and validate/trace the quoted cells to its orgininal place. Btw, is there a way to convert all the sheets in a working back and forth btw formula/values?