limit on the delimited list for Data Validation.

[Aleksandra]

Hi,

Is there a limit on the comma delimited list for Data Validation in Excel
2007?

I know that it was 255 character limit per cell in previous versions of
Excel, and I found that new number of characters that can be stored in a cell
formatted as Text is 32k in Excel 2007.

I am populating Data Validation list programmatically, and I can add really
long list of items. Excel 2007 can will save those long Validation lists
without any problem, and when I open file in XML format (Sheet1.xml) it shows
that all the items in Data Validation list are saved correctly.

The problem is when I try to reopen xlsx file again, I am getting an error
(Excel found unreadable content...) and after that ‘Excel Removed Feature:
Data Validation...’. This is happening only if the comma delimited list has
more that 255 characters.

I am wondering if there is any way to read the list from XML and repopulate
Data Validation because the entire list is saved in XML format. Any
suggestion will be helpful.

Thanks.
Aleksandra

 

[chuck buchanan]

I find it easier to populate a range of cells with the list of valid values,
give that range a name (i.e., myRange), and then for the Data Validation
Source field provide "=myRange".

 

[Aleksandra]

I can not use ranges due to the design limitations. Is this Excel 2007
limitation?Is there a workaround that I can use lists saved in XML?

Thanks.
Aleksandra