dynamic ranges


S

Sam

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
 
Ad

Advertisements

R

Rob van Gelder

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.
 
Ad

Advertisements

S

Sam

-----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





.

Thanks, Rob.

I'll try your suggestion and see what happens.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top