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.
=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
=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.
.. and the number of columns. Try it in an unused cell answer should be 2.
. once defined check the number of rows in your newly defined named range. Should be 4.
.. and columns should be 2
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
Define MyExpenses as below
Let’s break that down – Find the column titled ‘Expenses’. Match returns a value 1-n, so we need to subtract 1 for use by offset, which starts counting from 0. Try in an unused cell. Answer should be 1
…the number of data rows would be the number of rows in the data block -1 for the header. Should be 3
Putting it all together it would translate as this – 3 rows of data, 1 column to the right, and one column down from the beginning of myExpensesBlock
=OFFSET(myExpensesBlock ,1,1 ,3,1)
.. once defined check the number of rows in your newly defined named range. Should be 3
.. and columns should be 1
Define MyExpensesItems as below, and repeat the tests above, substituting myExpensesItems for MyExpenses. Note the only difference between the two definitions is the heading title – “Items” instead of “Expenses”. You can see that it would be very easy to set up many columns of data like this, and since everything is derived from other dynamic named ranges, the only time you have to refer to actual cell references is in the initial definition of the Block of data.
Now we have everything defined – so what?
Now that you have verified that all this is working, goto another sheet and enter. The result should be 3
You can use negative offsets too. This should give you “Items”.