Dropdown not working for large data

Question

Hi Guys

I am developing an integration between Excel and a System Software. Basically the System software downloads data in Excel format, fills it and uploads it back and the DB is updated after the uploaded.

However I'm facing an issue. The Mac version of excel is not behaving properly however the Windows version is.

Here is my problem:

Download the following file and open in excel On a Mac:

[code]

[url=http://www.mediafire.com/view/?9uas7adkeh6x5bu]Free Cloud Storage - MediaFire[/url][/code]

My requirement:

To have a dropdown on sheet '11-JUL-2012' , cell E7 , which shows the data from sheet 'Drop_Down_Data' , cell A9 to A35323.

If you click on the cell E7(on first sheet) you can see there is an indicator for drop down , but there is no value loaded.

Now try this:

Select the cell, E7 and Go to Data->Validate .

Change

[code] '=Drop_Down_Data!$A$9:$A$35323'[/code] to [code]'=Drop_Down_Data!$A$9:$A$10000'[/code] . Voila! The drop down works.

It basically is showing the data from Cell A9 to A10000. Now change it to [code]'=Drop_Down_Data!$A$10000:$A$35323'[/code]. Again , the drop down works. So it is now showing the data from A10000 to A35323.

So it means there is no wrong data in the list, and I see no reason why the drop down is not working from line A9 to A35323.

Funny thing is, if you download the file and open it using Excel 07 in Windows, it will work perfectly.

Any idea why this behavior is so?

Answer 1

Hello to everyone

I have the same problem but the number of values in the list is 1024 (10^2).

Is there a way to set up from 10^2 to 10^15?

I have the problem using a MacBook Air with OS X 10.9 and Excel 14.3.9.

I hope someone could help me... thank in advance.

Answer 2

Now again, the same excel works fine in Office 07 in Windows and Office 10 in Windows. I am not sure why it is not working in Mac Office.

I'm not sure why you're not sure - as I wrote before, the reason is that MacXL limits dropdowns to 32,767 entries. You can confirm it for yourself if you wish: 32,767 works, 32,768 doesn't.

FWIW, that is exactly the SAME limit in my installations of WinXL 2007 and WinXL 2010.

Perhaps it's a 32 bit vs 64 bit issue, instead of Mac/Win???

I haven't confirmed it on my Mac, but on my WinXL installation, the number of displayed entries in the dropdown doesn't limit actual validation - the 32,768th entry is validated, while an entry that isn't on the list is rejected.

Answer 3

Also, in the drop down appears in 2-3 seconds in Windows so it's not an issue.

Answer 4

Hi

The excel is safe, but anyway :)

It's not that we expect the user to actually scroll through the 35k entries.

The reason we have it in the list is that for certain attributes we have a limited set of valid data. e.g.. say the attribute is Day so we can only support Mon , Tue...and so on.Now the user may unknowingly enter Monday instead of Mon and if it gets into the system, the data will be corrupt.

Now in the system we use, if user gives an invalid data we check it against the backend DB to validate the data. However we can't do that on an excel.

So we download all the valid set of data in the excel itself, so that user is forced to have a value from the list as we don't allow any other value.

The other alternative is to read each data from excel and validate against backend, but it's a performance killer.

Now again, the same excel works fine in Office 07 in Windows and Office 10 in Windows. I am not sure why it is not working in Mac Office.

Answer 5

I rarely open a workbook from an unknown source, so I can't verify your data, but I'm certain that the problem is not with your data, but the size of your range.

I don't think I've seen a published XL2011 limit, but previous versions of MacXL (and all WinXL versions prior to XL07) limited the number of cells in a validation dropdown list to 2^15 - 1 (32,767) - i.e., the maximum value that can be stored in an Integer variable. Sounds like XL07 may have switched to a Long.

Is scrolling through 32K entries really something you expect your users to do? Even in a sorted list, that takes a significant time just to get to the end (30+ seconds in my informal test).