List Drop Down Boxes

ds_robb

Board Regular
Joined
Aug 25, 2002
Messages
72
I am trying to create a very large spreadsheet which has numerous drop down list boxes, although all the lists that are referred to are the same four throughout the sheet, Excel appears not to want to save the spreadsheet. Is there a limit on the number of references to drop down lists?

Using Excel 2002
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
ds_robb,

Welcome to the board (from a newbie to another :))

Have You consider to use DataValidation instead?

Kind regards,
Dennis
 
Upvote 0
Hi Denis. I was using Data Validation and selecting List and a name of a list I had predefined. Although there are only four list names, I use them repeatedly throughout the spreadsheet. Basically the Spreadsheet is a diary format with up to 100 people per day... I use four lists per person (so each day column would have 400 references to drop down lists (4 Lists X 100 people). I can seem to copy this format across 11 days (which would be 4,400 references and after that Excel reports an error on formatting when I try to say the file and all the references past day 11 are lost)
 
Upvote 0
Bit of further information - the absolute limit seems to be 4092 (very close to 4096, so looks like an array size limit of some sort within Excel). Anyone got any ideas how to get around this, amend the array size or anything else smart?
Regards
Duncan
 
Upvote 0
Further information still - it isn't size dependant, it seems to be the way the lists are structured. If you Have four drop down lists occupying say (A1,B1,A2,B2), you can only copy these a limited number of times... if you have the same for but instead of below each other, you arrange them side by side (i.e. A1,B1,C1,D1), you can copy them as many times as you like.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top