 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007Auditing formulas month over month
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. 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 > I tried a few searched, but had no luck in finding a solution. > [quoted text clipped - 17 lines] > > Any help would be appreciated. > 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 > > 1) Upon creation of spreadsheet, audited all formulas to make sure > > that are calculating as expected. [quoted text clipped - 32 lines] > > 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. > 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. [quoted text clipped - 4 lines] > 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 ? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.