Drop Downs created in Excel not working in OO

[edso]

Hi,
I created a drop down list in excel. Saved it as both a .xls and a .xlsx. from MS 2013.
The drop down does not exist when I open it in oo 4.1.1
The list of data for the drop down is still there, but no drop down.

If I create a .xls drop down from inside of 4.1.1, it is visible and can be used by Excel.
If you modify and save the workbook again in excel, it breaks the drop down in 4.1.1 again

Help or insight would be appreciated.

Thanks

[Villeroy]

This works with Excel:
menu:Data>Validation...
validate by cell range or by a list to be entered into the dialog.

Recent example file: download/file.php?id=21777 (cells in column A)

[edso]

Hi Villeroy,

Thanks for the reply.
Your example does a lot of things I don't need. Including any cell you click on in the document is a reference to the drop down list.
you have auto expanding drop down lists. It does work when you save it as a .xls, edit it in excel and then reopen it in oo. I am unclear as to what is making it work when doing the most basic form

In Excel
create a list of 5 names.
setup a data->validation
set drop down box to list
set source to point to range where the list is.
It works.
Open it in oo, it doesn't work
modify the spreadsheet.
reopen in excel it doesn't work.
I am unclear from your example what you are doing which makes the drop down list persistant.
Is it something to do with "auto-expanding"
Thanks

[edso]

Figured it out with more testing.

When the target of the drop down box is on a different sheet and saved in .xlsx format, oo doesn't deal with it correctly and it is lost.
If you then save back to .xls you have effectivly removed the drop down box.

[Villeroy]

This is a limitation of MS Excel. The validation range needs to be on the same sheet which should be no problem having a billion cells per sheet or 700,000 in xls (last cell in xls is IV65536).
The ODF standard (.ods, .odt .odp etc.) allows you to keep the range anywhere in the same document.