Have you ever encountered this Excel problem ...
... You use a function in one cell and it works perfectly. However, when you attempt to copy the function down to other rows, you get the
#REF! error arises when Excel is attempting to reference an invalid cell.
If you are copying a formula that works, down to other rows, the
#REF! error suggests that you are accessing cells that don't exist.
This will occur if you have referenced an entire worksheet by clicking on the grey square at the top left of the worksheet. In this case, the reference to the worksheet is
1:1048576(in recent versions of Excel) or
1:65536 (in Excel 2003). As these references are Relative References, Excel automatically increases the row references when this cell is copied down to other rows in the spreadsheet.
Therefore, when a cell containing the reference
1:1048576 is copied down, the row references automatically increase to
Similarly for Excel 2003, when a cell containing the reference
1:65536 is copied down to other rows, the row references are automatically increased to
As these increased row numbers (
1048578 in recent versions of Excel or
65538 in Excel 2003) don't exist, this causes the
#REF!error to be generated.
Check the formula in the first cell, that works. If you are referencing the cell range
1:1048576in a recent version of Excel (or
1:65536 in Excel 2003), then either:
$1:$1048576in recent versions of Excel or
$1:$65536in Excel 2003).
A$1:D$100) - but ensure you use the $ signs before the row numbers in this reference.