[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Checking & Validating Excel Formulas

Hello Experts,

I need to establish some validation and checking rules for formulas in an Excel sheet.

Specifically, I need to check that the row number in a given formula is the same as the actual row of the cell containing the formula, and be able to raise a flag if not.

So what I really need is a way (or perhaps a function) for reading the row refered to in the formula

For example, applying this function on below formula will give me 16

=SUM(Sheet2!H16:J16)

Assuming the function is called GetRowNumber() and the formula is in Cell B16, I need to place a checking formula on the same row like this:

=If(GetRowNumber(B16) <> ActiveCell.Row(), "Mismatch Row in formula", "Formula is OK")

The above is my suggested way for validating my formulas, please feel free to offer a better approach or best practice (if you will)

Thank you,
Hani
0
Mehawitchi
Asked:
Mehawitchi
  • 5
  • 4
1 Solution
 
StephenJRCommented:
Do your formulas vary? Or are all they of the form SUM(A1:B1)?
0
 
MehawitchiAuthor Commented:
Thank you Stephen for your query.

Below are 3 samples of the actual formulas:
=SUM(Corporate:Panorama!D57)
=SUM(Corporate:Panorama!E57)
=SUM(Corporate:Panorama!F65)

As you can see, only the column and row references vary, but the part "SUM(Corporate:Panorama!" is fixed.

Hope this helps.

Thanks again,
Hani
0
 
StephenJRCommented:
Hani - try this:
Function GetRowNumber(rIn As Range) As String

Dim s As String

s = rIn.Formula

With CreateObject("VBScript.RegExp")
    .Global = True
    .IgnoreCase = False
    .Pattern = "([A-Z]+)(\d+)"
    If CLng(.Execute(s)(0).Submatches(1)) = Application.Caller.Row Then
        GetRowNumber = "OK"
    Else
        GetRowNumber = "Wrong"
    End If
End With

End Function

Open in new window

So you would type =GetRowNumber(B16) in a cell etc.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MehawitchiAuthor Commented:
Hi Stephen,

I don't think the function is working properly. The result is always "Wrong"  whether or not the formula is right.

I tried executing the "CLng(.Execute(s)(0).Submatches(1))" statement in the debugger's Immediate window but I'm getting a compilers error

Did it work at your end?
0
 
StephenJRCommented:
Yes worked for me. Can you post a small workbook?
0
 
MehawitchiAuthor Commented:
Please check column "P" in "Total" sheet
Sample-File.xlsm
0
 
StephenJRCommented:
Try just changing line 10 to
.Pattern = "(\![A-Z]+)(\d+)"

Open in new window

0
 
MehawitchiAuthor Commented:
Thank you Stephan - It worked perfectly
0
 
StephenJRCommented:
My pleasure.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now