Apache OpenOffice (AOO) Bugzilla – Issue 3724
Xcl Exp: Formulas with parentheses
Last modified: 2013-08-07 15:15:02 UTC
Here is how the issue can be demonstrated: Create a spreadsheet Type "test" in cell A1 of Sheet1 Go to Sheet2 and type "=(Sheet1.A1)" in the cell Save in Excel 97/2000/XP format Open the spreadsheet in Excel 97 The value that shows for cell At on Sheet 2 is "#VALUE!" when it should be "test"
Hi Daniel, one 4 you
2do: Correct token class for operands in parentheses
*** Issue 6547 has been marked as a duplicate of this issue. ***
*** Issue 8184 has been marked as a duplicate of this issue. ***
The parentheses is irrelevant. ooo101 uses sheet1.a1, saving as Excel does not alter it to sheet1!a1
What do you mean with "irrelevant"? I have a document with 2 references: =Sheet1.A1 and =(Sheet1.A2), and export this file to Excel. The former works in Excel, the latter not. After reimport to Calc, both work correctly.
Profuse apologies - I posted that at the end of a very long night - I was, of course, talking utter rubbish and I shall endeavour not to do anything quite so stupid again.
Bob, ok ok, no problem ;-)
target->OOo2.0
Created attachment 10769 [details] Another example without parens
Not sure if this is related, but I posted an example that has: =B8+INDIRECT(ADDRESS(2;2)) When saved as an Excel file, and opened in Excel, this cell reads #VALUE! until the cell is edited (even without changing anything). Without the 'B8+', it works fine.
Thanks, I will take this into account.
*** Issue 27933 has been marked as a duplicate of this issue. ***
OC: Due to high workload this issue is retargeted to ooo.later
*** Issue 30217 has been marked as a duplicate of this issue. ***
Add me and Michael meeks in CC. I hope Michael can help as Daniel load cannot handle this for OOo 2.0. but if we look at the duplicate issue it considered as common issue, and better to solve sooner. also look at issue created (which is duplicate of this issue) http://www.openoffice.org/issues/show_bug.cgi?id=30217 Thanks
*** Issue 31257 has been marked as a duplicate of this issue. ***
Created attachment 16987 [details] yet another testdoc
Created attachment 18411 [details] Multi Page Excel file with complex IF formulas
I have read through all of the tickets opened on this item and would like to take a step further. I am experiencing the same issue of receiving #Value, however, the fixes I have read in these threads do not appear to be working. I created an attachment for you to review (# 18411). Note this file was created in Open Office and saved as an excel file. It has never been saved as a .sxc file. The parentheses in these forumlas are required for the forumla logic and can not be removed.
Thanks for the test doc. I am currently working on this issue and I think to be ready next week, so this will work in OOo 2.0
FIXED in CWS SRC680/dr27 (OOo 2.0) The case =(Sheet1.A1) works now in all variations (cell formula, array formula, conditional format, data validation, defined names. INDIRECT() in addition (test document "exportindirect.xls") works now, if loaded and re-exported in a fixed Calc (test doc provided by cdunham) Matrix formulas with multiplication and references (bugdoc "Issue_31257_array_function_test.sxc") works. I will attach more test documents to this issue, containing more general formulas (usage of all possible operands, operators, functions, operator precedence, ...). @dpoirier: The attached bugdoc "Formula Problem Doc.xls" is not valid. Just delete the spaces in all cells left to the #VALUE! cells, and the formulas will work in Calc and Excel.
set target to OOo 2.0
Created attachment 18938 [details] archive containing test documents for formula export
I have attached a ZIP archive containing a few test documents. formula_import_export_biff8: Contains several sheets with basic formula features, starting from simple values, over simple operations (addition, subtraction, ...), cell references, complex reference operations (range, intersection, list), function calls with variable number of arguments, and usage of parentheses at all possible places. Note that Calc lacks support for explicit error codes (i.e. =#VALUE!), constant arrays (i.e. ={1,2,3}), and range/list operations (i.e. =name1:name2) in formulas, cells using these features may show errors after import/export. Also, support for natural language references (2nd sheet) is very restricted. Both export filters (BIFF5, BIFF8) can be tested. formula_special_export.sxc Contains features that are partly not available in Excel or Calc's import filter. See first sheet in this document for more details. Both export filters (BIFF5, BIFF8) can be tested. functions_export.sxc Contains a list with all functions known in Calc AND Excel. Calc allows to enter functions with wrong number of arguments, Excel does not. Export filter has to filter these functions, it replaces them by a #NA error cell. Both export filters (BIFF5, BIFF8) can be tested. Note that some functions are not supported in BIFF5 (i.e. HYPERLINK) and will be replaced by #NA too. functions_import_biff?.xls These 5 documents are supposed to test function import from BIFF2, BIFF3, BIFF4, BIFF5/7, and BIFF8. They can be used to test a round trip (import, reexport) . name_import_export_biff?.xls These 2 documents use defined names in various contexts. They contain user- defined names, build-in names (i.e. print range, filter range), sheet-local and global names; and use them directly in cells and in functions (i.e. it is possible to sum up the print range of a sheet).
reopened
back to QA
fixed
verified in internal build cws_dr27
closed because fix available in OOo1.9m62
*** Issue 40835 has been marked as a duplicate of this issue. ***