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

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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MehawitchiAuthor Commented:
Thank you Stephan - It worked perfectly
0
StephenJRCommented:
My pleasure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.