Preventing corruption of dynamic range names on cell deletion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chrisso - 14 Sep 2007 12:00 GMT

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

Reply to this Message

JE McGimpsey - 14 Sep 2007 12:45 GMT

One way:

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

> Hi All
>
[quoted text clipped - 11 lines]
> Thanks in advance for any ideas,
> Chrisso

Reply to this Message