#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default 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))))
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic Range tiptoe Charts and Charting in Excel 0 February 19th 08 04:28 PM
Dynamic SUM range Carl Excel Worksheet Functions 8 October 30th 07 09:03 AM
Dynamic range prakash Excel Discussion (Misc queries) 2 September 11th 06 12:24 PM
Dynamic Range with unused formula messing up x axis on dynamic graph [email protected] Charts and Charting in Excel 2 February 2nd 06 09:02 PM
Dynamic range - is there a better way?... Jon Excel Worksheet Functions 0 November 9th 05 09:09 PM


All times are GMT +1. The time now is 09:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"