Finding Cells Overwritten with Numbers

Aaarrrggghhh!!! She overwrote the formulas with hard coded numbers and now the math is not working.

She has left for a position that has better growth potential and I am happy for her. So I took back the automated reporting that she was handling. She was actually doing it very well, but I must remember that not everyone is spreadsheet savvy.

So, for the last couple of weeks, I've been fixing errors caused by those overwrites, but I needed to fix them once and for all. And last week I hit upon an idea very similar to the one used in finding unprotected cells. The solution required writing a macro that would act like a function, just like SUM is a function. This macro function would show the formula of a cell rather than the contents or results of the formula.

Then, you set up a template that shows where the formulas should be. So spreadsheet A is the file with the correct set of formulas. Spreadsheet B will be the template that shows the correct layout of formulas versus numbers, labels and blanks. Spreadsheet C is the file that you suspect has been corrupted with numbers overwriting the formulas. And finally Spreadsheet D will compare the Spreadsheet C against the template Spreadsheet B.

Spreadsheet B will reference all of the relevant cells in spreadsheet A with a formula like: IF(LEFT(cellformula('[Spreadsheet A]Sheet1'!A1),1)="=","f","") where any cell with "f" means it contains a formula. The results of CELLFORMULA will always start off with = so checking for the equal sign means the cell contains a formula.

Now Spreadsheet D will compare the "corrupted" file Spreadsheet C against template Spreadsheet B to find all of the cells that contains numbers, texts or blanks when they should be filled with a formula. The cells in Spreadsheet D will contain the following formula:

IF(AND(LEFT(cellformula('[Spreadsheet C.xls]Sheet1'!A1),1)<>"=",'[Spreadsheet B.xls]Sheet1'!A1="f"),"x","") where "x" designates a cell that's been overwritten.

What will be the range of formulas that will contain CELLFORMULA and the IF formulas? It will be as large as you need it to be. You will copy the formulas found in A1 in Spreadsheet B and D and copy to the relevant cells. Make sure A1 is not absolute ($A$1).

If you have just one spreadsheet to check, then you are done, but if you have more than one, like I had 64 files, then you will need to do a macro to check all of the files. And that's a topic for another day and another post.