Does Numbers support named ranges yet?

Hi MacDaddyonMaui,

This is a stale thread, but thanks for your comments.

In Numbers each column in a table with a Header Row is automatically a named range. You don't even have to create the named range the way you do in Excel. The name is the value in the Header Row cell.

For example, in column C here you can insert the formula as: =Quantity*Price

That works even if you do not have this checked in Numbers > Preferences:

And similarly, each row in a table with a Header Column is a named range. So you could enter =Quantity*Price in row 4 here.

This can be very convenient. I'd be wary of merged cells, though.

SG

Scott,

Excel has an expansive grid/table on every sheet, as wide and as tall as the Worksheet can be. You can subdivide it by naming ranges.

Numbers has an expansive canvas (sheet). On it you can place numerous named tables.These are subsets of the Sheet cells as a whole. Analogous (loosely) to what is happening in Excel. There is no need to further subdivide the tables on a Numbers Sheet. Just make smaller ones.

Jerry

Scott,

I get where you are coming from. I don't expect that Numbers is going to go in that direction. If you read the torrent of recent complaints about Version 3.0, you will realize that quite the opposite is true.

Jerry

Hi Scott,

I found your post because I was looking for the same thing. I found something that actually works better for some needs, if you just want a name for a single cell to be used in a formula, you can just use the text that labels it in the spreedsheet.

Here is an example:

Both calculations multiply the value in B2 by 3.

If you "Click" on the cell when making the formula, you'll get the first one, but if you start typing "Source Field", Numbers will treat the cell as a named ranged as you see.

BTW, It also works for ranges in another table:

1. Start typing the name of the other table:

2. Click on the table you want:

3. The cell I wanted came up. If it didn't, I think I could keep typing... Then hit "Enter":

Hope this is helpful.

Hi

I just had the same problem.. sometimes thinking around the corner actually solves the problem!

In Excel it is neccessary to have "named ranges" as we cannot have "multiple" tables. In numbers we can!

Thus: simply create your for your lookup a new table and give it a table name (eg. lookupTable)

Then in your cell formulas (in my example a vlookup) I simply refer to lookupTable::2:2 which is basically the lookup table with 2 columns.

Works for me :-)

Hi Scott,

Ths thread popped up for me because of Micheal's post above.

If a big reason for your desire for named ranges is to populate popups you should explore applescript. SG has done some work with that relating to popups and even inspired me to write a script that updates my unused popups in several sheets in one doc.

If you are still interested you should post a new question.

quinn

Hi Michael,

In Excel it is neccessary to have "named ranges" as we cannot have "multiple" tables. In numbers we can!

Agree that the Numbers implementation of tables is great and greatly reduces any need for named ranges, though Mo-Ran-Ran's pointers on constants are very handy .... Just want to clarify that it in Excel it is possible to have multiple tables in a worksheet.

They're just not as easy to use as in Numbers.

SG

your "tables" are more "named ranges", or those tables you need to "lookup" data in

my tables are real individual tables which all start at column A and row 1 :-) of those kind you cannot have more than one on a sheet in Excel :-)

In Excel named ranges are different from tables, which are indeed "real" individual tables.

SG

Most of the responses in here are simply wrong. Numbers does support named ranges, in a way I find to be superior to Excel in most respects. What I like most, is simply entering values in the header row and column, also names the ranges, and the ranges are named including spaces, instead of replacing spaces with underscores. you don't need to type multiple menu commands as in excel.

To start, name your sheet (sheet name), then name your table (table name). In your table, set 1 or 2 header rows and/or header columns. You type the range names in these headers. The syntax can include spaces. The name selected is the rightmost or bottommost header cell with a value. Duplicate values in the headers are not allowed and take the syntax for formulas back to column letter row number.

For a multi row range - merge the header cells. For a multi column range - merge the header cells.

the naming syntax is: "sheet name::table name::colname rowname". the sheet name is not used on the same sheet, the table name is not used in the same table. if the column name is not specified, a row range is set. if the rowname is not set a column range is set. if your formula requires a a cell, you must specify a "colname rowname" that evaluates to an intersection of one cell.

the syntax is expanded somewhat when using applescript (and I assume Xcode). In the expansion, 2 header rows and 2 header columns can be used. For example, if the first header column contains file merged cells with the value "Gross Income", the syntax Range ("Gross Income") refers to a five row range, the width of the table. If you use another unique designator in the second header column, say (1,2,3,4,5), you can specify the syntax as Range ("Gross Income 2") and refer to the second row of the range alone.

to specify a column (or a range of columns) say "Value" Specify the syntax as Range("Value Gross Income 2). This will result in a range that is the row labeled "2" of "Gross Income" in the column or columns labelled "Value".

One thing I have discovered, is that there is an undocumented reserved word list, that will sometimes fail your formulas. if your names don't work in your formulas, try changing them slightly adding another letter or number to the end.