Dynamically Naming a Table of Data

[Ivor Davies]

Probably a stupid question.

I can dynamically name a columular data range using the Insert>Name>Define
=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?

 

[Arvi Laanemets]

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

 

[T. Valko]

Include the width argument by counting row 1:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))