On Thursday, December 28, 2017 at 9:51:51 PM UTC-8, GS wrote:
On Thursday, December 28, 2017 at 6:48:08 PM UTC-8, GS wrote:
Hi Howard,
So if I understand correctly, you want to have a fixed number of cells
contain unique entries in the Cable_1 list and remove those entries once
assigned to a cell until all entries in the list are used, thus destroying
the dynamic range.

Using your _Change event should work fine for creating the dropdown in the
cell below Target. What you also need to do is delete the selected entry
from the Cable_1 dynamic range (Shift=xlUp) so the next dropdown doesn't
include the selected item.

When the list is empty, the _Change event needs to know not to ask "Add
Dropdown?"

Another way:
Have your _Change event open a dialog that lets the user make selections
from a combobox to populate the intended cell with no opportunity to change
the selection after the dropdown has been used to avoid messing up control
over the items remaining in the list.

--
Garry


Hi Garry,

Yes to this question.
When the list is empty, the _Change event needs to know not to ask "Add
Dropdown?"

Ref: Removing items from Cable_1 as used.
On another sheet the named range is a product of this formula, pulled down.
Where it provides a contiguous list of non selected items.

=IF(ROW(A2)-ROW(A$2)+1COUNT(B$2:B$13),"",INDEX(A:A,SMALL(B$2: B$13,1+ROW(A2)-ROW(A$2))))

Whenever an item is selected in a drop down, it is removed from Cable_1, (by
the formula referencing other columns, A:A & B2:B13)

The formula makes a list from row 2 and on down for all remaining items yet
to be selected.

Howard

Ok, but that approach makes all entries in those dropdowns invalid because each
dropdown will auto-update to contain the new list. A user could change it and
thus mess up your control over the remaining list. Using a dialog (as a popup
window) as I described eliminates the ability to change the dropdowns after the
selection has been made. (just saying...)

--
Garry


Yes, each new drop down added will have the updated named range list which is exactly what I want. (NOT allow an item to be selected more than once)

The first drop down, (starter) will have say 12 entries. The second will have only 11 and so on until the last will show only 1.

All I need is a method to count the remaining items in the named range Cable_1.

I will then write some alert code.

Howard