Refering to cells within a range name

I use range names for rows. How do I reference a specific cell in a named row?

By Neale Blackwood

When you have a range name that refers to a whole row, its value will depend on where you use the name. Assume row 2 is name Sales. If you enter =Sales into cell E5, the value displayed will be the value of cell E2.

It will match the corresponding column in row 2. This relationship applies to other sheets of the workbook as well.

Here are two techniques to refer to a specific cell within a named row range. They allow you to select a specific cell from the named range.

Still assuming you have a range named Sales that refers to Row 2. Cell A2 contains the text Sales. Cell B2 contains 100, C2 has 200 and D2 has 400. To refer to cell B2 you could use =INDEX(Sales,0,2). This will display 100. The 2 in the INDEX formula refers to the second column within the range. To refer to cell D2 you would use =INDEX(Sales,0,4)

Another technique is to name columns. Assume Sales is named as above and that column B is named Jul, column C is named Aug and column D is named Sep.
To refer to cell B2 you would use =Sales Jul. The single space between the two range names instructs Excel to find the intersection of the two ranges. =Sales Sep will refer to cell D2.

To easily name a range, first select your range, then click in the Name Box (the drop-down box to the left of the formula bar, above the column headings) and type the name and then press Enter. You must press Enter or the name will not be created. You can’t use spaces in range names, but you can use the underscore “_”. For example, Tax_Rate. You should also capitalise at least one letter of your range name. When you enter a range name in lower case in a formula, Excel will change the capitalisation of the name if it recognises it. If you make a typing error, Excel will not capitalise the name, which then makes debugging the formula easier.

You can easily insert names in formula by pressing the F3 key. This will display the Paste Name dialogue box. Double click the name to insert it in your formula. Ctrl +F3 will display the Define Name dialogue box which enables you to modify or delete existing names.

Extras

If you have data in a table layout you can automatically creates names based on the column and row headings by using Ctrl + Shift + F3 this opens the Create Names dialog.