Apache OpenOffice (AOO) Bugzilla – Issue 80318
Data range validity : percent and date format not respected in the list
Last modified: 2017-05-20 11:11:42 UTC
Hi, Open the sample document : - click in cell B16 to display the list, data are not displayed in percent formatting - click in cell E16 to display the list, data are not displayed in date formatting Kind regards - Sophie
Created attachment 47287 [details] Sample displaying the wrong formatting
Hi Daniel, please have a look at this one. Frank
started
Excel inserts the formatted visible values of the source cells into the dropdown list, but after selecting a value from the list, it inserts the raw unformatted cell value into the destination cell, without changing its number format. To fix this issue, we need to store the display string and the underlying value of a cell in the dropdown list. dr->er: with the current implementation using a formula cell and its result matrix it is not possible to transport the formatted values (source code changes from issue 56566). Not sure how to do this, maybe we need the possibility to ask a formula cell for the result range instead of the result matrix, and to manually process the range, as it was before issue 56566.
See also issue 82165.
Known limitation of the feature implemented with issue 56566. Since the virtual formula cell used to calculate the arbitrary matrix (doesn't have to be a range of cells) isn't even part of the document, there is no way to ask it for a matrix range that matches the source range. Matrix needs a mechanism to transport number formats in such special cases. Certainly nothing for 2.4 anymore.
*** Issue 82165 has been marked as a duplicate of this issue. ***
Hi, perhaps this issue could be linked to issue 45291 ? Yves.
Issue 45291 is about something different.
Here is a workaround. Suppose you want some times to appear in the combo box, and these times are in the range F1:F4, type the formula TEXT($F$1:$F$4;"[HH]:MM:SS") in the source textbox in the Validity dialog, once you have selected Cell range as a criteria. For percentages, use something like TEXT($F$1:$F$4;"##0.00%"). Note that if you use the sorting option, the values are sorted as text, not as numbers and therefore 75% will be between 7% and 8%.
This problem first appeared in OO v2.3 and continues in v2.4. The problem does not occur in v2.2.1. This problem also when using time formats. The work around works if the spreadsheet is only required to work in OO but if the same spreadsheet has to work in Excel as well it won't work because OO v2.3 and v2.4 now use a semicolon as a separator and not a comma which is required by Excel. OO v2.2.1 accepted a comma.
Issue 97857 has a patch that looks like it should fix the portion of this issue that deals with date values.
I just filed a similar bug with the LibreOffice folks (it's been plaguing me for years). Just in case anyone is interested in discussing amongst the two dev groups: https://bugs.freedesktop.org/show_bug.cgi?id=53543
Reset assigne to the default "issues@openoffice.apache.org".