Form control references created in a multiple worksheet MS Excel workbook are imported incorrectly by Calc. Sheet references are changed in the Input Range and Cell Link fields of the Format Control for a Combo Box.
Steps to Reproduce:
1. 0pen a new workbook in MS Excel
2. add a second worksheet - it will be automatically named Sheet 2
3. on Sheet 2, create a table of values to populate a dropdown menu
4. select Developer tab in the ribbon
5. select Combo Box
6. draw a rectangle to create a Combo Box anywhere on Sheet 2
7. right-click on newly created Combo Box
8. select Format Control from context menu
9. click on Input Range field
10. highlight table of values created in step 3
11. click on Cell Link field
12. click on any unused cell in Sheet 2
13. click OK
14. click on any unused cell in Sheet 2
15. save, name and close the Excel workbook
16. open workbook in MS Excel
17. if not already visible, navigate to Sheet 2
18. click on Combo Box
19. select an option from the dropdown menu
20. result - values entered in step 3 are displayed, and selection is shown in cell designated in step 12
21. close the Excel workbook without saving changes
22. open workbook in LibreOffice Calc
23. if not already visible, navigate to Sheet 2
24. click on Combo Box to select an option from the dropdown menu
25. result - no data is displayed in dropdown menu
26. expected result - data from table of values created in step 3 should be displayed
27. right-click on Sheet 1 tab
28. select Delete Sheet option from context menu
29. click Yes when asked to confirm deletion
30. Sheet 2 is now the only active worksheet
31. click on Combo Box
32. select an option from the dropdown menu
33. result - item is selected as expected, and selection is shown in cell selected in step 12
34. close workbook without saving changes
35. open workbook in LibreOffice Calc
36. if not already visible, navigate to Sheet 2
37. display the Form Design Toolbar - View > Toolbars > Form Design
38. activate Design Mode by clicking on edit tool in the Form Design toolbar
39. right-click on Combo Box to display Properties dialog box
40. select Data tab in Properties dialog box
41. view Linked Cell field
42. result - shows Sheet1.[cell designated in step 12]
43. expected result - should show Sheet2.[cell designated in step 12]
44. view Source Cell Range field
45. result - shows Sheet1.[cell range designated in step 3]
46. expected result - should show Sheet2.[cell range designated in step 3]
see actual results described in steps 25, 42 and 45
see expected results described in steps 26, 43 and 46
User Profile Reset: Yes
there are no relevant LibreOffice help references
Created attachment 157486 [details]
sample MS Excel file
Opened test file in latest version of MSExcel for Mac (16.33)
Notice how dropdown box functions as explained by OP.
Opened test file in LO:
Build ID: 9a2fc64b49b4a3067ba78648e3fcc73390717c80
CPU threads: 4; OS: Mac OS X 10.15.2; UI render: default; VCL: osx;
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Observe that dropdown box fails to either display or select any of the values from the previously configured range. Cell D3 does not update automatically when any selection made in dropdown.
The dropdown menu displays a list of blank entries that can be selected with the mouse, but the values are simply not there.
Also confirming that, with the file loaded in Calc, and Form Design mode activated, the control in Sheet 2 points to cell references and ranges in Sheet1:
Linked cell : Sheet1.D3
Source cell range : Sheet1.B11:B16
I also notice a further difference, FWIW :
- the value displayed in the linked cell appears to be different between Excel and Calc. Excel displays the index number of the entry selected (so entry "Selected" = 1, entry 1=2, entry 2=3, etc) , whereas Calc displays the actual selected value from the list.
Also a bug on Windows
Form control not supported in LO 3.3, so not inherited
Version: 220.127.116.11 (x64)
Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8
CPU threads: 2; OS: Windows 10.0 Build 18363; UI render: default; VCL: win;
Locale: en-US (en_US); UI-Language: en-US
(In reply to Alex Thurgood from comment #3)
> Also confirming that, with the file loaded in Calc, and Form Design mode
> activated, the control in Sheet 2 points to cell references and ranges in
> Linked cell : Sheet1.D3
> Source cell range : Sheet1.B11:B16
> I also notice a further difference, FWIW :
> - the value displayed in the linked cell appears to be different between
> Excel and Calc. Excel displays the index number of the entry selected (so
> entry "Selected" = 1, entry 1=2, entry 2=3, etc) , whereas Calc displays the
> actual selected value from the list.
Responding to Alex's comment about different values being displayed in the linked cell (Sheet2, D3 in the sample file). LibreOffice Calc includes a feature that allows the linked cell to display either the selected entry, or the numerical position of the selected entry. The default appears to display the selected entry.
MS Excel does not include an equivalent feature to choose what to display in the linked cell - it displays the position of the selected entry in the linked cell. To make the linked cell in LibreOffice Calc behave like MS Excel, do the following:
1. open test file in LibreOffice Calc
2. if not already visible, navigate to Sheet 2
3. display the Form Design Toolbar - View > Toolbars > Form Design
4. activate Design Mode by clicking on edit tool in the Form Design toolbar
5. right-click on Combo Box to display Properties dialog box
6. select Data tab in Properties dialog box
7. next to the second line 'Contents of the linked cell...' click dropdown menu to reveal a choice between 'The selected entry' OR 'The position of the selected entry'
8. from the dropdown menu, select 'The position of the selected entry'
9. deselect the edit tool in the Form Design toolbar
It would seem that compatibility when opening a document created in MS Excel would be best served by LibreOffice Calc defaulting to displaying the position of the selected entry in the linked cell.
This topic seems to be a separate issue from my original bug submission. In fact, what is displayed in a linked cell for a Combo Box might not be a bug, but a feature request. I am a newbie - the original post is my first ever bug submission. Any advice about how best to submit this second issue would be most appreciated.
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 with the latest version of LibreOffice from https://www.libreoffice.org/download/
If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.
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) from https://downloadarchive.documentfoundation.org/libreoffice/old/
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: https://web.libera.chat/?settings=#libreoffice-qa
Thank you for helping us make LibreOffice even better for everyone!
Retested, as requested by QA Admin. Bug is still present.
Information from About LibreOffice:
Version: 18.104.22.168 / LibreOffice Community
Build ID: 8d71d29d553c0f7dcbfa38fbfda25ee34cce99a2
CPU threads: 2; OS: Mac OS X 10.11.6; UI render: default; VCL: osx
Locale: en-US (en.UTF-8); UI: en-US
Also tested with version 3.3, as requested by QA Admin. As noted by eisa01 in Comment 4, Form Control is not supported in version 3.3. Therefore, unable to test.