Hide used names in drop downbox.

[Robb27]

I found something I would like for my workbook. I found it on MVP Debra
Dalgleish's page http://www.contextures.com/xlDataVal03.html. The original
code is from Peo Sojblom and it's about hiding previously used items in a
drop down box. I have my list of dealers on a sheet (GamesDealt):

Col A is a list of Dealers (casino type).
col B is their hire date.
From Col C thru I has headings of: BJ PG WH TCP BS MB CR (games we offer)
I have an "X" in the row under the colunm of what game(s) they deal. Most
dealers deal more than one game.
If you put an "X" in the cell for what game they deal it copies their name
from Col A to Col L - R with the same headings as Col C thru I. - So if they
deal a game, x under what game they deal and it copies A1 to another set of
columns. The columns L-R are for the dynamic ranges for each game. I did this
due to high turnover rate of dealers (sucky job). I need to be able to add or
subtract dealers from Col A.
I did this because I have another sheet called "Monday" actually 7 of them 1
for each day of the week. It looks like A3 "BJ9" B3 is a data validation cell
and there are 17 of them (one for each game).

I want 17 drop boxes on sheet "Monday", each with a list of only the dealers
that deal that game, and once I use that person on a game, I don't want to be
able to pick them for another game on that same sheet. Hence Debra's page.

(Refer to Columns L-R, Those columns are dynamic named ranges.)

Peo's formulas are:

array formula to make blank cells move to the bottom of the list
IF(ROW(A1:A6)-ROW(A1)+1>COUNT(B1:B6),"",INDEX(A:A,SMALL(B1:B6,ROW(INDIRECT("1:"&ROWS(A1:A6)))))) <not my cell ranges>

and to make sure each name is used once,
IF(COUNTIF(Schedule!$B$2:$B$7,A1)>=1,"",ROW()) <not my ranges>
I just can't make it work on my pages. - Can anyone point me in the right
direction. I have the formulas from her site, I just can't make it work on my
page.


How can I fit this to my needs?

Thanks in advance.

Robb

 

[Debra Dalgleish]

I just added a sample file that may help you set up the weekday sheets:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for "Assign Qualified Employees to Single
Task per Day"

 

[Peo Sjobom]

Nice, now I don't have to do it. I felt for a moment it was my post to
answer but the misspelling of my name held me back <g>

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Debra Dalgleish]

Well, it's pretty hard to spell -- you seem to be having a bit of
difficulty with it yourself. <g>

 

[Peo Sjoblom]

LOL! That is why I never help myself

 

[Robb27]

Sorry I misspelled your name Peo. Thanks for being here.

Robb

 

[Robb27]

Thanks Debra, the new example on your page helped. Sometimes it takes a
different look at things.
I had to post an appology to Peo for butchering his name! lol

Robb

 

[Peo Sjoblom]

It was a joke Robb, I can't even spell the damn thing myself <bg>

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Debra Dalgleish]

You're welcome, and thanks for letting me know that the sample file helped.
I'm sure Poe Sojolbloom wasn't really upset about the misspelling, and
thanks for getting my name right!

 

[Dave Peterson]

The real amazing thing about that guy's name is the way he pronounces it:

Tom Smith

 

[Robb27]

lol Dave. Nothing like it's spelled huh?

The real amazing thing about that guy's name is the way he pronounces it:

Tom Smith

 

[Robb27]

Hi Debra,

After reviewing your new example, the light bulb came on about seeing how it
works. I did come up with one question though. I have 56 dealers total and
this is what I did.
I took a shortcut:
I copied my dealer list to your Col A (A2-A57).
I thought I could just drag the fill handle down and copy each cell down to
Row 57 in Col L thru R as well as T thru Z, AC thru AI and AK thru AQ.
So, I extend all columns down to row 57.

I can’t see why, but in Cols L-R and AC-AI (after I leave your original 7
rows), the formula changes from:
{IF(ROW($A2:$A8)ROW(A2)+1>COUNT(T2:T8),"",INDEX($A:$A,SMALL(T2:T8,ROW(INDIRECT("1:"&ROWS(A2:A8))))))}

to this:

IF(ROW($A9:$A15)ROW(A9)+1>COUNT(T9:T15),"",INDEX($A:$A,SMALL(T9:T15,ROW(INDIRECT("1:"&ROWS(A9:A15)))))) – (this changes every 8 rows)

If I’m in M2 and I hit ctrl/shift/enter for the array, it highlights down 7
rows.

Some of the names in each column repeat...up to 7 times in the same column.
It’s happening when I don’t put some x’s in some of the games the dealers
deal. When I change the x’s in your example set of 7, I don’t have any
problem. - I take away x’s or put some in, and I can see your example
working.
But, from row 9 on down, after adding the balance of my dealers and copying
all the cells down, I get more than one of the same dealer in columns M-R.
Then I realized, I don’t have any missing x’s in my dealer list under the BJ
column because all my dealers deal blackjack. Where they diversify is in the
other games. What am I missing?

btw...your name is not nearly as hard to pronounce, as opposed to...hmmmm?
<grin>

Thank you for your time.
Robb

 

[Peo Sjoblom]

Robb,

Hi Debra,

After reviewing your new example, the light bulb came on about seeing how
it
works. I did come up with one question though. I have 56 dealers total and
this is what I did.
I took a shortcut:
I copied my dealer list to your Col A (A2-A57).
I thought I could just drag the fill handle down and copy each cell down
to
Row 57 in Col L thru R as well as T thru Z, AC thru AI and AK thru AQ.
So, I extend all columns down to row 57.

I can't see why, but in Cols L-R and AC-AI (after I leave your original 7
rows), the formula changes from:
{IF(ROW($A2:$A8)ROW(A2)+1>COUNT(T2:T8),"",INDEX($A:$A,SMALL(T2:T8,ROW(INDIRECT("1:"&ROWS(A2:A8))))))}

to this:

IF(ROW($A9:$A15)ROW(A9)+1>COUNT(T9:T15),"",INDEX($A:$A,SMALL(T9:T15,ROW(INDIRECT("1:"&ROWS(A9:A15))))))
- (this changes every 8 rows)

It will increase in groups of 8 since it is an array formula that has been
entered in one fell swoop over
an array which is 8X7 cells so if you want to use Debra's workbook you need
to select L2:R57 or something like that then array enter

=IF(ROW($A2:$A57)-ROW(A2)+1>COUNT(T2:T57),"",INDEX($A:$A,SMALL(T2:T57,ROW(INDIRECT("1:"&ROWS(A2:A57))))))

The formulas in T2:Z2 can be copied down to row 57 in the regular way and so
do those in AK2:AQ2 but the formulas in AC2:AI2 needs to be dealt with the
same way as those in L2:R57

Btw, this is how you array enter Debra's formula, copy the formula I posted,
then click in the name box (above column A) and type L2:R57, and press
enter, click in the formula bar and highlight the formula that's in there,
replace the formula with the one I posted using Ctrl + V, then finish it
off with Ctrl + shift & enter


-

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Robb27]

Thanks Peo it worked great.
Sorry it took so long getting back, but I have been under the weather. I was
able to understand how Debra did it. I didn't know to select my range prior.
(She had selected 8 rows, then applied the array.) Now, I have another
question. It has to do with the data validation page (monday in her example).
Debra if your still checking in on this thread, feel free to weigh in.
I wanted to use a different page for my dealer road map. The person using
this schedule requested I make it as much like her sheet's orignal "look" as
I can, so she can just jump right in and use it without having to get used to
a new sheet.
I tried to apply data validation to her sheet named "Mon" (now in this
workbook) to no avail. First I tried to use Debra's formula that she used in
the example for me. This:
=OFFSET(INDIRECT("Mon"&B2),1,0,COUNT(INDIRECT("Mon"&B2&"Col")),1)
It didn't work. So I tried to change it to see if it would work. Nope.
Nothing I did worked. I tried to set up my own list, and it wouldn't let me
because the list is on another page. So I used Column AC (on her example
sheet) and named it as a range. This worked but it let me put the same dealer
on 2 different games at the same time. Not good. I can't see why Debra's
worked and I can't make it work. So, did I give you enough info? What do I do
to make it work on my sheet? Again, thanks for the help.