#N/A error

[squishen]

I have rather long =SUM function in Calc and all my arguments in the string seem to be correct. When I enter the string, everything seems to check out and it gives me a result in the cell. However, when I save the file, close it, then re-open it, the cell just says #N/A. In the bar up top it says =NA().
Any ideas what I'm doing wrong? Is my string too long? I've pasted it below for reference. I've verified that I have a valid value in every cell referenced.

=SUM($'2011'.J2;-SUM(E3:E5);D6;D8;-SUM(E9:E10);D11:D22;D23-2000;D24;D27:D35;-SUM(E37:E39);D40:D59;D60-2000;-SUM(E62:E64);D65:D72;D73-2500;D74:D84;D86+1053;D87:D91;-SUM(E92);D93;-SUM(E95:E97);D98:D100;-SUM(E101:E102);D103:D107;D109:D117;D118-100;D119:D123;-SUM(E125:E129);D130:D149;D151+1200;D152;-SUM(E155:E157);D158:D159;D160-8000;D161:D169)

[ozzie]

Hi squishen

1) There is a function =NA(), this function has no arguments, and returns the error value #N/A but how and why it should turn up out of the blue on your sheet is beyond me!
2) The documentation says that the limit to a SUM function is, "up to 30 numbers or ranges/arrays of numbers whose sum is to be calculated". Certainly the limit to that picked up by edit (F2) of the SUM is 30, but I was able create a larger (but perhaps not so variable) function that worked fine, so perhaps the documentation lags behind the programming which is not surprising.
3) Your formula worked fine according to my tests but did not give the same reaction to opening and closing.

Having said all that unhelpful stuff, on looking at your formula I did get the impression of a list of debits and credits (positive and negative) with the occasional adjustments thrown in. If there is no text in these columns could not-
Carryover from previous year plus SUM(column D) minus SUM(column E) plus/minus adjustments (perhaps 3rd column) be used? (at least for ease of reading).
Otherwise I am unable to be of any assistance - good luck.
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).

[squishen]

Thanks for the reply, Ozzie. Yes, it's a mystery. I think I'll try the multi-column approach. Thanks for that suggestion.

[keme]

Most likely the software supports the "extended" use of the function (more parameters than the documentation says is permitted), but it is cut off somehow when saved or reloaded. I guess you're working on a structured approach now, but if you still have your original file it would be interesting to know more.

This formula should do exactly the same, but it is "within specs". Try it and report back with your findings, if you will...
=$'2011'.J2+SUM(D6;D8;D11:D24;D27:D35;D40:D60;D65:D84;D86:D91;D93;D98:D100;D103:D107;D109:D123;D130:D149;D151:D152;D158:D169)-SUM(E3:E5;E9:E10;E37:E39;E62:E64;E92;E95:E97;E101:E102;E125:E129;E155:E157)-12347

Also, what software title and version number are you on?
(Libre Office, OpenOffice.org, or other;version 3.3/3.4/other.)

[squishen]

I think you're right about that, keme (too many parameters causing it to be cut off when saved). Your new formula indeed worked. Thanks for that!