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 displays 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 (in current versions of Excel). This is a reference to all the rows in the worksheet.
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 will automatically attempt to increase the row references.
Therefore, when a cell containing the reference 1:1048576 is copied from row 1 to row 2 of a spreadsheet, Excel will attempt to update this reference to 2:1048577.
However, as the row number 1048577 does not exist, this is an invalid reference, which causes the #REF! error to be generated.
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).