Checking & Validating Excel Formulas
Posted on 2012-03-15
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
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)