One way:

=OFFSET(INDIRECT("Sheet1!A1"),,,COUNTA(Sheet1!$A:$ A),1)

In article . com,
Chrisso wrote:

Hi All

I have a simple dynamic range name with the formulae:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

When I delete cell A1 this range names becomes corrupt:

=OFFSET(Sheet1!#REF!,0,0,COUNTA(Sheet1!$A:$A),1)

This is very limiting and annoying! Is there anyway to get around this
problem?

Thanks in advance for any ideas,
Chrisso