Apache OpenOffice Community Forum

I'm using OOo 2.2.1. I had set the validity of column of cells to use a "Cell range". This range was a list of 33 items in a column off to the side of the sheet. However, I find this method untidy as it tends to get messed up if new rows are inserted or if other such changes are made to the sheet. So I copied the list and then changed the "Allow" drop-down value from "Cell range" to "List" and pasted the copied list into the "Entries" textarea.

This seemed to work fine, just like it had before. All the list items were there. But when I reopened the file later I noticed that the list was cut off on the 13th item. When I checked into it further I discovered that the "Entries" textarea cuts off text after the 255th character once you save the file. Does anyone know if this a bug or an intentional limitation? Is there a remedy? I tried downloading the newest release, 2.3.0, but it behaved the same way.

Thanks!

I would suggest that you stick to the cell range. Assign a range name for the list of valid entries, and use that name in the Data> Validity setup. As long as you delete or insert rows somewhere within the range, it should be automatically adjusted.

kryptech wrote:I'm using OOo 2.2.1. I ... set the validity of column of cells to use a ... list of 33 items ...

This seemed to work fine ... But when I reopened the file later I noticed that the list was cut off on the 13th item. When I checked into it further I discovered that the "Entries" text area cuts off text after the 255th character once you save the file. Does anyone know if this a bug or an intentional limitation? Is there a remedy? I tried downloading the newest release, 2.3.0, but it behaved the same way.

There's no mention of such a limitation in Help. I can't find a bug report in the issue tracker.

I also see some truncation - for me, 292 words (1219 characters) was reduced to 257 words (1074 characters) after an ODS save, all words off the end of the list. Saving as XLS left just the first word in the list! This is with StarOffice 8u5.

To quickly create a list (for anyone else wanting to check this) I used the dummy text in Writer, replacing spaces with soft hard line breaks, and deleting commas, periods, colons & querys, leaving just the apostrophes. I saved the list in Writer. I then pasted into the list entry box of Calc. After saving and reopening with Calc I could copy out of Calc's list entry box into a new Writer doc and use compare & word count to see what was missing.

You could register and raise an issue - please post the issue number back here if you do.

Edit: corrected terminology

I checked this out a little more this morning and realized I forgot to mention something important. The list truncation occurs when I'm working with an XLS file, not the native ODS files. In ODS format the lists work fine. That would explain why the lists work OK while the document is open, but get truncated when the file is saved and reopened. I've never tried making a validity list in Excel and when I tried to in Excel 97 I noticed that when "List" is chosen, there isn't a multi-line textbox like Calc provides. Instead Excel gives a single line and the lines I entered in Calc are strung together with commas replacing the return characters. And guess what? That field has a cap of 255 characters.

So my solution will be to save the problem spreadsheets from their original XLS format to native ODS. That should fix it.