May 28 2021 08:21 AM
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.
May 28 2021 09:01 AM
SolutionThe 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]]
May 28 2021 09:14 AM
= Table1[#All]
will return headers and the totals row. For calculation
= Table1
is more useful.
May 28 2021 09:46 AM
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.
May 30 2021 11:06 PM
May 31 2021 03:53 AM
You are welcome. But again, it's better to use native =Table1[#All]
May 28 2021 09:01 AM
SolutionThe 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]]