10 Replies Latest reply: Jul 4, 2012 7:54 PM by Jerrold Green1
BGist Level 2 Level 2 (265 points)

I'm new to Numbers - lifelong Excel user/developer. 

 

I'm trying to do something which I beleive should be extremely simple.  I have two spreadsheets, one called "Expenses" and one called "Summary."  Revenues has three tables. 

 

In the "Summary" spreadsheet, I'm in a table and I'm trying to multiply a cell from the "Fixed Costs" table in "Expenses" by 12 to get the annual fixed costs.

 

When I type "=" and then use my mouse to select any cell in the "Fixed Costs" table in the "Expenses" spreadsheet, it fails to return a valid reference. 

Instead of a valid reference, it just the text "Y1 Cost" ... which generates the error in the subject of this discussion.  (See graphic).

 

Screen Shot 2012-07-02 at 9.26.43 PM.png

Screen Shot 2012-07-02 at 9.26.56 PM.png

 

All of the other tables in "Expenses" return proper cell references (see below).

Screen Shot 2012-07-02 at 9.26.08 PM.png

I've verified that the cell in question is numeric (its a formula itself). 

I've deleted the table and re-created it.

Screen Shot 2012-07-02 at 9.32.42 PM.png

If I try to create a sum (=sum( ... .and then select B2:B3)... it works.  But selecting a single cell does not.

 

Its making me a bit crazy. 

I can work around it ... but I'd sure appreciate understanding why its behaving this way, so I can avoid it in the future. 


MacBook Pro, iPhone 4GB, Mac OS X (10.4.10)
  • Barry Level 7 Level 7 (29,470 points)

    "I have two spreadsheets, one called "Expenses" and one called "Summary."  Revenues has three tables."

     

    "Spreadsheet" = "Document"?

     

    Or do you mean "My document has two Sheets, "Expenses" and "Summary"

     

    What is "Revenues"? Another Sheet in the current Document? A separate Document?

     

    Is your profile accurate? It lists Mac OS X v10.4.10, one update before the final one for v10.4. Unlikely to be a contributing factor, but possible.

     

    I don't see a reason for the failure. I would suggest turning off 'Use header cell names as references' while building formulas/tables, but that may just be personal preference on my part.

     

    Regards,

    Barry

     

    PS: Answers to the questions may help someone come to an AHA! moment before I get back to this.

    B

  • Wayne Contello Level 6 Level 6 (16,160 points)

    Also can you show the complete formula for the first two pictures in your previous post?  select the cell with the yield sign, then triple click the focmula in the formula bar, copy, and paste into this forum:

    e.g.:

    D4=What ever the formula you paste

  • BGist Level 2 Level 2 (265 points)

    Sorry for the confusion, yes, I mean I have two "Sheets" inside of one "Document" ... and I'm trying to reference one "sheet" from the other. 

     

    Profile isn't accurate - I have 10.7.4 on an i7 MBP running Numbers09 (v 2.1.436).

     

    I also should have mentioned, this file was originally built on Numbers for iOS, saved to iCloud ... copied to OSX ... saved back to iCloud ... and back and forth.  It was on the 4th copy to OSX that it failed.  This is particlarly suspicious, as the file is over 6MB, and has nothing but a few "sheets" of numbers.  I can't see how it should be larger than 200k. 

     

    I've now turned off the "Use Header cells names as reference" option ... I didn't like that anyhow.

    This "solved" the problem ... but ... I'd still like to understand why Numbers wasn't able to reference this one particular table ... or what made it different from all the others. 

     

    More notes to follow ...

  • BGist Level 2 Level 2 (265 points)

    Those were actually the full formula - I was showing a picture from the formula bar ... that was the weird part - as soon as I was typing the formula ... it just sorta ignored the fact that the reference was from another "Sheet"

     

    Screen Shot 2012-07-03 at 7.38.07 AM.png

     

    Screen Shot 2012-07-03 at 7.37.11 AM.png

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

    BGist,

     

    It would help if you would go to Numbers Preferences and turn off the Header cell names as references option. Those references are nifty but can be confusing, especially considering that the reference seems to be the source of your troubles.

     

    Jerry

  • Barry Level 7 Level 7 (29,470 points)

    "it just sorta ignored the fact that the reference was from another Sheet"

     

    That's because of the use of Header cell names as references—there's only one column with a header cell name "Y1", so the Sheet name isn't necessary to identify the referenced cell. That's an example of the confusion that can arise from using the header cell names as references.

     

    Regards,

    Barry

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

    BG,

     

    OI think the problem may be that Y1 is a valid Cell Reference in itself. When the compiler sees that, it may not consider the rest of your typed reference text as part of the address.

     

    Using the Point and Click method of entering cell references seems to overcome this issue.

     

    Jerry

  • BGist Level 2 Level 2 (265 points)

    That's exactly the problem.  I am using the point and click method.  I'm hitting the "=" key, then clicking on the "Sheet" named "Expenses" and clicking on the cell B7 in the Table "Costs." 

     

    I dont know why its not returning the cell reference. 

  • BGist Level 2 Level 2 (265 points)

    This is the "solution" I'm following now ... but it only serves to reinforce my uncertainty regarding my continued use of Numbers.  It does things I don't understand, and don't seem to be repeatable.  On the iOS version, sometimes when I type "=" it starts a formula, other times it types the character "=" in the cell ... sometimes when I select the upper-left-hand corner of a table, it allows me to move it, other times, it enters the cell.  Unless I can understand what causes Numbers to do the things I don't want it to do ... I don't see myself making any transition from Excel. 

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

    BGist wrote:

     

    This is the "solution" I'm following now ... but it only serves to reinforce my uncertainty regarding my continued use of Numbers.  It does things I don't understand, and don't seem to be repeatable.  On the iOS version, sometimes when I type "=" it starts a formula, other times it types the character "=" in the cell ... sometimes when I select the upper-left-hand corner of a table, it allows me to move it, other times, it enters the cell.  Unless I can understand what causes Numbers to do the things I don't want it to do ... I don't see myself making any transition from Excel. 

    I can't help you with what is going on in the iOS version, and as far as I know, you don't have many spreadsheet program options there. As I mentioned above, I would stay away from the Header names as Cell References.

     

    Jerry