Multiple Dropdowns with Hundreds of Options in the List

[faygie]

I am setting up an Excel program for a catering company. They have a list of hundreds of menu choices. Each customer orders many items. I'm trying to set up a template where there are 100 rows per customer and each row contains a dropdown with the same list of menu options.

I had originally created a simple dropdown list in each cell, using a named range with all the menu choices.

However, it is very time consuming to fill out. I would like to have an autocomplete option for each cell. I was able to do it in VBA using a combobox but I dont think I can have a hundred combo boxes per customer.

Any ideas would be greatly appreciated.

Thanks.

 

[PKFillmore]

By Autocomplete do you mean you want to have default values for each customer? *(and have them change it if they don't want it?)

 

[faygie]

No, there are no default values. Each customer has an empty worksheet with 100 rows. Each row is a dropdown list, all based on the same named range which is a list of food.

 

[PKFillmore]

Can you explain what you mean by auto-complete ?

ie when customer is filling out form that have to either a) type the whole item or b) pick from large drop down list

 

[faygie]

Now it's just a simple dropdown list from the data validation menu. They have to pick from a large drop down list. I want it to auto complete, sort of like what google chrome does- it gives you suggestions as you type...

 

[sous2817]

Have a look here: http://www.contextures.com/xlDataVal10.html

 

[faygie]

I saw that before I started this thread but as I stated above, I can't use a combo box with VBA because I need 100 rows (drop down lists) per customer so I can't have 100 combo boxes or else my workbook will be extremely heavy and impractical to manage.

 

[sous2817]

then it sounds like you'll need to find a different approach all together...

 

[My Aswer Is This]

So a customer places an order and each order is placed on a new sheet is that correct?
Each row represents a new item they ordered is that correct?
How many columns are used for each customer?
I would think a UserForm ListBox would be a good plan. The customer selects from the listbox the items they want to order. When you hit a submit button all the items the customer orders in put in separate rows in a single column. Is it necessary for each customer to have a separate sheet?
Instead of having 100 option buttons on a Userform we have a 100 items shown in a listbox they choose from.

Would this work?
We could have a separate sheet for each customer if that is necessary.
If you think this plan may work I could help you set this up.

 

[faygie]

I know I need a different approach and that's what I'm asking for. Do you have any ideas of what I should do?