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?

Answer 1

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.