Dynamic Range

  • Thread starter Thread starter my.wandering.mind
  • Start date Start date
M

my.wandering.mind

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
 
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
Click to expand...

Try it like this...

Include your sheet name:

=SheetName!$A$5001:INDEX(SheetName!$A$5001:$A$65536,COUNTA(SheetName!$A$5001:$A$65536))
 
T. Valko said:
Include your sheet name:

=SheetName!$A$5001:INDEX(SheetName!$A$5001:$A$65536,COUNTA(SheetName!$A$5001:$A$65536))
Click to expand...

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$5001:$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))))
 
Back
Top