Can I name a column and use it in a formula? (Excel 2007)

[Jennifer Murphy]

I've used named cells many times and they make the formulas much
easier to read and much less error-prone.

Can this be extended to a column of calculations?

Suppose I want to calculate the area of a table of rectangles. My data
might look like this:


A B C
1 Height Width Area
2 2 3 6
3 3 5 15
4 8 6 48

The formula in C2 is "=A2*B2", which is not as easy to read as
"=Height*Width".

Is there a way to name Column A "Height" and Column B "Width" and then
be able to put something like "=Height*Width" in Column C?

I selected Column A and typed "Sheet1!Height" in the Name Box. Excel
seemed to like it. If I select the entire column, Excel shows "Height"
in the Name Box. But if I put "=Height*Width" in C2, I get a name
error. I also tried "=Height(2)*Width(2)" and a few others.

Is there some way I can put something like "=Height*Width" in C2?

 

[GS]

Jennifer Murphy was thinking very hard :

I've used named cells many times and they make the formulas much
easier to read and much less error-prone.

Can this be extended to a column of calculations?

Suppose I want to calculate the area of a table of rectangles. My data
might look like this:


A B C
1 Height Width Area
2 2 3 6
3 3 5 15
4 8 6 48

The formula in C2 is "=A2*B2", which is not as easy to read as
"=Height*Width".

Is there a way to name Column A "Height" and Column B "Width" and then
be able to put something like "=Height*Width" in Column C?

I selected Column A and typed "Sheet1!Height" in the Name Box. Excel
seemed to like it. If I select the entire column, Excel shows "Height"
in the Name Box. But if I put "=Height*Width" in C2, I get a name
error. I also tried "=Height(2)*Width(2)" and a few others.

Is there some way I can put something like "=Height*Width" in C2?

Yes!
You should make the names local in scope and column absolute/row
relative so that the same formula can be used in any row.

How...
******

Select A1.
In the Defined Name dialog:
In the name box type: '<SheetNameGoesHere>'!Height
In the RefersTo box type: =$A1
Click the Add button

In the name box type: '<SheetNameGoesHere>'!Width
In the RefersTo box type: =$B1
Click the Add button

[Optionally]
In the name box type: '<SheetNameGoesHere>'!Area
In the RefersTo box type: =Height*Width
Click the Add button
******

In the spreadsheet:
Select all the cells in ColC where you want the result
Press F2 to enter Edit Mode
Type: =Area
Hold down the 'Ctrl' key and press 'Enter'

 

[GS]

'<SheetNameGoesHere>'!

Note that <SheetNameGoesHere> should be replaced with the name of the
sheet you're defining the names on, AND that this is wrapped in
apostrophes folowed by an Exclamation character. This is important to
giving the defined name local scope. You'll know if this was done
correctly if you see the sheetname to the right in the names list.

So.., for the sheet named "Sheet1" the entry in the namebox will be:

'Sheet1'!NameGoesHere

 

[GS]

I didn't pick up that you already defined the names "Height" and
"Width" on Sheet1. In this case just edit each definition's RefersTo as
indicated in my original reply and Press the OK button to commit the
changes.

Optionally, you could delete the existing names and start 'clean'.

 

[Jennifer Murphy]

Jennifer Murphy was thinking very hard :

I've used named cells many times and they make the formulas much
easier to read and much less error-prone.

Can this be extended to a column of calculations?

Suppose I want to calculate the area of a table of rectangles. My data
might look like this:


A B C
1 Height Width Area
2 2 3 6
3 3 5 15
4 8 6 48

The formula in C2 is "=A2*B2", which is not as easy to read as
"=Height*Width".

Is there a way to name Column A "Height" and Column B "Width" and then
be able to put something like "=Height*Width" in Column C?

I selected Column A and typed "Sheet1!Height" in the Name Box. Excel
seemed to like it. If I select the entire column, Excel shows "Height"
in the Name Box. But if I put "=Height*Width" in C2, I get a name
error. I also tried "=Height(2)*Width(2)" and a few others.

Is there some way I can put something like "=Height*Width" in C2?

Yes!
You should make the names local in scope and column absolute/row
relative so that the same formula can be used in any row.

How...
******

Select A1.
In the Defined Name dialog:
In the name box type: '<SheetNameGoesHere>'!Height
In the RefersTo box type: =$A1
Click the Add button

In the name box type: '<SheetNameGoesHere>'!Width
In the RefersTo box type: =$B1
Click the Add button

[Optionally]
In the name box type: '<SheetNameGoesHere>'!Area
In the RefersTo box type: =Height*Width
Click the Add button
******

In the spreadsheet:
Select all the cells in ColC where you want the result
Press F2 to enter Edit Mode
Type: =Area
Hold down the 'Ctrl' key and press 'Enter'

Wow. It works. Thanks a million. I would never have figured that one
out.

After a little fiddling around, I came up with a couple of shortcuts.

1. I didn't put the sheetname in quotes, because it doesn't have any
spaces. It actually is "Sheet1", so I just entered "sheet1!hgt", which
works. I've been doing that for years. ;-)

2. It was a real pain to have to use the Name Manager to delete the
second "$" to change the $A$1 to $A1. (The example I posted was not
the real application, which has 10-12 columns that need to be named.)

I discovered that what I did originally actually works. If I select an
entire column (eg Column A) and enter a name, it gets entered as an
absolute column and relative name ($A1). This avoids having to edit it
in Name Manager.

Whatever method is used to establish the names, this method has a
couple of non-fatal, but highly annoying glitches. I'm hoping there is
some work-around:

1. The name is not displayed when the cells are selected. I consider
this a bug or a design flaw. It isn't even displayed when the column
is selected, which I consider a serious bug or a design flaw.

2. While entering a formula, clicking on any of the cells in the named
column does *NOT* make use of the column name. If I click in C1, type
"+", and then click in A1, it enters "=A1". This makes no sense to me.
I can type the name and it will provide me with a drop-down list, but
this is much less convenient than clicking on the cell I want.

Are there any ways to make this work better?

 

[GS]

After serious thinking Jennifer Murphy wrote :

Wow. It works. Thanks a million. I would never have figured that one
out.

You're welcome.
Sorry my instructions were in early version. I forgot that 2007 has the
name manager and so the instruction would have been simpler had I
remembered...

Select A1
In the Name Manager dialog:
Edit "Height" RefersTo: =$A1
Edit "Width" RefersTo: =$B1

After a little fiddling around, I came up with a couple of shortcuts.

1. I didn't put the sheetname in quotes, because it doesn't have any
spaces. It actually is "Sheet1", so I just entered "sheet1!hgt", which
works. I've been doing that for years. ;-)

Yes, I could have left that part out if I paid attention that your wks
was named "Sheet1". Also, I forgot that the Defined Name dialog had
been redesigned for 2007. The apostrophes are no longer needed when
defining names here (but ARE still needed when using the name box left
of Formula bar).

2. It was a real pain to have to use the Name Manager to delete the
second "$" to change the $A$1 to $A1. (The example I posted was not
the real application, which has 10-12 columns that need to be named.)

Here's a tip! When the RefersTo box has focus use F2 to enter edit
mode. Otherwise this field behaves like a ref edit when using arrows
keys. -Most annoying!

I discovered that what I did originally actually works. If I select an
entire column (eg Column A) and enter a name, it gets entered as an
absolute column and relative name ($A1). This avoids having to edit it
in Name Manager.

No, that's not true. If you select an entire column and enter a name
(in the name box OR DN dialog) the RefersTo will be $Col:$Col.

Whatever method is used to establish the names, this method has a
couple of non-fatal, but highly annoying glitches. I'm hoping there is
some work-around:

1. The name is not displayed when the cells are selected. I consider
this a bug or a design flaw. It isn't even displayed when the column
is selected, which I consider a serious bug or a design flaw.

Actually it's not a design flaw. Because the name is not fully absolute
it doesn't show in the name box. Only fully absolute names show here.

2. While entering a formula, clicking on any of the cells in the named
column does *NOT* make use of the column name. If I click in C1, type
"+", and then click in A1, it enters "=A1". This makes no sense to me.

It doesn't work like that, and is why we use defined names. It actually
makes no sense to not use a relative defined name once created,
otherwise Excel behaves normally as it would whenever you click a cell.
The logic here is that you don't want to type a defined name and so
Excel behaves as it should.

I can type the name and it will provide me with a drop-down list, but
this is much less convenient than clicking on the cell I want.

I don't agree! Since you're already typing it's more convenient to type
the first letter of the name you wish to use and get the popup list.
Use the Tab key to auto-insert that name from the list. It's more
inconvenient <IMO> to stop typing, use the mouse to select a range,
then resume typing the rest of the formula.

HTH

 

[Charabeuh]

Hello,

Select A1:B4
Then in the ribbon, choose (Excel 2010) :
Formula
In the manager name zone, select "Create from selection"
In the dialog box choose: Top row

==> A1:A4 will be named Height
==> B1:B4 will be named Width

Or you can also select columns A:B instead of A1:B4

Hope it will help you

__________________________________________________
Après mûre réflexion, Jennifer Murphy a écrit :