Cell reference in Numbers

One feature most people probably don't notice in Numbers is that when you have a Header Row then each column automatically becomes a named range. Thus in the formula editor you can easily enter formulas using names rather than cell addresses.

Here, I started typing Pr and used the right arrow to navigate over to Price and hit enter. The formula editor inserted the appropriate reference.

Then I typed * for multiplication followed by Qu and right arrow to Quantity and hit enter.

Then I filled the formula down.

When you look in one of the cells in column D you will see this;

If at Numbers > Preferences you turn on "Use header names as labels" you will see this.

Under the hood the named ranges are always active and you can often take advantage of them when entering formulas and debugging formulas.

SG

You cannot assign a name to a cell in Numbers and there is no naming that is independent of an address, except by doing it like in the next paragraph. If you use the preference "Use header names as labels", a cell will be given a name based on what is in its header column and header row (as long as it is unique within the table, no two cells in a table can have the same name). But it still works like an address. You cannot move the header "names" elsewhere and have the cell reference move to there.

But all is not lost. While not very practical, you can use INDIRECT along with the header names to create a "variable" address. If a cell has "Bob" in its header column and "Smith" in its header row then INDIRECT("Bob Smith") will be a reference to that cell. If you move "Bob" to a different column or "Smith" to a different row, INDIRECT("Bob Smith") will refer to that cell instead. You do not need to have "Use header names as labels" selected for this to work. For best use, there should be only one "Bob Smith" in the entire document. If there are more, it will be the "Bob Smith" in the same table (if there is one) or you will need to include the table name or maybe even the sheet name, INDIRECT("Table 1::Bob Smith").

Somehow tonight I called the header column the header row and vice versa. Header row is row 1. Header column is column A. Header row has "Bob". Header column has "Smith". Hopefully everything else I wrote is correct. Sorry 'bout that.