Define Dynamic Range for a Table

Question

Background

I find myself quiet often in the situation where I want to define a dynamic range covering a whole table. I want enough flexibility in the sense that if I add more columns or rows or move the table, the range should be updated.

So what I do is basically:

Name the left upper cell of the table (start.table say)

Define the dynamic range of the table as follows:

  =OFFSET(start.table;0;0;
          COUNTA(INDIRECT(
                 SUBSTITUTE(ADDRESS(ROW(start.table);1);"$1";"") & ":" & 
                 SUBSTITUTE(ADDRESS(ROW(start.table);1);"$1";"")));
          COUNTA(INDIRECT(
                 SUBSTITUTE(ADDRESS(1;COLUMN(start.table));"$A";"") & ":" & 
                 SUBSTITUTE(ADDRESS(1;COLUMN(start.table));"$A";""))))

Explanation

ADDRESS gives me a string representation of the entire column / row of cell start.table
I need to SUBSTITUTE the $1 and $A part to get just the column or the row qualifier respectively
Then I can COUNTA the non empty cells in the INDIRECT range as referenced by the SUBSTITUTE string
With this approach the table stays properly defined even If I would add empty lines before/after the table or add columns or rows

Question

This approach seems to be quite on overkill and I was thinking whether I could not simply replace the whole INDIRECT(...) construct by $A:$A and $1:$1 respectively. First test would confirm that it works, but I was wondering whether I am overlooking something here? Do you see any potenital issues with this approach?

ManishChristian ManishChristian · Accepted Answer · 2017-02-22T16:06:10

This is what I use in name manager to define dynamic table:

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

This will cover all the rows and columns on your given sheet.

Define Dynamic Range for a Table

1 Answers