11 Replies Latest reply: Sep 21, 2014 2:10 PM 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,935 points)

    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 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,935 points)

    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

  • Scott Paine Level 1 Level 1 (35 points)

    Complaints!?

     

    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)

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

  • t quinn Level 4 Level 4 (2,690 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.

     

    quinn

  • SGIII Level 5 Level 5 (5,760 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.

     

    SG

  • 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 (5,760 points)

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

     

    SG