Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

dynamic ranges

0 views

Skip to first unread message

unread,

Jan 21, 2005, 2:39:09 AM1/21/05

Hello everyone,

I've a question about dynamic ranges.

I can use something like this in a cell formula to create
a dynamic range reference -

INDIRECT("Sheet1!A$2:A$"&COUNTA(Sheet1!A:A))

If I want to create a dynamic named range and use it in
the formula instead, I use this -

Insert/Name/Define

Name - List

Refers to: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A))

So my question is, aren't both of these formulas
interchangable? I always use the OFFSET function for named
ranges (I learned to do it that way).

What advantage does one have over the other? (other than a
few keystrokes)

Thanks

unread,

Jan 21, 2005, 3:03:08 AM1/21/05

I believe there are differences with speed.

The big one I see is the reference doesn't move with inserted cells.

Try this:
Right-click column A, select Insert.

Now look at your two named range examples.

"Sam" <anon...@discussions.microsoft.com> wrote in message
news:145201c4ff8c$4b4d2db0$a601...@phx.gbl...

unread,

Jan 21, 2005, 2:46:46 PM1/21/05

>.
>

Thanks, Rob.

I'll try your suggestion and see what happens.

0 new messages