Dynamic Range
 

Hi All,

I have a data table which starts from A5001 to A50674. This data will
expand by adding new rows and columns.

How can I name this data.

Also I would like to create separate names for each column that is
added.
I tried using =OFFSET($A$5001,0,0,COUNTA($A:$A),1), but it does not
recognize the data.

Can someone help me.

Thanks
vishnu

Dynamic Range
 

wrote in message
...
Hi All,

I have a data table which starts from A5001 to A50674. This data will
expand by adding new rows and columns.

How can I name this data.

Also I would like to create separate names for each column that is
added.
I tried using =OFFSET($A$5001,0,0,COUNTA($A:$A),1), but it does not
recognize the data.

Can someone help me.

Thanks
vishnu


Try it like this...

Include your sheet name:

=SheetName!$A$5001:INDEX(SheetName!$A$5001:$A$6553 6,COUNTA(SheetName!$A$5001:$A$65536))


--
Biff
Microsoft Excel MVP

Dynamic Range
 

"T. Valko" wrote...
....
Include your sheet name:

=SheetName!$A$5001:INDEX(SheetName!$A$5001:$A$655 36,COUNTA(SheetName!$A$5001:$A$65536))

Doesn't Excel automatically add the worksheet name if the defined name
refers to ranges in the same worksheet? That is, if you're in the
worksheet named A and define the name foo referring to

=$A$5001:INDEX($A$5001:$A$65536,COUNTA($A$5001:$A$ 65536))

doesn't Excel automatically convert this to

=A!$A$5001:INDEX(A!$A$5001:$A$65536,COUNTA(A!$A$50 01:$A$65536))

?

Then there's the usual caveat that if there were any blank cells in
A5001:A65536, the resulting dynamic range wouldn't span all the
nonblank cells. If these named ranges are meant to extend down to the
bottommost nonblank cell in their respective columns, it's always
safer to define them as

=$A$5001:INDEX($A$5001:$A$65536,MATCH(2,1/(1-ISBLANK($A$5001:$A
$65536))))