Is there a way to assign an alias name to a column?

[wolfv]

Is there a way to assign an alias name to a column?
For example,

• assign alias name “sell_price” to B:B
• assign alias name “buy_price” to H:H

Thus the formula

• =B4-H8

can be written as

• ='sell_price’4-'buy_price’8

which is more readable because the alias names are more meaningful than the column letters.

[mikekaganski]

Recognizing Names as Addressing

[wolfv]

@mikekaganski,
With “Automatically find column and row labels” checked, formula

• ='sell_price'4-'buy_price'8
  

got Err:509 (an operator is missing from the formula)

[keme]

The intersection operator and a row range gives almost what you ask for. Try:

='sell_price'!4:4-'buy_price'!8:8

[wolfv]

Thanks keme, that worked! After some consideration, I decided to use the INDEX() function.

[keme]

… decided to use the INDEX() function.

Good choice!

The intersection is mostly for special cases. You can add some flexibility by creating ranges dynamically (using OFFSET() or INDIRECT()) but that will often be unnecessarily complex. INDEX() tends to be the easy and reliable path to your end.

[keme]

Solution

You can use the automatic name recognition, linked in @mikekaganski’s comment. This is useful in some cases and fast to work with, but not very robust.

• Empty cells may break the name-connection to column heading, or not.
  
  Making a “gutter” spacing between two tables may still connect between data in second table and heading in first table. In other cases, empty space within a table might break the connection. I have not observed any consistent rule to this.
• There is no warning when duplicate names exist.
  
  Name is silently reassigned, possibly based on calculation order. Again, I have not observed any consistent behavior.
• Array formulas do not detect the data size from automatic naming.

Best practice, according to myself

I suggest you disable the automatic naming and assign names explicitly. Assigning name to range is a matter of selecting the range you want to name (for your case: click the column letter in the frame above the cell grid) and typing a name in the Name field (dropdown/input field far left on the formula toolbar). The name you type will become a selectable entry in the dropdown.

Selecting an element from a named range is best done by the INDEX() function. Sometimes the range intersection operator ! is also good to use for this.

Fun stuff (useful and in context)

If you want to investigate the behavior of automatic range naming, look at this file.

The Prices and Amounts columns in the green frame are explicitly defined named ranges. Every other referenced name autodetects. Inspect the file, formulas and named ranges, and play around with it.

• The formulas in D2 and H2 are both entered as array formulas.
  
  For autodetected ranges, size is not detected.
• Note that the yellow cells identify the Cost range differently.
  
  Names are redefined in a less than predictable fashion, somewhat towards “nearest match” but not quite.
• The two cells with #REF error fail to identify the Submarine row.
  
  If you enter some text above or below “Submarine” (cell A12 or A14), and then “mock edit” the error formulas (type space, delete it with backspace, enter), the row is successfully identified.
• In A2, change “Car” to “Automobile”. See what happens to the formula in cell D20.
  
  References update automatically. This may come as a surprise. The reference to an autodetected heading is still a reference, not a lookup towards content. If the name of a reference changes, the change is reflected everywhere it is referenced.

The naming (whether automatic or manual) makes spreadsheets largely “self documenting” which helps you to continue working with them after you forgot exactly what you did. This makes named ranges an efficient use of resources when it works, and dangerous when it misbehaves. You now know more about how it works and how it doesn’t.

Edit: Note that (as you may have guessed) I am not eager to use the autodetection feature. Hence, there may be perils (and also advantages) to it which I have missed.

Good luck!

[erAck]

An apparently little known alternative to automatic label recognition where cell content is used as column or row names are label ranges, Sheet → Named Ranges and Expressions… → Labels. These then can be used the same as automatic labels but without their drawbacks (like possibility of unexpected position taking depending on vicinity and order of cell content). See help under related topics at the link Mike posted in his first comment.

Another way to use cell content as column labels with additional functionality is “database ranges”. Data → Define Range…