Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamically Naming a Table of Data
Probably a stupid question.
I can dynamically name a columular data range using the InsertNameDefine =OFFSET(Sheet!$A$1,0,0,COUNTA(Sheet!$A:$A) function. This allows me to add data to a list and the named range automatically takes the new data into account, very handy indeed. Problem is I need to apply the same dynamic range to a table of data, not just a single column, so it needs to recognise new data in the rows below and to columns to the right of the named range. I tried changing the COUNTA(Sheet$A:A) part of the formula to take the other columns of data into account (I.e. COUNTA(Sheet$A:ZZ) but it doesn't recognise this as a valid range. Any idea how to do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamically Naming a Table of Data
Hi
The formula for single-column named range is like =OFFSET(Sheet!$A$1,0,0,COUNTA(Sheet!$A:$A) ,1) or with column headers in row 1 (as I prefer) =OFFSET(Sheet!$A$1,1,0,COUNTA(Sheet!$A:$A)-1 ,1) N.B. I anchored here OFFSET to cell A1, and then moved the reference poit 1 row down, to avoid the named range definition compromised whenever 1st data row in table is deleted - so long as you keep table header existent, the named range is OK. To define a table as named range dynamically, you better have at least one column in table, which is always filled whenever there are some data in same row. Usually it is the leftmost column in table, and serves as entry identifier too - so you can it use as key column whenever you need to use VLOOKUP function to get some data from this table. The counting of table rows when defining the table as dynamic range is done then on this column. P.e. with headers in row 1, the table starting from column A and having 10 columns, and the column A being the identifier =OFFSET(Sheet!$A$1,1,0,COUNTA(Sheet!$A:$A)-1 ,10) P.S. When there is no column in table which serves as identifier naturally, I usually add a counter column as leftmost into table, with formula which calculates row numbers for this table. Arvi Laanemets "Ivor Davies" wrote in message ... Probably a stupid question. I can dynamically name a columular data range using the InsertNameDefine =OFFSET(Sheet!$A$1,0,0,COUNTA(Sheet!$A:$A) function. This allows me to add data to a list and the named range automatically takes the new data into account, very handy indeed. Problem is I need to apply the same dynamic range to a table of data, not just a single column, so it needs to recognise new data in the rows below and to columns to the right of the named range. I tried changing the COUNTA(Sheet$A:A) part of the formula to take the other columns of data into account (I.e. COUNTA(Sheet$A:ZZ) but it doesn't recognise this as a valid range. Any idea how to do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamically Naming a Table of Data
Include the width argument by counting row 1:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1)) -- Biff Microsoft Excel MVP "Ivor Davies" wrote in message ... Probably a stupid question. I can dynamically name a columular data range using the InsertNameDefine =OFFSET(Sheet!$A$1,0,0,COUNTA(Sheet!$A:$A) function. This allows me to add data to a list and the named range automatically takes the new data into account, very handy indeed. Problem is I need to apply the same dynamic range to a table of data, not just a single column, so it needs to recognise new data in the rows below and to columns to the right of the named range. I tried changing the COUNTA(Sheet$A:A) part of the formula to take the other columns of data into account (I.e. COUNTA(Sheet$A:ZZ) but it doesn't recognise this as a valid range. Any idea how to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically Changing HLOOKUP table | Excel Worksheet Functions | |||
Pivot Table Group Naming | Excel Discussion (Misc queries) | |||
how do i link to a pivot table to get data dynamically | Excel Worksheet Functions | |||
Naming a Pivot Table??? | Excel Discussion (Misc queries) | |||
Dynamically compare two slightly different copies of a table | Excel Worksheet Functions |