User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:40.0) Gecko/20100101 Firefox/40.0
Build Identifier: LibreOffice 22.214.171.124
This applies to v126.96.36.199 as well
Steps to Reproduce:
1. Open document created in MS Excel, save as xlsx
2. Open in LO Calc
3. Dropdown is not visible
[Information automatically included from LibreOffice]
[Information guessed from browser]
OS: Linux (All)
OS is 64bit: yes
Reset User Profile?Not a bug
Thank you for filing the bug. Please send us a sample document, as this makes it easier for us to verify the bug.
I have set the bug's status to 'NEEDINFO', so please do change it back to 'UNCONFIRMED' once you have attached a document.
(Please note that the attachment will be public, remove any sensitive information before attaching it.)
How can I eliminate confidential data from a sample document?
Created attachment 118982 [details]
xlsx dropdown does nto work in Calc
Created attachment 118983 [details]
MS Excel sample file
Note that if I use MS Excel to save the file to a .xls file, the problem remains.
Looking into the matter, it seems that there is a difference in approach between Excel and Calc.
In Excel, you can create the list in cells, then link the cells from the cell which is to be a dropdown. No named range is created.
In Calc, you can create the list in cells, but then you need to name the range. In the dropdown cell, you reference the named range.
I then saved the the file I added the dropdown using Calc. I opened the new file using Excel, the new dropdown I created worked (cell B30), but none of the others (which had been created using Excel) worked any longer.
The Name that was created in Calc to define the range is also not visible in Excel.
Unfortunately, even though govts claim to support open source, they tend to all use MS. Consequently they create forms which one has to complete. If you are an exclusively Open user (and do not have the MS tools), then this can be a problem.
I checked this out in OpenOffice Calc, and it acts in the same way as LO Calc. Thus it is not a regression or bug, but an inconsistency between Excel and Calc. Not sure how you deal with this,
Created attachment 118984 [details]
added a dropdown (cell B30) using Calc
The new dropdown (cell B30) works in Calc and Excel, however, the original dropdowns created using Excel no longer work in Excel.
Win 7 Pro 64-bit, Version: 188.8.131.52 (x64)
Build ID: 37b43f919e4de5eeaca9b9755ed688758a8251fe
Locale: fi-FI (fi_FI)
Version: 184.108.40.206.alpha1+ (x64)
Build ID: 8d16834a9820fb8c983753c3e2a05922885060b9
TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-09-22_21:29:32
Locale: en-US (fi_FI)
Our charity has MS Office on some computers and Libre office on others. I am creating a spreadsheet with drop-down boxes and have been creating this in Excel. However, when I try to open the spreadsheet (Template) in Calc, the drop-downs are not there.
I am using a sheet I with a columns to populate each drop-down box.
In Excel the validation criteria in Data is "Lists", In Calc "Cell Range"
In Excel the formula is <=Lists!$x$n:$x$n>
In Calc the formula is <$LISTS.$x$n:$x$n>
** Please read this message in its entirety before responding **
To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.
There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.
If you have time, please do the following:
Test to see if the bug is still present on a currently supported version of LibreOffice
(5.2.7 or 5.3.3 https://www.libreoffice.org/download/
If the bug is present, please leave a comment that includes the version of LibreOffice and
your operating system, and any changes you see in the bug behavior
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave
a short comment that includes your version of LibreOffice and Operating System
Please DO NOT
Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not
appropriate in this case)
If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)
2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword
Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa
Thank you for helping us make LibreOffice even better for everyone!
In this post (https://www.amazon.co.uk/Converter-VicTsing-Transmission-Computer-Projector/dp/B01M1IQ7F9/ref=pd_sim_23_4?_encoding=UTF8&psc=1&refRID=3MVH3EK0XMVE1QEBHXD7) someone suggest the following as possible workaround:
When you use "Data", "Validity", from the "Allow" drop down select "Cell Range" instead of "List", and then use a range of cells to list the available options.
This works saving to xls or xlsx from LibreOffice 220.127.116.11, possibly also from earlier versions but I have not tested.
I haven't tested but I thought it may help someone perhaps.
It would be great of course if LO could manage directly to save this info correctly in xls and xlsx files... for improved compatibility.
I can confirm that opening the "MS Excel sample file" attached to this bug in LO 18.104.22.168 (x64) installed on Win10 Pro x64 OS, I cannot see or select the dropdown cells in the file they appear as standard cells with "Please Select" as text content.
So bug is still present.
(In reply to Richard Léger from comment #9)
> In this post
> ref=pd_sim_23_4?_encoding=UTF8&psc=1&refRID=3MVH3EK0XMVE1QEBHXD7) someone
> suggest the following as possible workaround:
Bad copy paste!!!
I meant this post:
Sorry I could not correct my original post after posting...
(In reply to Richard Léger from comment #10)
> Sorry I could not correct my original post after posting...
Yep, unfortunately this is not yet implemented: https://bugzilla.mozilla.org/show_bug.cgi?id=1144473
I opened the Excel file and the dropdowns are all there.
Saved the file using Calc, opened that, all is fine.
Build ID: 1:5.3.3~rc2-0ubuntu0.16.04.1~lo0
CPU Threads: 4; OS Version: Linux 4.4; UI Render: default; VCL: gtk2; Layout Engine: new;
Locale: en-ZA (en_GB.UTF-8); Calc: group
Build ID: ddf8539d97ce044b7df8d51d6ec72ec864b40fb8
CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk2;
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-06-04_22:10:59
Locale: en-ZA (en_GB.UTF-8); Calc: group
also opened the excel file using
Linux Mint MATE (don't think this is material, but)
Build ID: 1:5.1.4-0ubuntu1
CPU Threads: 1; OS Version: Linux 4.4; UI Render: default;
Locale: en-ZA (en_ZA.UTF-8)
IT PRESENTED SAME PROBLEM
When I open the ODS saved in v5.5, the dropdowns are all there
It seems it must have been fixed sometime between v5.1 and v5.3
Fantastic news. Let's tweak status to WFM as we don't know the fixing commit.
My version of Windows 10 where I tested it is: 1607 (OS Build 14393.1198)
If that can be of any help for further testing.
Any chance the fix can be backed ported in the current stable version of LO?
When is the next stable version of LO coming out?
(In reply to Richard Léger from comment #14)
> My version of Windows 10 where I tested it is: 1607 (OS Build 14393.1198)
> If that can be of any help for further testing.
> Any chance the fix can be backed ported in the current stable version of LO?
> When is the next stable version of LO coming out?
Elmar said the fix is already in 5.3.3, which is the current stable version.
This bug is NOT fixed. I've tried it on two Ubuntu machines running 22.214.171.124, one Ubuntu machine running 126.96.36.199, and a Mac running 5.0. As described elsewhere, using LO Calc to add a validity rule of any kind (not just a list) to a cell in an empty column, then saving it to .xlsx, then re-opening it (whether in Excel or LO Calc), reveals that the validity rule wasn't saved at all. However, putting e.g. a space on line 1000 of that column before saving to .xlsx prevents the bug from arising. Presumably, for .xlsx, LO Calc doesn't bother saving anything past the last non-empty cell in a column, even if formatting or validity rules are applied to those subsequent cells.
This isn't a problem when saving to .ods, and I don't think it ever was.
This post sums it up nicely, and also confirms the bug still exists in 188.8.131.52:
I think this is part of a more general problem: LO Calc doesn't save any types of formatting to .xlsx, such as cell colour, after the last "empty" row, where empty means no values entered.
*** This bug has been marked as a duplicate of bug 41425 ***