SOLVED

create dynamic range of table including headers and footers

%3CLINGO-SUB%20id%3D%22lingo-sub-2396084%22%20slang%3D%22en-US%22%3Ecreate%20dynamic%20range%20of%20table%20including%20headers%20and%20footers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2396084%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20Office%20365%20on%20Windows%2010.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wanted%20to%20create%20a%20Dynamic%20Named%20Range%20of%20a%20Table%20called%20Table1%2C%20including%20it's%20Headers%20and%20Footers%20(Totals%20Row).%20By%20Default%2C%20when%20you%20enter%20the%20Dynamic%20Named%20Range%20of%20a%20Table(%3DTable1)%2C%20you%20get%20just%20it's%20Data%20Rows%20and%20Columns%20and%20not%20even%20it's%20Headers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20using%20the%20following%20Formula%20for%20the%20range%20definition%20in%20my%20Named%20Range%2C%20but%20Excel%20didn't%20like%20it%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DADDRESS(MIN(ROW(Table1))-1%2CMIN(COLUMN(Table1)))%3AADDRESS(MAX(ROW(Table1))%2B1%2CMAX(COLUMN(Table1)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20returns%20an%20error%20saying%2C%20are%20you%20writing%20a%20Formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20me%20come%20up%20with%20the%20correct%20Formula%20for%20this%20requirement.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2396084%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2396268%22%20slang%3D%22en-US%22%3ERe%3A%20create%20dynamic%20range%20of%20table%20including%20headers%20and%20footers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2396268%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1065944%22%20target%3D%22_blank%22%3E%40cliffordanup%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20range%20consisting%20of%20the%20header%20row%20%2B%26nbsp%3Bthe%20data%20rows%20is%20Table1%5B%5B%23Headers%5D%2C%5B%23Data%5D%5D%3C%2FP%3E%0A%3CP%3EThe%20range%20consisting%20of%20the%20header%20row%20%2B%20the%20data%20rows%20%2B%20the%20total%20row%20is%20Table1%5B%23All%5D%3C%2FP%3E%0A%3CP%3EThe%20range%20consisting%20of%20the%20data%20rows%20%2B%20the%20total%20row%20is%20Table1%5B%5B%23Data%5D%2C%5B%23Totals%5D%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2396324%22%20slang%3D%22en-US%22%3ERe%3A%20create%20dynamic%20range%20of%20table%20including%20headers%20and%20footers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2396324%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1065944%22%20target%3D%22_blank%22%3E%40cliffordanup%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20Table1%5B%23All%5D%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewill%20return%20headers%20and%20the%20totals%20row.%26nbsp%3B%20For%20calculation%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20Table1%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eis%20more%20useful.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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]