Re: [sc-dev] Values depending on linked and not available spreadsheet - xls-viewer OOO
Eike Rathke schrieb: Hi Cor, On Thursday, 2008-04-17 10:10:19 +0200, Cor Nouws wrote: Easy to fix as you suggested. Only saving as xls and re-opening, again shows #NA ... Saving as ods does save the results. It seems that interim results of external references aren't saved to Excel file format. Daniel? They are saved as long as the referenced range does not exceed a size of 1024 cells. This has been done to prevent memory problems with files that reference an entire sheet in an external document, see http://www.openoffice.org/issues/show_bug.cgi?id=70418. AFAIK, Excel does only save the results of non-empty external cells, that would be an option for the export filter too. Daniel - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Values depending on linked and not available spreadsheet - xls-viewer OOO
Hi Cor, On Thursday, 2008-04-17 10:10:19 +0200, Cor Nouws wrote: Easy to fix as you suggested. Only saving as xls and re-opening, again shows #NA ... Saving as ods does save the results. It seems that interim results of external references aren't saved to Excel file format. Daniel? Eike -- OOo/SO Calc core developer. Number formatter stricken i18n transpositionizer. SunSign 0x87F8D412 : 2F58 5236 DB02 F335 8304 7D6C 65C9 F9B5 87F8 D412 OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS Please don't send personal mail to the [EMAIL PROTECTED] account, which I use for mailing lists only and don't read from outside Sun. Use [EMAIL PROTECTED] Thanks. pgpWFcZ25mIsx.pgp Description: PGP signature
Re: [sc-dev] Values depending on linked and not available spreadsheet - xls-viewer OOO
Hi Cor, the problem is not the missing linked file, at least not with OOo2.3. The problem is the unsorted search vector used with the VLOOKUP functions in column H. Excel requires a sorted search vector for the vlookup as used in the column as well as Calc do. At least they require this in the help for the function. So they break the own rules. The problem can be solved either by sorting the search vector or setting the parameter (zero or false) for unsorted search vectors in the vlookup formula. Frank Cor Nouws wrote: Hi *, I've been asked my opinion about a nasty problem, described here [1]. An Excel file does not show all correct values in OOo. The final analyses (on the forum): - the xls file contains reference to another spreadsheet; - the another spreadsheet is not available; - Excel viewer is able to show the right values; - OOo is not. So it looks as if the Excel viewer uses some data in the file, that OOo does not. I cannot find anything about that in IssueTracker. Also at [EMAIL PROTECTED] no reply. Does anyone know this problem? Is there an issue describing it? Thanks, Cor 1] http://www.oooforum.org/forum/viewtopic.phtml?t=59180highlight=excel (includes screen prints and test file) -- Sun Microsystems GmbH, Frank Stecher Quality Assurance StarOffice Calc Nagelsweg 55 Hamburg 20097 DE Phone x66636 / +49 40 23 646 636 Email [EMAIL PROTECTED] http://www.sun.de/staroffice Sitz der Gesellschaft: Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Amtsgericht Muenchen: HRB 161028 Geschaeftsfuehrer: Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Values depending on linked and not available spreadsheet - xls-viewer OOO
Hi Cor, On Thursday, 2007-10-18 11:38:21 +0200, Cor Nouws wrote: Frank Stecher wrote (18-10-2007 10:03) The problem is the unsorted search vector used with the VLOOKUP functions in column H. Excel requires a sorted search vector for the vlookup as used in the column as well as Calc do. At least they require this in the help for the function. So they break the own rules. They probably don't break the rule, but for unsorted data the so-called sorted range lookup may deliver any result that fits for a less-or-equal query. The actual result delivered depends on the algorithm used. It is coincidence that Calc for that data constellation delivers #N/A. So whoever used the data for vital tasks in Excel or some viewer should carefully check the results it delivered with reality ;-) Eike -- OOo/SO Calc core developer. Number formatter stricken i18n transpositionizer. SunSign 0x87F8D412 : 2F58 5236 DB02 F335 8304 7D6C 65C9 F9B5 87F8 D412 OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS Please don't send personal mail to this [EMAIL PROTECTED] account, which I use for mailing lists only and don't read from outside Sun. Use [EMAIL PROTECTED] Thanks. pgp9cygRQqQKk.pgp Description: PGP signature