software

I found something I would like for my workbook. I found it on MVP Debra
Dalgleish's page 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 quot;Xquot; in the row under the colunm of what game(s) they deal. Most
dealers deal more than one game.
If you put an quot;Xquot; 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 quot;Mondayquot; actually 7 of them 1
for each day of the week. It looks like A3 quot;BJ9quot; B3 is a data validation cell
and there are 17 of them (one for each game).

I want 17 drop boxes on sheet quot;Mondayquot;, 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 a

array formula to make blank cells move to the bottom of the list
IF(ROW(A1:A6)-ROW(A1) 1gt;COUNT(B1:B6),quot;quot;,INDEX(A:A,SMALL(B1:B6,RO W(INDIRECT(quot;1:quot;amp;ROWS(A1:A6)))))) lt;not my cell rangesgt;

and to make sure each name is used once,
IF(COUNTIF(Schedule!$B$2:$B$7,A1)gt;=1,quot;quot;,ROW()) lt;not my rangesgt;
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
I just added a sample file that may help you set up the weekday sheets:

www.contextures.com/excelfiles.html

Under Data Validation, look for quot;Assign Qualified Employees to Single
Task per Dayquot;

Robb27 wrote:
gt; I found something I would like for my workbook. I found it on MVP Debra
gt; Dalgleish's page www.contextures.com/xlDataVal03.html. The original
gt; code is from Peo Sojblom and it's about hiding previously used items in a
gt; drop down box. I have my list of dealers on a sheet (GamesDealt):
gt;
gt; Col A is a list of Dealers (casino type).
gt; col B is their hire date.
gt; From Col C thru I has headings of: BJ PG WH TCP BS MB CR (games we offer)
gt; I have an quot;Xquot; in the row under the colunm of what game(s) they deal. Most
gt; dealers deal more than one game.
gt; If you put an quot;Xquot; in the cell for what game they deal it copies their name
gt; from Col A to Col L - R with the same headings as Col C thru I. - So if they
gt; deal a game, x under what game they deal and it copies A1 to another set of
gt; columns. The columns L-R are for the dynamic ranges for each game. I did this
gt; due to high turnover rate of dealers (sucky job). I need to be able to add or
gt; subtract dealers from Col A.
gt; I did this because I have another sheet called quot;Mondayquot; actually 7 of them 1
gt; for each day of the week. It looks like A3 quot;BJ9quot; B3 is a data validation cell
gt; and there are 17 of them (one for each game).
gt;
gt; I want 17 drop boxes on sheet quot;Mondayquot;, each with a list of only the dealers
gt; that deal that game, and once I use that person on a game, I don't want to be
gt; able to pick them for another game on that same sheet. Hence Debra's page.
gt;
gt; (Refer to Columns L-R, Those columns are dynamic named ranges.)
gt;
gt; Peo's formulas a
gt;
gt; array formula to make blank cells move to the bottom of the list
gt; IF(ROW(A1:A6)-ROW(A1) 1gt;COUNT(B1:B6),quot;quot;,INDEX(A:A,SMALL(B1:B6,RO W(INDIRECT(quot;1:quot;amp;ROWS(A1:A6)))))) lt;not my cell rangesgt;
gt;
gt; and to make sure each name is used once,
gt; IF(COUNTIF(Schedule!$B$2:$B$7,A1)gt;=1,quot;quot;,ROW()) lt;not my rangesgt;
gt; I just can't make it work on my pages. - Can anyone point me in the right
gt; direction. I have the formulas from her site, I just can't make it work on my
gt; page.
gt;
gt;
gt; How can I fit this to my needs?
gt;
gt; Thanks in advance.
gt;
gt; Robb
gt;
gt;
gt;
gt;
gt;--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.htmlNice, 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 lt;ggt;

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;Debra Dalgleishquot; gt; wrote in message
...
gt;I just added a sample file that may help you set up the weekday sheets:
gt;
gt; www.contextures.com/excelfiles.html
gt;
gt; Under Data Validation, look for quot;Assign Qualified Employees to Single Task
gt; per Dayquot;
gt;
gt; Robb27 wrote:
gt;gt; I found something I would like for my workbook. I found it on MVP Debra
gt;gt; Dalgleish's page www.contextures.com/xlDataVal03.html. The
gt;gt; original code is from Peo Sojblom and it's about hiding previously used
gt;gt; items in a drop down box. I have my list of dealers on a sheet
gt;gt; (GamesDealt):
gt;gt;
gt;gt; Col A is a list of Dealers (casino type).
gt;gt; col B is their hire date.
gt;gt; From Col C thru I has headings of: BJ PG WH TCP BS MB CR (games we offer)
gt;gt; I have an quot;Xquot; in the row under the colunm of what game(s) they deal. Most
gt;gt; dealers deal more than one game.
gt;gt; If you put an quot;Xquot; in the cell for what game they deal it copies their
gt;gt; name from Col A to Col L - R with the same headings as Col C thru I. - So
gt;gt; if they deal a game, x under what game they deal and it copies A1 to
gt;gt; another set of columns. The columns L-R are for the dynamic ranges for
gt;gt; each game. I did this due to high turnover rate of dealers (sucky job). I
gt;gt; need to be able to add or subtract dealers from Col A. I did this because
gt;gt; I have another sheet called quot;Mondayquot; actually 7 of them 1 for each day of
gt;gt; the week. It looks like A3 quot;BJ9quot; B3 is a data validation cell and there
gt;gt; are 17 of them (one for each game). I want 17 drop boxes on sheet
gt;gt; quot;Mondayquot;, each with a list of only the dealers that deal that game, and
gt;gt; once I use that person on a game, I don't want to be able to pick them
gt;gt; for another game on that same sheet. Hence Debra's page.
gt;gt;
gt;gt; (Refer to Columns L-R, Those columns are dynamic named ranges.) Peo's
gt;gt; formulas a
gt;gt;
gt;gt; array formula to make blank cells move to the bottom of the list
gt;gt; IF(ROW(A1:A6)-ROW(A1) 1gt;COUNT(B1:B6),quot;quot;,INDEX(A:A,SMALL(B1:B6,RO W(INDIRECT(quot;1:quot;amp;ROWS(A1:A6))))))
gt;gt; lt;not my cell rangesgt;
gt;gt;
gt;gt; and to make sure each name is used once,
gt;gt; IF(COUNTIF(Schedule!$B$2:$B$7,A1)gt;=1,quot;quot;,ROW()) lt;not my rangesgt; I just
gt;gt; can't make it work on my pages. - Can anyone point me in the right
gt;gt; direction. I have the formulas from her site, I just can't make it work
gt;gt; on my page.
gt;gt;
gt;gt;
gt;gt; How can I fit this to my needs?
gt;gt;
gt;gt; Thanks in advance.
gt;gt;
gt;gt; Robb
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Contextures
gt; www.contextures.com/tiptech.html
gt;
Well, it's pretty hard to spell -- you seem to be having a bit of
difficulty with it yourself. lt;ggt;

Peo Sjobom wrote:
gt; Nice, now I don't have to do it. I felt for a moment it was my post to
gt; answer but the misspelling of my name held me back lt;ggt;
gt;--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.htmlLOL! That is why I never help myself

--

Regards,

Peo Sjöblomquot;Debra Dalgleishquot; gt; wrote in message
...
gt; Well, it's pretty hard to spell -- you seem to be having a bit of
gt; difficulty with it yourself. lt;ggt;
gt;
gt; Peo Sjobom wrote:
gt;gt; Nice, now I don't have to do it. I felt for a moment it was my post to
gt;gt; answer but the misspelling of my name held me back lt;ggt;
gt;gt;
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Contextures
gt; www.contextures.com/tiptech.html
gt;
Sorry I misspelled your name Peo. Thanks for being here.

Robb

quot;Peo Sjobomquot; wrote:

gt; Nice, now I don't have to do it. I felt for a moment it was my post to
gt; answer but the misspelling of my name held me back lt;ggt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Excel 95 - Excel 2007
gt; Nothwest Excel Solutions
gt; www.nwexcelsolutions.com
gt; quot;It is a good thing to follow the first law of holes;
gt; if you are in one stop digging.quot; Lord Healey
gt;
gt;
gt; quot;Debra Dalgleishquot; gt; wrote in message
gt; ...
gt; gt;I just added a sample file that may help you set up the weekday sheets:
gt; gt;
gt; gt; www.contextures.com/excelfiles.html
gt; gt;
gt; gt; Under Data Validation, look for quot;Assign Qualified Employees to Single Task
gt; gt; per Dayquot;
gt; gt;
gt; gt; Robb27 wrote:
gt; gt;gt; I found something I would like for my workbook. I found it on MVP Debra
gt; gt;gt; Dalgleish's page www.contextures.com/xlDataVal03.html. The
gt; gt;gt; original code is from Peo Sojblom and it's about hiding previously used
gt; gt;gt; items in a drop down box. I have my list of dealers on a sheet
gt; gt;gt; (GamesDealt):
gt; gt;gt;
gt; gt;gt; Col A is a list of Dealers (casino type).
gt; gt;gt; col B is their hire date.
gt; gt;gt; From Col C thru I has headings of: BJ PG WH TCP BS MB CR (games we offer)
gt; gt;gt; I have an quot;Xquot; in the row under the colunm of what game(s) they deal. Most
gt; gt;gt; dealers deal more than one game.
gt; gt;gt; If you put an quot;Xquot; in the cell for what game they deal it copies their
gt; gt;gt; name from Col A to Col L - R with the same headings as Col C thru I. - So
gt; gt;gt; if they deal a game, x under what game they deal and it copies A1 to
gt; gt;gt; another set of columns. The columns L-R are for the dynamic ranges for
gt; gt;gt; each game. I did this due to high turnover rate of dealers (sucky job). I
gt; gt;gt; need to be able to add or subtract dealers from Col A. I did this because
gt; gt;gt; I have another sheet called quot;Mondayquot; actually 7 of them 1 for each day of
gt; gt;gt; the week. It looks like A3 quot;BJ9quot; B3 is a data validation cell and there
gt; gt;gt; are 17 of them (one for each game). I want 17 drop boxes on sheet
gt; gt;gt; quot;Mondayquot;, each with a list of only the dealers that deal that game, and
gt; gt;gt; once I use that person on a game, I don't want to be able to pick them
gt; gt;gt; for another game on that same sheet. Hence Debra's page.
gt; gt;gt;
gt; gt;gt; (Refer to Columns L-R, Those columns are dynamic named ranges.) Peo's
gt; gt;gt; formulas a
gt; gt;gt;
gt; gt;gt; array formula to make blank cells move to the bottom of the list
gt; gt;gt; IF(ROW(A1:A6)-ROW(A1) 1gt;COUNT(B1:B6),quot;quot;,INDEX(A:A,SMALL(B1:B6,RO W(INDIRECT(quot;1:quot;amp;ROWS(A1:A6))))))
gt; gt;gt; lt;not my cell rangesgt;
gt; gt;gt;
gt; gt;gt; and to make sure each name is used once,
gt; gt;gt; IF(COUNTIF(Schedule!$B$2:$B$7,A1)gt;=1,quot;quot;,ROW()) lt;not my rangesgt; I just
gt; gt;gt; can't make it work on my pages. - Can anyone point me in the right
gt; gt;gt; direction. I have the formulas from her site, I just can't make it work
gt; gt;gt; on my page.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; How can I fit this to my needs?
gt; gt;gt;
gt; gt;gt; Thanks in advance.
gt; gt;gt;
gt; gt;gt; Robb
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Debra Dalgleish
gt; gt; Contextures
gt; gt; www.contextures.com/tiptech.html
gt; gt;
gt;
gt;
gt;

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

quot;Debra Dalgleishquot; wrote:

gt; I just added a sample file that may help you set up the weekday sheets:
gt;
gt; www.contextures.com/excelfiles.html
gt;
gt; Under Data Validation, look for quot;Assign Qualified Employees to Single
gt; Task per Dayquot;
gt;
gt; Robb27 wrote:
gt; gt; I found something I would like for my workbook. I found it on MVP Debra
gt; gt; Dalgleish's page www.contextures.com/xlDataVal03.html. The original
gt; gt; code is from Peo Sojblom and it's about hiding previously used items in a
gt; gt; drop down box. I have my list of dealers on a sheet (GamesDealt):
gt; gt;
gt; gt; Col A is a list of Dealers (casino type).
gt; gt; col B is their hire date.
gt; gt; From Col C thru I has headings of: BJ PG WH TCP BS MB CR (games we offer)
gt; gt; I have an quot;Xquot; in the row under the colunm of what game(s) they deal. Most
gt; gt; dealers deal more than one game.
gt; gt; If you put an quot;Xquot; in the cell for what game they deal it copies their name
gt; gt; from Col A to Col L - R with the same headings as Col C thru I. - So if they
gt; gt; deal a game, x under what game they deal and it copies A1 to another set of
gt; gt; columns. The columns L-R are for the dynamic ranges for each game. I did this
gt; gt; due to high turnover rate of dealers (sucky job). I need to be able to add or
gt; gt; subtract dealers from Col A.
gt; gt; I did this because I have another sheet called quot;Mondayquot; actually 7 of them 1
gt; gt; for each day of the week. It looks like A3 quot;BJ9quot; B3 is a data validation cell
gt; gt; and there are 17 of them (one for each game).
gt; gt;
gt; gt; I want 17 drop boxes on sheet quot;Mondayquot;, each with a list of only the dealers
gt; gt; that deal that game, and once I use that person on a game, I don't want to be
gt; gt; able to pick them for another game on that same sheet. Hence Debra's page.
gt; gt;
gt; gt; (Refer to Columns L-R, Those columns are dynamic named ranges.)
gt; gt;
gt; gt; Peo's formulas a
gt; gt;
gt; gt; array formula to make blank cells move to the bottom of the list
gt; gt; IF(ROW(A1:A6)-ROW(A1) 1gt;COUNT(B1:B6),quot;quot;,INDEX(A:A,SMALL(B1:B6,RO W(INDIRECT(quot;1:quot;amp;ROWS(A1:A6)))))) lt;not my cell rangesgt;
gt; gt;
gt; gt; and to make sure each name is used once,
gt; gt; IF(COUNTIF(Schedule!$B$2:$B$7,A1)gt;=1,quot;quot;,ROW()) lt;not my rangesgt;
gt; gt; I just can't make it work on my pages. - Can anyone point me in the right
gt; gt; direction. I have the formulas from her site, I just can't make it work on my
gt; gt; page.
gt; gt;
gt; gt;
gt; gt; How can I fit this to my needs?
gt; gt;
gt; gt; Thanks in advance.
gt; gt;
gt; gt; Robb
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Contextures
gt; www.contextures.com/tiptech.html
gt;
gt;

It was a joke Robb, I can't even spell the damn thing myself lt;bggt;

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;Robb27quot; gt; wrote in message
...
gt; Sorry I misspelled your name Peo. Thanks for being here.
gt;
gt; Robb
gt;
gt; quot;Peo Sjobomquot; wrote:
gt;
gt;gt; Nice, now I don't have to do it. I felt for a moment it was my post to
gt;gt; answer but the misspelling of my name held me back lt;ggt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; Excel 95 - Excel 2007
gt;gt; Nothwest Excel Solutions
gt;gt; www.nwexcelsolutions.com
gt;gt; quot;It is a good thing to follow the first law of holes;
gt;gt; if you are in one stop digging.quot; Lord Healey
gt;gt;
gt;gt;
gt;gt; quot;Debra Dalgleishquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I just added a sample file that may help you set up the weekday sheets:
gt;gt; gt;
gt;gt; gt; www.contextures.com/excelfiles.html
gt;gt; gt;
gt;gt; gt; Under Data Validation, look for quot;Assign Qualified Employees to Single
gt;gt; gt; Task
gt;gt; gt; per Dayquot;
gt;gt; gt;
gt;gt; gt; Robb27 wrote:
gt;gt; gt;gt; I found something I would like for my workbook. I found it on MVP
gt;gt; gt;gt; Debra
gt;gt; gt;gt; Dalgleish's page www.contextures.com/xlDataVal03.html. The
gt;gt; gt;gt; original code is from Peo Sojblom and it's about hiding previously
gt;gt; gt;gt; used
gt;gt; gt;gt; items in a drop down box. I have my list of dealers on a sheet
gt;gt; gt;gt; (GamesDealt):
gt;gt; gt;gt;
gt;gt; gt;gt; Col A is a list of Dealers (casino type).
gt;gt; gt;gt; col B is their hire date.
gt;gt; gt;gt; From Col C thru I has headings of: BJ PG WH TCP BS MB CR (games we
gt;gt; gt;gt; offer)
gt;gt; gt;gt; I have an quot;Xquot; in the row under the colunm of what game(s) they deal.
gt;gt; gt;gt; Most
gt;gt; gt;gt; dealers deal more than one game.
gt;gt; gt;gt; If you put an quot;Xquot; in the cell for what game they deal it copies their
gt;gt; gt;gt; name from Col A to Col L - R with the same headings as Col C thru I. -
gt;gt; gt;gt; So
gt;gt; gt;gt; if they deal a game, x under what game they deal and it copies A1 to
gt;gt; gt;gt; another set of columns. The columns L-R are for the dynamic ranges for
gt;gt; gt;gt; each game. I did this due to high turnover rate of dealers (sucky
gt;gt; gt;gt; job). I
gt;gt; gt;gt; need to be able to add or subtract dealers from Col A. I did this
gt;gt; gt;gt; because
gt;gt; gt;gt; I have another sheet called quot;Mondayquot; actually 7 of them 1 for each day
gt;gt; gt;gt; of
gt;gt; gt;gt; the week. It looks like A3 quot;BJ9quot; B3 is a data validation cell and
gt;gt; gt;gt; there
gt;gt; gt;gt; are 17 of them (one for each game). I want 17 drop boxes on sheet
gt;gt; gt;gt; quot;Mondayquot;, each with a list of only the dealers that deal that game,
gt;gt; gt;gt; and
gt;gt; gt;gt; once I use that person on a game, I don't want to be able to pick them
gt;gt; gt;gt; for another game on that same sheet. Hence Debra's page.
gt;gt; gt;gt;
gt;gt; gt;gt; (Refer to Columns L-R, Those columns are dynamic named ranges.) Peo's
gt;gt; gt;gt; formulas a
gt;gt; gt;gt;
gt;gt; gt;gt; array formula to make blank cells move to the bottom of the list
gt;gt; gt;gt; IF(ROW(A1:A6)-ROW(A1) 1gt;COUNT(B1:B6),quot;quot;,INDEX(A:A,SMALL(B1:B6,RO W(INDIRECT(quot;1:quot;amp;ROWS(A1:A6))))))
gt;gt; gt;gt; lt;not my cell rangesgt;
gt;gt; gt;gt;
gt;gt; gt;gt; and to make sure each name is used once,
gt;gt; gt;gt; IF(COUNTIF(Schedule!$B$2:$B$7,A1)gt;=1,quot;quot;,ROW()) lt;not my rangesgt; I just
gt;gt; gt;gt; can't make it work on my pages. - Can anyone point me in the right
gt;gt; gt;gt; direction. I have the formulas from her site, I just can't make it
gt;gt; gt;gt; work
gt;gt; gt;gt; on my page.
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; How can I fit this to my needs?
gt;gt; gt;gt;
gt;gt; gt;gt; Thanks in advance.
gt;gt; gt;gt;
gt;gt; gt;gt; Robb
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; Debra Dalgleish
gt;gt; gt; Contextures
gt;gt; gt; www.contextures.com/tiptech.html
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
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!

Robb27 wrote:
gt; Thanks Debra, the new example on your page helped. Sometimes it takes a
gt; different look at things.
gt; I had to post an appology to Peo for butchering his name! lol
gt;
gt; Robb
gt;
gt; quot;Debra Dalgleishquot; wrote:
gt;
gt;
gt;gt;I just added a sample file that may help you set up the weekday sheets:
gt;gt;
gt;gt; www.contextures.com/excelfiles.html
gt;gt;
gt;gt;Under Data Validation, look for quot;Assign Qualified Employees to Single
gt;gt;Task per Dayquot;
gt;gt;
gt;gt;Robb27 wrote:
gt;gt;
gt;gt;gt;I found something I would like for my workbook. I found it on MVP Debra
gt;gt;gt;Dalgleish's page www.contextures.com/xlDataVal03.html. The original
gt;gt;gt;code is from Peo Sojblom and it's about hiding previously used items in a
gt;gt;gt;drop down box. I have my list of dealers on a sheet (GamesDealt):
gt;gt;gt;
gt;gt;gt;Col A is a list of Dealers (casino type).
gt;gt;gt;col B is their hire date.
gt;gt;gt;From Col C thru I has headings of: BJ PG WH TCP BS MB CR (games we offer)
gt;gt;gt;I have an quot;Xquot; in the row under the colunm of what game(s) they deal. Most
gt;gt;gt;dealers deal more than one game.
gt;gt;gt;If you put an quot;Xquot; in the cell for what game they deal it copies their name
gt;gt;gt;from Col A to Col L - R with the same headings as Col C thru I. - So if they
gt;gt;gt;deal a game, x under what game they deal and it copies A1 to another set of
gt;gt;gt;columns. The columns L-R are for the dynamic ranges for each game. I did this
gt;gt;gt;due to high turnover rate of dealers (sucky job). I need to be able to add or
gt;gt;gt;subtract dealers from Col A.
gt;gt;gt;I did this because I have another sheet called quot;Mondayquot; actually 7 of them 1
gt;gt;gt;for each day of the week. It looks like A3 quot;BJ9quot; B3 is a data validation cell
gt;gt;gt;and there are 17 of them (one for each game).
gt;gt;gt;
gt;gt;gt;I want 17 drop boxes on sheet quot;Mondayquot;, each with a list of only the dealers
gt;gt;gt;that deal that game, and once I use that person on a game, I don't want to be
gt;gt;gt;able to pick them for another game on that same sheet. Hence Debra's page.
gt;gt;gt;
gt;gt;gt;(Refer to Columns L-R, Those columns are dynamic named ranges.)
gt;gt;gt;
gt;gt;gt;Peo's formulas a
gt;gt;gt;
gt;gt;gt;array formula to make blank cells move to the bottom of the list
gt;gt;gt;IF(ROW(A1:A6)-ROW(A1) 1gt;COUNT(B1:B6),quot;quot;,INDEX(A:A,SMALL(B1:B6,RO W(INDIRECT(quot;1:quot;amp;ROWS(A1:A6)))))) lt;not my cell rangesgt;
gt;gt;gt;
gt;gt;gt;and to make sure each name is used once,
gt;gt;gt;IF(COUNTIF(Schedule!$B$2:$B$7,A1)gt;=1,quot;quot;,ROW() ) lt;not my rangesgt;
gt;gt;gt;I just can't make it work on my pages. - Can anyone point me in the right
gt;gt;gt;direction. I have the formulas from her site, I just can't make it work on my
gt;gt;gt;page.
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;How can I fit this to my needs?
gt;gt;gt;
gt;gt;gt;Thanks in advance.
gt;gt;gt;
gt;gt;gt;Robb

--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.htmlThe real amazing thing about that guy's name is the way he pronounces it:

Tom SmithDebra Dalgleish wrote:
gt;
gt; You're welcome, and thanks for letting me know that the sample file helped.
gt; I'm sure Poe Sojolbloom wasn't really upset about the misspelling, and
gt; thanks for getting my name right!
gt;
gt; Robb27 wrote:
gt; gt; Thanks Debra, the new example on your page helped. Sometimes it takes a
gt; gt; different look at things.
gt; gt; I had to post an appology to Peo for butchering his name! lol
gt; gt;
gt; gt; Robb
gt; gt;
gt; gt; quot;Debra Dalgleishquot; wrote:
gt; gt;
gt; gt;
gt; gt;gt;I just added a sample file that may help you set up the weekday sheets:
gt; gt;gt;
gt; gt;gt; www.contextures.com/excelfiles.html
gt; gt;gt;
gt; gt;gt;Under Data Validation, look for quot;Assign Qualified Employees to Single
gt; gt;gt;Task per Dayquot;
gt; gt;gt;
gt; gt;gt;Robb27 wrote:
gt; gt;gt;
gt; gt;gt;gt;I found something I would like for my workbook. I found it on MVP Debra
gt; gt;gt;gt;Dalgleish's page www.contextures.com/xlDataVal03.html. The original
gt; gt;gt;gt;code is from Peo Sojblom and it's about hiding previously used items in a
gt; gt;gt;gt;drop down box. I have my list of dealers on a sheet (GamesDealt):
gt; gt;gt;gt;
gt; gt;gt;gt;Col A is a list of Dealers (casino type).
gt; gt;gt;gt;col B is their hire date.
gt; gt;gt;gt;From Col C thru I has headings of: BJ PG WH TCP BS MB CR (games we offer)
gt; gt;gt;gt;I have an quot;Xquot; in the row under the colunm of what game(s) they deal. Most
gt; gt;gt;gt;dealers deal more than one game.
gt; gt;gt;gt;If you put an quot;Xquot; in the cell for what game they deal it copies their name
gt; gt;gt;gt;from Col A to Col L - R with the same headings as Col C thru I. - So if they
gt; gt;gt;gt;deal a game, x under what game they deal and it copies A1 to another set of
gt; gt;gt;gt;columns. The columns L-R are for the dynamic ranges for each game. I did this
gt; gt;gt;gt;due to high turnover rate of dealers (sucky job). I need to be able to add or
gt; gt;gt;gt;subtract dealers from Col A.
gt; gt;gt;gt;I did this because I have another sheet called quot;Mondayquot; actually 7 of them 1
gt; gt;gt;gt;for each day of the week. It looks like A3 quot;BJ9quot; B3 is a data validation cell
gt; gt;gt;gt;and there are 17 of them (one for each game).
gt; gt;gt;gt;
gt; gt;gt;gt;I want 17 drop boxes on sheet quot;Mondayquot;, each with a list of only the dealers
gt; gt;gt;gt;that deal that game, and once I use that person on a game, I don't want to be
gt; gt;gt;gt;able to pick them for another game on that same sheet. Hence Debra's page.
gt; gt;gt;gt;
gt; gt;gt;gt;(Refer to Columns L-R, Those columns are dynamic named ranges.)
gt; gt;gt;gt;
gt; gt;gt;gt;Peo's formulas a
gt; gt;gt;gt;
gt; gt;gt;gt;array formula to make blank cells move to the bottom of the list
gt; gt;gt;gt;IF(ROW(A1:A6)-ROW(A1) 1gt;COUNT(B1:B6),quot;quot;,INDEX(A:A,SMALL(B1:B6,RO W(INDIRECT(quot;1:quot;amp;ROWS(A1:A6)))))) lt;not my cell rangesgt;
gt; gt;gt;gt;
gt; gt;gt;gt;and to make sure each name is used once,
gt; gt;gt;gt;IF(COUNTIF(Schedule!$B$2:$B$7,A1)gt;=1,quot;quot;,ROW() ) lt;not my rangesgt;
gt; gt;gt;gt;I just can't make it work on my pages. - Can anyone point me in the right
gt; gt;gt;gt;direction. I have the formulas from her site, I just can't make it work on my
gt; gt;gt;gt;page.
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;How can I fit this to my needs?
gt; gt;gt;gt;
gt; gt;gt;gt;Thanks in advance.
gt; gt;gt;gt;
gt; gt;gt;gt;Robb
gt;
gt; --
gt; Debra Dalgleish
gt; Contextures
gt; www.contextures.com/tiptech.html

--

Dave Peterson