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
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Do your formulas vary? Or are all they of the form SUM(A1:B1)?
Author 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
Commented:
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
So you would type =GetRowNumber(B16) in a cell etc.
Author 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?
Commented:
Yes worked for me. Can you post a small workbook?
Author Commented:
Please check column "P" in "Total" sheet
Sample-File.xlsm
Commented:
Try just changing line 10 to
.Pattern = "(\![A-Z]+)(\d+)"

Experts Exchange Solution brought to you by