Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range | Charts and Charting in Excel | |||
Dynamic SUM range | Excel Worksheet Functions | |||
Dynamic range | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic range - is there a better way?... | Excel Worksheet Functions |