Forum Discussion
Solved
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 D...
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]]
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]