To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=5658
------- Additional comments from fdservi...@openoffice.org Wed Jun 3 13:40:32 +0000 2009 ------- I think I now understand the problem better. If, in the real world, you are trying to use OpenOffice as an Excel substitute, then I recommend that you do not. As I have said time and again, "If you need Excel, use Excel". OpenOffice is not compatible in many ways. Also Excel is inconsistent in its evaluation of formula as (I think) we all know, so absolutely: Use Excel to read Excel sheets. @karlis - "To those who preach purity. Fine! But then do not ignore numbers formed as text silently. Either pop up a notice when opening the file or put appropriate error notice in the cell that attempts to use numbers-formed-as-text. Silent ignoring is the only wrong approach!" - I agree. @kkeane - "Which makes me wonder why he even cares about it one way or the other?" - for the reasons specified e.g. assuming you can accept this as a proper formula "=IF(ISERROR(VLOOKUP($A6,'Path to Workbook\[Workbook.xls]Sheet1'!$A$1:$F$500,5,FALSE)),"",VLOOKUP($A6,'Path to Workbook\[Workbook.xls]Sheet1'!$A$1:$F$500,5,FALSE))" then this formula will be needed to test whether a result is Debit or Credit and then add and subtract figures across a sheet correctly "=IF(SUM(E6,G6,I6,K6,N6)-SUM(F6,H6,J6,L6,M6)>0,SUM(E6,G6,I6,K6,N6)-SUM(F6,H6,J6,L6,M6),"")" If you want a detailed explanation of why this should work and nothing else, I can give it to you, but I wish such complexities were not necessary. "Training people? Come on, get real." - Oh dear! @philhibbs - seems like a sensible suggestion. Treat Excel created formula the Excel way. To repeat myself, this is similar to the Transition Formula Evaluation option in Excel, but executed automatically. I think, however, that there should be a warning issued when such a sheet is opened to prompt the user that Excel rules apply. @kkeane - you seem to have discovered another Excel "feature"! Importing csv and text files works fine, you can even specify the format of each column imported, but it is not usually necessary since Excel makes a creditable guess in each case. "Even if you later change the cell format to "General" Excel will keep the number as text!" - you are right again, yet another "feature"! "you can save yourself a lot of work formatting the cells by typing in the numbers in the correct format, if you start it with a quote". - This, from an experienced user, beggars belief! There have been a number of extremely sensible suggestions made in this thread to achieve backward compatibility with Excel formula evaluation: 1) Automatically mark cells with text content which will evaluate to numbers. (this can be done in OpenOffice already, but it is not automatic) 2) When opening an ".xls" file, warn the user that this is an Excel type file and then use transition formula evaluation as per philhibbs suggestion. But, what is the correct method of then saving the ".xls" file in a different format, and what would happen if you chose to save an OpenOffice file in ".xls" format? may be the kiss principle is best... 3) have a Transition Formula Evaluation option which is the reverse of the Excel implementation. If you want Excel formula evaluation, tick the box, if you do not, leave it clear. Andrew --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@sc.openoffice.org For additional commands, e-mail: issues-h...@sc.openoffice.org --------------------------------------------------------------------- To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
