12 Replies Latest reply: Jan 30, 2010 11:39 AM by KOENIG Yvan
mampo Level 1 Level 1 (0 points)
Dear forum members,

after working years in Excel and having had a look at Numbers, I face weird errors even for simple tasks.
I have, for example a formula that I enter into a field with references that I click on, let's say =B4-B3.
This works ONLY if I enter the cell references by keyboard. If I click on the same cells in the spreadsheet, Numbers enters the "real" name of the cell and this seems to cause an syntax error (red little triangle).
The same weirdness happens if I enter the formula by hand/keyboard with e.g. =B4-B3, then press enter (everything is fine), then go into the cell again, and see that the cell "real" names are expanded again (e.g. "Zählerstand 16.1.2010") in the formula. Without changing anything in the formula, just by pressing enter again, I get a syntax error.
It seems that Numbers has a problem with certain title names??
Does anyone know this problem?

Was I able to explain my problem?

All the best
Martin

MacBook Pro, Mac OS X (10.6.2)
  • Badunit Level 6 Level 6 (11,400 points)
    I recall a post a while ago where Numbers was having a problem with a cell name that had several quotes or apostrophes in it, I can't remember which. It might have the same problem with too many periods (decimal points). I tried the cell name you posted and had no problem but we are probably not using the same localization so your mileage may vary.
  • Level 8 Level 8 (41,790 points)
    If I understand well, you have
    "Zählerstand" in the column header,
    "16.1.2010" in the row header.
    On my French system, this setting behaves flawlessly with Numbers used in English, in French or in German.

    Which is your System setting ?

    Yvan KOENIG (VALLAURIS, France) samedi 16 janvier 2010 21:36:46
  • mampo Level 1 Level 1 (0 points)
    Dear Yvan,

    thank you very much for trying to replicate.
    Yes, your assumption is correct. My setting is German in my system.
    For example, one "problem-formula" reads
    "=(Zählerstand 16.01.2010-Zählerstand 11.06.2009)*10" I entered it via =(B4-B3)*10.
    After pressing return, I get the red triangle and syntax error.
    For me, it seems very closely related to the column and row names (something which does not happen in Excel).
    So if I enter everything not by clicking on cells in the formula/spreadsheet but by giving the references via keyboard, still Numbers expands them to the "real names" but everything is fine unless I enter that cell again and press return. I can send / upload the file if desired.
  • mampo Level 1 Level 1 (0 points)
    Dear Badunit,

    thank you for the tip, I might try reformatting the column and row names. Maybe there is an issue with dots or commas?

    I will give feedback
  • Level 8 Level 8 (41,790 points)
    Here it behaves flawlessly.



    What is the contents of the cells ?

    If one of them is empty, the error message is normal.

    Which is the error message displayed if you click the red triangle ?

    Click my blue name to get my mail address so you will be able to send the document.

    Yvan KOENIG (VALLAURIS, France) dimanche 17 janvier 2010 19:27:40
  • Jerrold Green1 Level 7 Level 7 (29,935 points)
    Martin,

    Do you have the same error is you disable the "Use Header Cell Names as References" Preference?

    Jerry
  • mampo Level 1 Level 1 (0 points)
    Dear Yvan,

    thank you for your further infos!
    Here is the error message in the red triangle:
    The formula contains a syntax error

    I will send you the file.
    Thanks a lot.
    So far I survive by not touching the formulas
  • mampo Level 1 Level 1 (0 points)
    Jerry,

    thank you for pointing me to this preference.
    Switching it off actually solves my problem.
    So Numbers has a problem with the header cell contents.

    Martin
  • Jerrold Green1 Level 7 Level 7 (29,935 points)
    Martin,

    You may send feedback to Apple via the Numbers menu. Reference this topic (give them the URL) in your note to them.

    Jerry
  • Level 8 Level 8 (41,790 points)
    It appears that the problem is linked to your own system.

    I received your file and it behave flawlessly : no red triangle when the beast use the header cells as references.

    Yvan KOENIG (VALLAURIS, France) dimanche 24 janvier 2010 08:31:14
  • mampo Level 1 Level 1 (0 points)
    Yvan,

    thank you for replicating the error on your system!

    You gave me a workaroung via email (calculating the header cell from a date entry in the second column).
    This worked.
    But I will file a bug report for Apple. Maybe they can fix it)
  • Level 8 Level 8 (41,790 points)
    The problem is linked to dates using the period as components delimiter.

    If I use the current French format 31/12/1943, the spreadsheet behaves flawlessly without the added column.
    As it appears that this format is unavailable in the default Deutsch resource file, two soluces are available :

    (1) use an added column (B) storing the true dates and, in the headers of row, build the names with a formula of this kind :
    =SUBSTITUTE(B,".","_")
    (2) use the script which I re-posted some days ago to add at least one new date format to the Deutsch resource file.
    This one must be DD/MM/YYYY
    If you use it, you will be able to work without the added column.
    Here, Numbers enclose the date with slashes ibetween single quotes.
    =(Zählerstand '11/01/2010'-Zählerstand '11/06/2009')*10

    Yvan KOENIG (VALLAURIS, France) samedi 30 janvier 2010 20:39:09