Description: 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] Actual Results: see actual results described in steps 25, 42 and 45 Expected Results: see expected results described in steps 26, 43 and 46 Reproducible: Always User Profile Reset: Yes Additional Info: there are no relevant LibreOffice help references
Created attachment 157486 [details] sample MS Excel file
Confirming. 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: Version: 7.0.0.0.alpha0+ 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 Calc: threaded 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: 6.4.0.3 (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 Calc: threaded
(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 > 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. 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.
Dear Willis, 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! Warm Regards, QA Team MassPing-UntouchedBug
Retested, as requested by QA Admin. Bug is still present. Information from About LibreOffice: Version: 7.2.7.2 / 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 Calc: threaded 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.