MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Comparing formulae in a column

Posted by Xayvian on February 07, 2002 12:26 AM

How do I check that a formula has been copied down correctly in a column?

ie. If I enter a formula in cell a1 and copy it down to cells a2 through a10, how can I check later that cell a8 hasn't been changed?

Posted by Tom Urtis on February 07, 2002 1:14 AM

Here are 3 suggestions, in reverse order of practicality:

(1) Create a macro that loops through your range and flags you if it returns a False ID, such as (note, not correct syntax, just a concept overview, and not worthy of first choice):
If ActiveCell.HasFormula Then
MsgBox ("Formula")
Offset(1, 0)

(2) Highlight your range of interest and click on Edit > GoTo > Special > select "Formulas", and hit OK. THis will select all the cells in your range with formulas, and you can see that cells not selected are possible culprits.

(3) Most effective, lock your formula cells and password protect the worksheet, so you don't have to worry about this in the first place.

Tom Urtis

Posted by Xayvian on February 07, 2002 9:18 AM

Thanks Tom,

I checked out the Edit, Go To , Special menu and the feature I want is Column differences. I often have to check someone else's spreadsheets and can use this feature to make sure he/she has copied a formula down correctly and not made a change to one of the cells half way down the column.