I have attached a spreadsheet that demonstrates the problem. 1) Open the attached spreadsheet that contains formulas stored as defined names. 2) Note that cell E4 displays #NAME?. 3) Ctrl-F3 to open the defined names window and examine defined name "ElecUsageKW". Note that the Assigned To field contains a valid formula. 4) Highlight and copy the formula. Then click Delete. (note: you cannot click modify as that will crash 3.4.3. A separate bug report was submitted about that problem which is not related to the subject of this bug) 5) Immediately paste the saved formula back into the Assigned To field and then click Add. 6) Click OK and note that cell E4 now displays the correct value value of 1682. 7) Resave the spreadsheet. 8) Close the spreadsheet. 9) The next time the spreadsheet is opened the problem will be the same as above. I have not been able to find a work-around to this problem. This was not a problem in 3.4.2. It is new to 3.4.3.
Created attachment 50852 [details] Spreadsheet that demonstrates the bug.
Same as https://bugs.freedesktop.org/show_bug.cgi?id=40378 *** This bug has been marked as a duplicate of bug 40378 ***
(In reply to comment #0) > 4) Highlight and copy the formula. Then click Delete. (note: you cannot click > modify as that will crash 3.4.3. A separate bug report was submitted about > that problem which is not related to the subject of this bug) Which bug ID?
(In reply to comment #3) > (In reply to comment #0) > > 4) Highlight and copy the formula. Then click Delete. (note: you cannot click > > modify as that will crash 3.4.3. A separate bug report was submitted about > > that problem which is not related to the subject of this bug) > > Which bug ID? 40571.
The cause of #NAME? was fixed with bug 40378 in master.
(In reply to comment #5) > The cause of #NAME? was fixed with bug 40378 in master. I downloaded and installed master~2011-09-02_14.25.52_LibO-Dev_OOO350m1_Win_x86_install_en-US.exe and it did appear to partially fix the problem. Unfortunately, that version breaks several other things in the spreadsheet so I just uninstalled it and went back to 3.4.2 which does not have the problem. Perhaps the next release will fix this without breaking anything else.
The fix implemented by bug 40378, which this bug was marked as a DUPL of, does not completely correct this problem. The problem as indicated by the originally attached spreadsheet is fixed but a different formula stored as a defined name in the complete spreadsheet fails. This was determined by testing with master master~2011-09-06_02.34.59_LibO-Dev_OOO350m1_Win_x86_install_en-US.exe. The defined name formula problem created by the fix (or not corrected as a result of an incomplete fix) is that double quotes are removed from formulas stored as defined names when the spreadsheet is opened. Original defined name value: IF(ElecUsage<>"",VLOOKUP(BillingDate,GasRates,3)*GasUsage/10+VLOOKUP(BillingDate,GasRates,2)),"") Valued of defined name after spreadsheet loads: IF(ElecUsage<>,VLOOKUP(BillingDate,GasRates,3)*GasUsage/10+VLOOKUP(BillingDate,GasRates,2)),) Notice that the Double Quotes that are supposed to be following the <> in the initial element of the formula are gone. Also the double quotes at the end of the formula are gone. If I replace these in the formula and do a modify (which now works thanks for the fix to 40571) the formula processes correctly until the spreadsheet is saved and reloaded.
Thanks for noticing. For the records: the name in question is ElecCalcProjected, also other ElecCalc* names that use similar formulas are affected. I'll take a look at this.
Fixed in master http://cgit.freedesktop.org/libreoffice/core/commit/?id=34c9a01b075788653b89c244eb8b3fcee54bfa68
This problem still exists in 3.4.4. See bug 42745.
*** Bug 42745 has been marked as a duplicate of this bug. ***
This bug has been marked as RESOLVED FIXED. The definitions of those words is subject to interpretation. As it turns out, the fix will not be included until at least release 3.5.0. The bug was introduced in 3.4.3. 3.4.2 worked correctly. 3.4.3 created the problem. The fix is now stated as being too invasive to include in 3.4.4 or 3.4.5. Amazing. They broke it in 3.4.3 but the fix is "too invasive" to be implemented until 3.5.0 at the earliest. With this operating philosophy I don't believe that anyone should consider LibreOffice for use in a true production environment if your software maintenance policy embraces the concept of installing incremental fix releases. See bug 42745 for more.
In 3-4 http://cgit.freedesktop.org/libreoffice/calc/commit/?h=libreoffice-3-4&id=e8a321db4419be43c693d5b5d06bfda25709d2f5
For completeness: also needed due to the split repos on 3-4: http://cgit.freedesktop.org/libreoffice/libs-core/commit/?h=libreoffice-3-4&id=a61057eb182d680eefaa996c0b4cd13ba4bdfcc0