Check for continuously in excel formulas

[peterandrewsuk]

Hi everybody,

Do you guys know a tool/method that makes it possible to check for continuously in excel formulas?

I have a big sheet with a lot of formulas and I would like to check, that I have not changed in some of these by mistake.

Basically I am looking for a tool that can check that the formulas are the same, and that it's only the columns/rows that changes.

Thanks in advance!

Best regards,
Peter.

[shg]

Theres a sub that does this at https://www.box.com/s/z6qv4zbz8xy17d5ziesn.

Copy the file to your desktop (or wherever) and then drag it into the VBA project for the workbook of interest.

From the comments in the code:

' ============================== S H E E T   M A P =============================
' shg 2010-09, 2010-11, 2013-01

' Creates a sheet map to characterize the contents of each cell with a color,
' and, for non-empty cells, a two-character code

'   Color:
'       Dark Grey   Empty
'       Light Grey  Formula
'       Yellow      A number or date stored as text
'       Red         An error
'       White       None of the above

'   First character => formula or literal:
'       L   A literal
'       F   A formula
'       <   A formula the same as that at left
'       ^   A formula the same as that above
'       +   A formula the same as those above and left

'   Second character => type of value:
'       $   String      ' from the type declaration character
'       @   Currency    ' from the type declaration character
'       #   Double      ' from the type declaration character
'       D   Date
'       E   Error

The code runs on the shortcut Ctrl+Shift+M (think "Conveniently Show Map")

[TMS]

A couple of things you could do.

1. Unlock the cells where you DO want to enter data; ensure that all cells with formulae or constant values are locked. Then protect the worksheet with a password.

2. Array enter the formulae in a column. Then you cannot change or delete an entry in that range. So, instead of putting the formula into a cell and dragging down, you would select the cells that need to accept the formula, type it into the first cell and use Ctrl-Shift-Enter to commit it.


Regards, TMS

[loginjmor]

Hi Peter -

There are a few things you can do:

1. Excel puts a little green triangle in the upper left corner of a cell that has a formula different from the surrounding cells. You might just scan for that.
2. You can go to Excel Options -> Advanced -> Display Options for This Worksheet -> Check the box that says "Show Formulas in Cells Instead of Calculated Results". Again, a visual scan to see any obvious errors. Don't forget to change this back to show results after you are satisfied the formulas are ok.
3. Depending on the formulas, you can recopy the template formula across whatever range of cells it applies to. For example if you have a formula "=A1*B1" in cell C1 you can simply copy it down as far as needed to overwrite any erroneous formulas
4.Go to your last backup of the file and copy all of the cells with formulas and paste them into your current worksheet, overwriting any erroneous cells. Probably use Paste Special -> Formulas
5. Once you get your formulas the way you want them, use the Protection features in Excel to prevent someone from inadvertently changing a formula, rather than data.
6. I think the later editions of Excel might have a Track Changes feature. I haven't used that, but if the spreadsheet is not used frequently, it would be a way to see if it has been changed.

I am not aware of any tools to check formulas to see if there are unwanted changes, but I am very far from the smartest person in this forum.

Hope the above helps.