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

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.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Sam" wrote in message
...
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

-----Original Message-----
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.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Sam" wrote in
message
...
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



.


Thanks, Rob.

I'll try your suggestion and see what happens.