Forum Discussion
create dynamic range of table including headers and footers
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.
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]]
5 Replies
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!
You are welcome. But again, it's better to use native =Table1[#All]
= Table1[#All]
will return headers and the totals row. For calculation
= Table1
is more useful.
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]]
