13 Replies Latest reply: Aug 18, 2015 9:22 AM by SGIII
Scott Paine Level 1 Level 1 (35 points)

Excel and OpenOffice both support named ranges, but Numbers has not. So, I have been using LibreOffice for complex spreadsheets and Numbers for simple ones. I updated to Mavericks and installed the new version of Numbers.


Unless I'm missing something, there remains no support for named ranges and no way to control the values for cell popup menus via a formula or reference to a named range. You can use a range of values in a formula, such as to place the sum of a range in a distant cell, if the range uses the standard header row; but this is not quite the same as a named range. And there is no way to reference a range as a popup value, such as using the Indirect and Index functions; you have to type in values for the menu.


...unless I'm missing something. Anybody see a new way to handle named ranges in Numbers 3?

MacBook Pro, Mac OS X (10.7.5)
  • Jerrold Green1 Level 7 Level 7 (29,955 points)



    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.



  • Scott Paine Level 1 Level 1 (35 points)

    Thanks. I get the Numbers paradigm and I like it, but I have failed to find a way to dynamically relate values in one table from values in another table for validation (popup menus) and conditional formatting in a sheet in Numbers.


    It's likely I have not made my goal clear. Table A contains a column of values, and Table B contains a column of values that I want to constrain to only the values in Table A—via a popup menu or a validation rule (also missing from Numbers as far as I can find). The popup menu for values in Table B must reflect edits to values in Table A, such that if I duplicate a value in Table A it shows up in the popup in Table B only once, and if I delete all instances of a value in Table A the popup menu in Table B no longer has that value, etc… This is a gross simplification. Of course for this case I would not need Table A; I would just enter values in Table B and the popup format would already manage its own values from those already entered/deleted/etc…


    By way of rationalization for this method, I am not saying Numbers should be able to do everything Excel does, although if it did I could get rid of Calc and enjoy a Mac-like experience for all my spreadsheet and database needs  Still, all the functions have been in place since at least 2009. MATCH and INDEX allow me to lookup data from a table based on values in another table. INDEFINITE allows referencing data using a string that looks like a reference. All that seems to be missing is the ability to use a formula as a popup menu value. Ditto for conditional formatting and (if present) validation rules. Combined, these features allow me to build fully relational databases for clients and myself using only a spreadsheet, albeit not a Numbers one.

  • Jerrold Green1 Level 7 Level 7 (29,955 points)



    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.



  • Scott Paine Level 1 Level 1 (35 points)



    I was hoping for some more advanced features, but I've been delighted by the UI changes. Numbers is much less clunky now. I'd guess it handled about 75% of my spreadsheet needs, and now all that has gotten a LOT easier.


    I will continue to use Numbers for simple spreadsheets and LibreOffice for complex ones.


    Thanks for your input.


  • Mo-Ran-Ran Level 1 Level 1 (0 points)

    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:

    Named Ranges.jpg

    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:

    Name Ranges2.jpg

    2. Click on the table you want:

    Name Ranges3.jpg

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

    Name Ranges4.jpg


    Hope this is helpful.

  • Scott Paine Level 1 Level 1 (35 points)

    Your method provides a way to lookup single variables or values based on a label, but does not refer to a range in the way I require per my original post. Still, it's a piece of solution to a big problem (that Apple will probably not ever deal with because it's not in the product scope).

    Thanks for your reply, though; because it's a great tutorial for using labeled cells

  • Michael Zischeck1 Level 1 Level 1 (55 points)



    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 :-)

  • t quinn Level 4 Level 4 (3,665 points)

    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.



  • SGIII Level 5 Level 5 (7,160 points)

    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.


    Screen Shot 2014-09-21 at 4.56.24 PM.png


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



  • Michael Zischeck1 Level 1 Level 1 (55 points)

    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 :-)

  • SGIII Level 5 Level 5 (7,160 points)

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



  • MacDaddyonMaui Level 1 Level 1 (0 points)

    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.

  • SGIII Level 5 Level 5 (7,160 points)

    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


    Screen Shot 2015-08-18 at 12.11.38.png


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

    Screen Shot 2015-08-18 at 12.07.48.png

    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.

    Screen Shot 2015-08-18 at 12.13.33.png

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