Searchable dropdown list in multiple cells from one source

[thevicioushamster]

Hi,

I'm looking to create a searchable dropdown list in each cell of a calendar, for 10 classes, roughly 2000 dropdowns. At the moment I have non-searchable dropdowns in each cell, which means scrolling a long list of lessons to find the correct one - it would be much easier if I could type the first few letters of a lesson and select from the few matches than from the whole year's lessons.

The problem is that all of the searchable solutions I have seen so far involve a column (or several) per dropdown list, e.g. in a hidden sheet. This is possible with much copy and paste, but hardly efficient, since each of the 2000 odd tables I would need to create would need to mirror any changes to the original.

Is there a simpler way to manage this? I am happy to take VBA solutions, though I'd rather avoid if I can.


Any suggestions?<li-image width="999" height="236" alt="Excel Prob.png" align="inline" id="131161iD12626EEC3F1BA21" size="large" sourcetype="new"></li-image>

 

[Akuini]

By dropdown you mean data validation, right?
I suggest instead of data validation you use a searchable combobox that can appear and hide automatically. So you need only 1 combobox that will pop up when you select a cell in certain range.
You can find an example that I created here:
https://www.mrexcel.com/forum/excel-questions/1089693-data-entry-force-selection.html

 

[thevicioushamster]

Hi Akuini,

Thank you for your reply, I have tried to use your document but my download says the Excel file needs to be repaired, and then removes the VBA, without which it is a bit useless. Can't think why this might be.

Can you send me the code for the macro separately?

Thanks.