Home / Analysis / Finding Cells Overwritten with Numbers

# 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.

The macro for the function is shown off to the right. You set it up just like a macro but instead of saying Macro, you say Function. Then to use this macro function, you simply do =CELLFORMULA(cell ref) and you will see the formula, starting with =.

## Function CellFormula(Rng As Range) As String CellFormula = Rng.Formula End Function

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: