Growing Named Range

[EricL]

Hi all, Great boards.
I tried to find this in old post but wasn't seeing it in first 100 so thought I would ask.
I am looking to have a formula in my define Named range that can grow as I add things.
I have a table and as it is its named range is
=Sheet1!a1:z100
Now if I insert a row it should adjust its self in the named I believe. What I would like is for it to just grow if I add row.
I thought a formula like
=sheet1!a1:z and counta(z:z)) to get last number would work, but it doesn't. Would this be better done in VBA?
I want to be able to grow the table in both in rows and columns.

Thanks !

Eric

 

[PaddyD]

Hi,

search for 'dynamic named range' not 'growing...'

Also, look at, e.g.:

http://www.tushar-mehta.com

or

http://www.ozgrid.com

where there are a number of example / tutorials. Post back with more if you need to..

Paddy

 

[EricL]

Thanks!
I will look at them.

 

[Aladin Akyurek]

See:

http://www.mrexcel.com/board/viewtopic.php?topic=16207&forum=2&7

 

[PaddyD]

On 2002-09-10 21:27, Aladin Akyurek wrote:

See:

http://www.mrexcel.com/board/viewtopic.php?topic=16207&forum=2&7

A fine choice

 

[Felafel]

i've just changed the range to a dynamic one and now it won't come up as an available column....any ideas?

 

[PaddyD]

post the formula you're using & a description of what you're trying to make it do.

Paddy