Let’s take the concept of named ranges a little further. Its true that the TABLE capabilities in later versions of excel give you much more opportunity to organize your data than before, but if we want many people to use our worksheets we cant rely on people have the latest versions.
How to use Named with column headings.
1 |
=OFFSET('complicated sheetname'!$A$2,0,0,COUNT('complicated sheetname'!$A$2:$A$10000),1) |
Set up this date to follow along in a worksheet called ‘complicated sheetname’
We used this data on the right to introduce named ranges | ![]() |
Let’s change that and add another column and some headings | ![]() |
You are first going to define a named range, MyExpensesBlock that will describe the whole block of data, including the titles
1 |
=OFFSET('complicated sheetname'!$A$1,0,0,COUNTA('complicated sheetname'!$A:$A),COUNTA('complicated sheetname'!$1:$1)) |
Lets break that down – will calculate the number of rows. Try it in an unused cell answer should be 4.
1 |
=COUNTA('complicated sheetname'!$A:$A) |
.. and the number of columns. Try it in an unused cell answer should be 2.
1 |
=COUNTA('complicated sheetname'!$1:$1) |
. once defined check the number of rows in your newly defined named range. Should be 4.
1 |
=ROWS(MyExpensesBlock) |
.. and columns should be 2
1 |
=COLUMNS(MyExpensesBlock |
Now, wherever you are in your workbook, you can just refer to ‘MyExpensesBlock’, and it will be the size and shape of the data in it. You can just forget all about the actual physical location as we go ahead and define named ranges for the individual data items.
Dynamic columns of data
1 |
=OFFSET(myExpensesBlock ,0,0,1) |
1 |
=OFFSET(myExpensesBlock ,1,MATCH("Expenses",myExpensesHeadings,0)-1,rows(myexpensesb |
1 |
=MATCH("Expenses",myExpensesHeadings,0)-1 |
1 |
=rows(myexpensesblock)-1 |
1 |
=OFFSET(myExpensesBlock ,1,1 ,3,1) |
1 |
=ROWS(MyExpenses) |
1 |
=COLUMNS(MyExpenses) |
1 |
=OFFSET(myExpensesBlock ,1,MATCH("Items",myExpensesHeadings,0)-1,rows(myexpensesblock)-1,1) |
Now we have everything defined – so what?
1 |
=SUM(MyExpenses) |
1 |
=COUNTA(MyExpensesItems) |
1 |
=Offset(MyExpensesItems,-1,0,1,1) |