This page addresses the following common Excel problem ...
You use a formula in one cell and it works perfectly. However, when you attempt to copy the formula down to other rows, it returns the #REF error.
The #REF! error arises when Excel is attempting to reference an invalid cell.
If you are copying a formula that works in row 1, down to other rows in a spreadsheet, the #REF! error may indicate that your formula contains relative references where absolute references should have been used.
For example, if you automatically insert a reference to an entire worksheet into a formula, by clicking on the grey square at the top left of the worksheet, Excel automatically inserts the reference 1:1048576 (or 1:65536 in Excel 2003).
As the reference 1:1048576 is a relative reference, when you copy the formula containing this reference down into other rows of your spreadsheet, Excel automatically increases the row references.
Therefore, when a cell containing the reference 1:1048576 is copied from row 1 to row 2 of a spreadsheet, the references will attempt to update to 2:1048577.
However, as the row number 1048577 does not exist, this causes the #REF! error to be generated instead.
Add the dollar symbols to change the reference to an absolute reference (e.g. change the reference 1:1048576 to $1:$1048576).
The $ signs in the above reference prevent Excel from adjusting the row references as the formula is copied to other rows (read more about this on the Excel Cell References page).