SOLVED

create dynamic range of table including headers and footers

Copper Contributor

Hello,

 

I'm using Office 365 on Windows 10.

 

I wanted to create a Dynamic Named Range of a Table called Table1, including it's Headers and Footers (Totals Row). By Default, when you enter the Dynamic Named Range of a Table(=Table1), you get just it's Data Rows and Columns and not even it's Headers.

 

I tried using the following Formula for the range definition in my Named Range, but Excel didn't like it:

 

=ADDRESS(MIN(ROW(Table1))-1,MIN(COLUMN(Table1))):ADDRESS(MAX(ROW(Table1))+1,MAX(COLUMN(Table1)))

 

This returns an error saying, are you writing a Formula?

 

Please help me come up with the correct Formula for this requirement.

5 Replies
best response confirmed by cliffordanup (Copper Contributor)
Solution

@cliffordanup 

The range consisting of the header row + the data rows is Table1[[#Headers],[#Data]]

The range consisting of the header row + the data rows + the total row is Table1[#All]

The range consisting of the data rows + the total row is Table1[[#Data],[#Totals]]

@cliffordanup 

= Table1[#All]

will return headers and the totals row.  For calculation

= Table1

is more useful.

@cliffordanup 

Back to your formula, ADDRESS() returns the text, not the reference. To convert on reference use INDIRECT() like

=INDIRECT(ADDRESS(MIN(ROW(Table1))-1,MIN(COLUMN(Table1)))):
 INDIRECT(ADDRESS(MAX(ROW(Table1))+1,MAX(COLUMN(Table1))))

which returns entire table. As already mentioned same as =Table1[#All]. The easiest way is to start typing =Table1[

After that drop-down menu suggests you all options - entire table, or only data, or only headers, or only totals.

Thank you for the correct Formula Sergei! It works now!

@cliffordanup 

You are welcome. But again, it's better to use native =Table1[#All]

1 best response

Accepted Solutions
best response confirmed by cliffordanup (Copper Contributor)
Solution

@cliffordanup 

The range consisting of the header row + the data rows is Table1[[#Headers],[#Data]]

The range consisting of the header row + the data rows + the total row is Table1[#All]

The range consisting of the data rows + the total row is Table1[[#Data],[#Totals]]

View solution in original post