Need better solution for dynamic drop down list

[Grilleman]

Hello -

First, I've attached a sample workbook which will likely help you understand what I need. I have been struggling with this for days now and just can't figure it out.

To explain, I am working on an application whereas the user will "import" a list of names which will be stored on a hidden wkst called "Names". The number of names imported will vary widely from one event to another and... "may need to be updated from time to time"... but will likely never exceed 300 in total for any given company event. These names will be the foundation for a dynamic drop down list on the "UserWkst". In the attached workbook you will see that while I have a functional solution, it's rather clumsy. I just want the user to import a list of names associated to a company and then begin creating his list of preferred participants.


What I would like to do is create a macro that will do the following:

a) As a given, assume that once an import takes place, the Company ID, Event Name and the associated group of individual names (i.e., participant names) are added to the normalized list on the "Names" wkst.

b) Next, create named ranges containing the names of each individual associated with each Company ID. Note, these named ranges may need to be dynamic as names may be added or removed from time to time.

c) On the UserWkst, display relevant names associated with the selected Company ID in the drop down list in coloumn F.

d) This is the big one! When on the 'UserWkst" the user selects a name from any cell containing the drop down list, that name is then REMOVED from the drop down list. If the user makes a selection from any drop down list but then deletes his selection, the name is then ADDED BACK to the drop down list.

I hope this is clear and that someone in the community can help me out.

Thank you!

[torachan]

Attached uses UserForm with ComboBox & ListBox.
Just illustrating the advantages of this approach.
Other than loading you table there is no maintenance needed.
No 'Name Managers' or convoluted formulas.
If this approach is of interest I will expand it to transfer the data back and forth to Sheet.
The ListBox can be single or multi-choice.
torachan.

[Grilleman]

Thank you Torachan. May I have a day to study your suggested approach and get back to you?

[Grilleman]

Question? So if I go with this UF method, can each selection made from the UF combobox be assigned to a specific cell on another worksheet?

[torachan]

Most things are possible, a clearer final specification (i.e. dummy spreadsheet with actual format of data available and total result anticipated would be a good starting point).
The data should be desensitized but representative of structure and format.

[Grilleman]

Hey -

I've applied your User Form approach to my actual worksheets (see attached). I've removed all worksheeets, User Forms and code that does not dirctly apply this process.

That said, open the Pairings wkst and click "Assign Players" button. Select a company; the listbox populates with the appropriate name choices. Now I need to be able to select a name in the listbox and apply it any cell in col F of my worksheet. So for example, I might want to start by assigning Alex Bars (2nd name in listbox) to cell F22...and J.P. Holtz (15th name in listbox) to F10 and so on until all the names in my list box have been assigned. End result: Listbox is empty and column F is fully populated with names.

Again, this has and will continue to be a great learning for me. Thank you so much for the assist!!

[torachan]

Hi.
Give the attached a go.
The process is started by clicking in your selected cell in column 'F'
The code is a little cluttered at the moment as sequencing gave me 'brain fog'.
If you select the 'blank' line at the top of the listbox that returns your selected 'cell' back to the listbox.
Also if you want to swap, select your cell (previous selection), select new selection from listbox and they will swap.
torachan.

[Grilleman]

Works perfectly. Just what I needed! Thank you Torachan!