need a dynamic source for data validation drop down lists

'DIMINISHING DROP DOWN LIST
Here's a link to a page where I show the technique I use for Diminishing Drop Down lists:

Jerry Beaucaire's Excel Tools

(the file you want is DiminishingDropList.xls)

actually, looks like 2 added columns, seems the key field is required as well,..am testing it now, but will thank you anyway, IS a solution that does not recquire me doing VBA coding ...YAY!!

looks like i can not give you another "star " yet, sorry

Notice the link at the top of the screen called Unanswered Threads? Many contributors click that link first to see which posts have not been answered yet since their last visit. Regardless of how long you wait, once a reply is posted to your thread your thread would never show on that list at all. So bumping your own thread as the first reply is always probably a bad idea.

I would recommend waiting at least 24 hours to bump. Most active contributors come by at least once a day, that gives everyone a shot at your thread on the Unanswered threads list, at least until someone replies.

You're correct. For each "column" where you wish the diminishing to occur you will need a "key" column adjacent to the original list to number the items not used yet, then a new "secondary list" made of only the numbered items, that column would only use the secondary list for its data validation.

It should be simple enough to setup the first set, then just add pairs of additional sets by copying and editing the formulas.

okay, i modified the code in your example to fit my example, however, the key column populates with 0's, and the available teams list also populates with 0's...i'm obviously missing something, but for i cant see what...
have included the modified workbook, haven't changed the dropdowns as yet, seeing as the data is not there to use

Sorry..attached old file,am attaching thenew one