Auditing formulas month over month

[pbogle]

I tried a few searched, but had no luck in finding a solution.

We have a few very complex spreadsheets, and am looking to find a way
to ensure completeness and accuracy of the spreadsheets month over
month. Currently we do the following:

1) Upon creation of spreadsheet, audited all formulas to make sure
that are calculating as expected.
2) Month over month we select a sample of row to ensure that the
formulas match the baselined formulas. This is done manually.

What I would like to do is change part 2 so that we just create a
worksheet that automatically checks the formulas against the
baseline.For example, the baselines formula might be something like
=IF(OR(L3<>0,C3="Index"),0,O3). Every month, I would like to check to
make sure all cells in the column match that same formula. I thought
it might be possible using conditional formatting, but am having
trouble getting it implemented. If it's not possible using
conditional formatting, maybe this would be possible in VB?

Any help would be appreciated.

 

[Pete_UK]

I'm not quite sure what you want to do, but if you use a formula to
check a formula, won't that also need to be audited?

Apart from new data, do the sheets change from month to month? If they
are essentially the same, then can't you audit one thoroughly and then
set this up as a master sheet or template, protecting formulae from
change?

Pete

 

[sysmod]

1) Upon creation of spreadsheet, audited all formulas to make sure
that are calculating as expected.
2) Month over month we select a sample of row to ensure that the
formulas match the baselined formulas. This is done manually.

Very good work practices!

Every month, I would like to check to
make sure all cells in the column match that same formula. I thought
it might be possible using conditional formatting, but am having
trouble getting it implemented. If it's not possible using
conditional formatting, maybe this would be possible in VB?

Simplest is to add other mathematical cross-checks doing the same calc
a different way and show a msg if they disagree.

You could use CF if you use a VBA function that returns the formula,
like
Function GetFormulaR1C1(Cell as Range) as String
GetFormula = Cell.FormulaR1C1
End Function

and format the cells to match.

It would be better to investigate WHY the formulas might ever be
different, and correct THAT.
For example, protect the sheet, unprotecting only input cells.
Use an array entry so the formula block can only be changed as a
whole.
Add a VBA self-check for formula integrity on each workbook save
event.
Use the many enterprise control tools like Compassoft, ClusterSeven,
Prodiance, Lyquidity etc that perform these checks outside Excel using
system policies that users cannot alter.

http://www.sysmod.com/scc.htm

 

[pbogle]

Very good work practices!


Simplest is to add other mathematical cross-checks doing the same calc
a different way and show a msg if they disagree.

You could use CF if you use a VBA function that returns the formula,
like
Function GetFormulaR1C1(Cell as Range) as String
GetFormula = Cell.FormulaR1C1
End Function

and format the cells to match.

It would be better to investigate WHY the formulas might ever be
different, and correct THAT.
For example, protect the sheet, unprotecting only input cells.
Use an array entry so the formula block can only be changed as a
whole.
Add a VBA self-check for formula integrity on each workbook save
event.
Use the many enterprise control tools like Compassoft, ClusterSeven,
Prodiance, Lyquidity etc that perform these checks outside Excel using
system policies that users cannot alter.

http://www.sysmod.com/scc.htm

I have seen and worked with a few of those spreadsheet control tools
(Mainly Spreadsheet professional, and another I can't remember the
name of), and I don't think they would be very efficient in this
case. The spreadsheet is used to perform a number of calculations
based on the data at a current month. This data can change
frequently, so data that is relevant in August, might not be included
in September. Given this, we copy in all the data from a third-party
application, and then copy the formulas from the previous month.
While it works well so far, due to the manual nature at some point I
predict formula errors will arise.

I think the most efficient solution would probably be to do the VBA
checking, I just figured there might be a way to put the expected
formulas in Row 2, and then using conditional formatting check all
other rows against those formulas, highlighting any time the formula
is not equal.

 

[sysmod]

I have seen and worked with a few of those spreadsheet control tools
(Mainly Spreadsheet professional, and another I can't remember the
name of), and I don't think they would be very efficient in this
case.

The ones I mentioned are much more enterprise tools than SP or other
auditing addins. Google for them.

The spreadsheet is used to perform a number of calculations
based on the data at a current month. This data can change
frequently, so data that is relevant in August, might not be included
in September.

As I understand it, it's not the data you want to check, but the
formulas.
But reading on...

Given this, we copy in all the data from a third-party
application, and then copy the formulas from the previous month.

Unless you add new formulas each month?
If so, could you prefill in all the formulas for the year, and then
lock the sheet?

While it works well so far, due to the manual nature at some point I
predict formula errors will arise.

I think the most efficient solution would probably be to do the VBA
checking, I just figured there might be a way to put the expected
formulas in Row 2, and then using conditional formatting check all
other rows against those formulas, highlighting any time the formula
is not equal.-

How do you get on with the way I suggested - using GetFormulaR1C1 ?