Syntax errors with cell references - weird

Question

Level 1

8 points

Syntax errors with cell references - weird

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)

Posted on Jan 16, 2010 6:20 AM

Reply

Answer 1

Badunit

Level 7

25,664 points

Jan 16, 2010 9:23 AM in response to mampo

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.

Reply

Answer 2

KOENIG Yvan

Level 8

41,807 points

Jan 16, 2010 12:36 PM in response to mampo

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

Reply

Answer 3

mampo Author

Level 1

8 points

Jan 17, 2010 1:48 AM in response to KOENIG Yvan

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.

Reply

Answer 4

mampo Author

Level 1

8 points

Jan 17, 2010 1:50 AM in response to Badunit

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

Reply

Answer 5

KOENIG Yvan

Level 8

41,807 points

Jan 17, 2010 10:32 AM in response to mampo

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

Reply

Answer 6

Jan 17, 2010 7:10 PM in response to mampo

Martin,

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

Jerry

Reply

Answer 7

mampo Author

Level 1

8 points

Jan 23, 2010 2:29 PM in response to KOENIG Yvan

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

Reply

Answer 8

mampo Author

Level 1

8 points

Jan 23, 2010 2:31 PM in response to Jerrold Green1

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

Reply

Answer 9

Jan 23, 2010 2:34 PM in response to mampo

Martin,

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

Jerry

Reply

Answer 10

KOENIG Yvan

Level 8

41,807 points

Jan 23, 2010 11:31 PM in response to mampo

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

Reply

Answer 11

mampo Author

Level 1

8 points

Jan 30, 2010 8:45 AM in response to KOENIG Yvan

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)

Reply

Answer 12

KOENIG Yvan

Level 8

41,807 points

Jan 30, 2010 11:39 AM in response to mampo

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

Reply