COUNTIF and the data range

LibreOffice 4.3.7 , Windows 7
The data has 1500 rows, and I used the data range $A$1:$A$1048576 for the formula =IF(COUNTIF($A$1:$A$1048576,A1)>1,“Y”,"") even it’s only 1500 rows , then I tried to add one row between row 1 and row 2 and got the error message “$REF!”.

If you insert a Row, the Range in formula expands to $A$1:$A$1048577 which is out of available Rows, →→ $REF-error

So it can’t be converted into $A:$A automatically like MS excel when the maximum number of rows have been reached.

@lolax: exactly