Excel are Descriptive Column Names possible?

[6volt]

I know you can name a column, but that is basically naming a range.

Is there any way to rename the column "name", i.e. A,B,C,... etc?

Because

Columns are usually variable names, and
Rows are usually indices

Example:

R12 = A1 + B2*C17*FF37

has absolutely no intuitive meaning.

But the following does:

Efficiency12 = Temp1 + Pres2*Velocity17*Length37

I find that when I have a formula, the way I read it is to click on the text so that the colored cells are created, then I read the formula in colors, i.e. Red*Green/Blue instead of R2C[-37] which makes my head explode.

Thanks
Tom

 

[SkipVought]

Hi,

What Version of Excel?

If 2007+, then Excel has a feature called Structured Tables and Structured References. If 97-2003 (and in 2007+ as well), then Excel has Named Ranges using Insert > Names > Name -- Create name in TOP row, for instance where the the formula in a cell could be valid
[tt]
= Temp1 + Pres2*Velocity17*Length37
[/tt]
I make extensive use of both these features in 2007.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I just noticed the NUMBERS in your 'variables' and I think I need to make a clarification.

Suppose that your table were...
[tt]
Pres Velocity Length Formula
1 2 3
4 5 6
[/tt]
and furthermore a CELL, somewhere, is named [highlight]Temp[/highlight], that is used as a CONSTANT, let's say you enter 10.

Then the formula in each row of data in column D would be...
[tt]
[highlight]=Temp+Pres*Velocity*Length[/highlight]
[/tt]
The result in D2 & D3 would be 16, 130

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[foxup]

You can use row 1 for your variable names, and then hide the column and row headers.
Tools menu > Options > View Tab > then uncheck "Row & column headers" box.

Don't forget, it'll be a big pain to maintain as an Excel file. Rows and columns are just reference numbers for addressing.


Thanks,
FOXUP!

 

[6volt]

It looks like this would work if you are calculating on the same row, however if you are jumping all over the place row-wise, I guess it won't work, right?

And then everything must work with INDIRECT, ADDRESS, etc. too.

Thanks
Tom

 

[SkipVought]

This is for TABLE processing.

If you mean by jumping all over the place row-wise, picking a row, based on a VALUE, then you have a LOOKUP of some sort and, YES, these Range Names can be used by other functions, when appropriately employed.

Post a SPECIFIC example of a TABLE and values, therein, that are needed and the logic you wish to impose.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[6volt]

I think this might clarify what I'm thinking of:

If you name cell A1 "Length", any time you use "LENGTH" in a formula, it is identical to $A$1.

I'm after something like that.

I associate the name "TEMP" with the column name "D" so anywhere I would use D, such as D1, $D$1, D$1, etc. I could use TEMP.

Example:

TEMP23
$TEMP$23
TEMP$23
$TEMP23

Now that would be very intuitive and useful.

 

[SkipVought]

TEMP could be ONE cell or it could be a RANGE of cells.

Why would you use TEMP23? That is a DIFFERENT name, that would actually be a nameless reference unless you named some cell TEMP23.

You need to read the Excel HELP on Named Ranges AND Structured References if you have 2007+

What you are referring to is akin to using INDEX() lookup.

=INDEX(TEMP,23)

where TEMP would be a RANGE of cells

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[6volt]

Temp23 would tell me that I wanted the 23rd value in my temperature vector which resides in column D.

If I use D23, it means nothing. And in a complex formula, the formula becomes gibberish. Its like trying to read machine code. You see a Register number in a formula, it tells you nothing about the formula. You have to know what variable name is in the register to make any sense out of it.

D23 and Temp23 would have the same exact function everywhere in XL.

INDEX(Temp,23) would be identical to INDEX(D,23) which makes no sense in XL.

However, the concept is almost there but it would be cumbersome in a formula: ( I know you can't assign a value to INDEX !)

INDEX(Area,24) = INDEX(Length,11) * INDEX(Width,2)

But Area24 = Length11 * Width2 would be great.

Consider that in code you would have
area(24) = length(11) * width(2) which is very clear.

A24 = B11 * C2 means nothing until you mentally substitute the header names. This example is obvious, but if you have a long formula with conditions and calculations, it is a mess and unintelligible. Even the fact the XL will not allow you to use spaces for clarification is another blow to readability.

Basically MS arbitrarily gave the column headers the names of A,B,C,... They could have used Greek letters, or Able, Baker, Charlie,.... or Apples, Bananas, Coconut Cream Pies, .... So why not let the user choose the header name set?

Its too bad because they let you name a cell and a range, but they won't allow you to name a header.

Thats all. I'm sure it cannot be done.

Thanks
Tom

 

[mintjulep]

How about a cheat?

Enter "Temp" in A1

Enter =A$1&ROW(A2)-1 into A2

Copy down.

Select columns A and B

Insert | Names...

Create.

Check "Create Name in Left Column"

Hide column A if you want.