Have you ever encountered this 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, you get 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, the reference to the worksheet is 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 down to the next row the references would generally increase by 1 (i.e. 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.