|
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, 1 Aug 04
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.
First published: AUSTRALIAN CPA
Magazine August 2004 - Page 48 Excel Yourself
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.
Be the First to Comment!
Related Articles
| Dynamic Range Name - What is a dynamic range? |
|
A dynamic
range is a range whose size changes as data is added or
deleted in an area ... |
 |
| Dynamic Range Name - What is a dynamic range? |
|
A dynamic
range is a range whose size changes as data is added or
deleted in an area ... |
|
| List Range Names - Is there a way to list all the range names in a workbook? |
|
Yes. Go to
a cell where you want the list to start, then press the
F3 key to open the Paste Name box and click the Paste
List button to paste all the names used ... |
|
| List Range Names - Is there a way to list all the range names in a workbook? |
|
Yes. Go to
a cell where you want the list to start, then press the
F3 key to open the Paste Name box and click the Paste
List button to paste all the names used ... |
|
| Mulitply a range of cells by a factor - I have a range of cells that contain formulae and values. I need to multiply all the cells in the range by a factor. The factor could change. Is there is any easy solution? |
|
The easiest way is to use a range
name. First enter the factor value in a cell and name
that cell Factor ... |
|
| Use a Range Name in a single sheet - Can I create a range name that only works in a single sheet? |
|
Yes. To create a range name that only
applies to a single sheet you type in the sheet name following
by a "!" then the range name you require ... |
|
|
Print
