Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Excel are Descriptive Column Names possible?

6volt (Programmer) (OP)
29 Feb 12 12:06
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 (Programmer)
29 Feb 12 12:15


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

= Temp1 + Pres2*Velocity17*Length37

I make extensive use of both these features in 2007.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

SkipVought (Programmer)
29 Feb 12 12:26



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

Suppose that your table were...

Pres Velocity Length Formula
1    2        3    
4    5        6    

and furthermore a CELL, somewhere, is named Temp, 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...

=Temp+Pres*Velocity*Length

The result in D2 & D3 would be 16, 130

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

foxup (Programmer)
29 Feb 12 12:26
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 (Programmer) (OP)
29 Feb 12 13:36
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 (Programmer)
29 Feb 12 13:43
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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

6volt (Programmer) (OP)
29 Feb 12 17:07
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 (Programmer)
29 Feb 12 17:29
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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

6volt (Programmer) (OP)
29 Feb 12 17:58
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 (TechnicalUser)
29 Feb 12 18:18
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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close