I’m using:
Version: 4.2.8.2
Build ID: 420m0(Build:2)
I have created some drop-down boxes and validation rules on the worksheet. I all works fine with data.
I replicate the validation rules to empty cells so that a user may enter new data as per the rules.
But when I reopen the file, there are no validation rules or drop downs. There’s just plain cells.
What can I do to get around this?
BTW, I’m saving it as an XLSX file for the user to enter on Excel.
In order to save the drop down list in validity rules to an Excel file format you need to use a range of cells for the list of options as this is the only method for a drop down list that is available in Excel.
Edit.
I suspect that Calc is seeing empty cells as unused and therefore does not save that part of the worksheet. I was able to confirm the problem in Sheet2 of the attached example. Untitled 22.xlsx
It seems to be possible to work around the problem by either
1). having a none empty cell at the end of your list, I entered “.” in Cell A21 on Sheet2_2, (" " might be OK).
2). set the background color of the cell at the end of your list, I set Cell A21 background to white on Sheet2_3
Unfortunately this workaround only seems to work if the document is saved from Calc. If you open the document using Excel it is OK but after save again from Excel then the blank cells will still be missing the drop down selection. If I think of a way around this I’ll post back again.
Note if you might have empty columns you could set the cell of every column after your last data row to avoid losing any empty columns of drop down lists.
Thank you mark_t for your answer. That is what I’m using. The drop-down remains when I reopen the file, on rows that have data. But on the blank rows, the drop-downs are gone. Is there something I can do about it? Thanks!
OpenDocument doesn’t save empty cells either.
Saving a spreadsheet document in .ods does not seem to have the same problem with missing the drop down list on empty cells. There seems to be a difference in what is treated as an empty cell. In this case it seems that a cell with a drop down data validity list is considered as empty when saved in .xlsx but not when saved as .ods. It is not considered part of the used range of the worksheet and so the data validity drop down list is not saved for that part of the spreadsheet.
@rautamiekka when I reopen my ods file, the formatting and rules are all in tact. So mark_t is correct. But the issue is when I save it as Excel.
@itsols, Did you try adding some text or background format to the end of the table? If you save from LibreOffice as .xlsx, then re-open this .xlsx in LibreOffice, you should still see the drop down. If you open from Excel the drop down is still present. After save from Excel, re-open from Excel is OK, but re-open again with LibreOffice no longer has the drop down lists.
When I tried this earlier I used 5.2.0.4, but just tried again with 5.2.1.2 with the same symptoms. I’m using 64 bit versions on Windows 8.1. I’ll check bugzilla and raise bug if not already raised.
@mark_t My primary concern was giving the file to a user in Excel format. And your method worked. I literally followed your instructions and put a dot on the last row for every column… As for coming back to ODS, I’m not sure - I did not try it. I guess you’re right. It must be a bug. But thanks a lot for your help on this!
I tested with Calc v 5.4.0.3 and the problem still persist, but only for XLSX format, if I save in ODS or XLS it works how is expected