Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
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 a array formula to make blank cells move to the bottom of the list IF(ROW(A1:A6)-ROW(A1)+1COUNT(B1:B6),"",INDEX(A:A,SMALL(B1:B6,RO W(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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
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" Robb27 wrote: 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 a array formula to make blank cells move to the bottom of the list IF(ROW(A1:A6)-ROW(A1)+1COUNT(B1:B6),"",INDEX(A:A,SMALL(B1:B6,RO W(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 Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
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" wrote in message ... 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" Robb27 wrote: 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 a array formula to make blank cells move to the bottom of the list IF(ROW(A1:A6)-ROW(A1)+1COUNT(B1:B6),"",INDEX(A:A,SMALL(B1:B6,RO W(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 Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
Well, it's pretty hard to spell -- you seem to be having a bit of
difficulty with it yourself. <g Peo Sjobom wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
LOL! That is why I never help myself
-- Regards, Peo Sjöblom "Debra Dalgleish" wrote in message ... Well, it's pretty hard to spell -- you seem to be having a bit of difficulty with it yourself. <g Peo Sjobom wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
Sorry I misspelled your name Peo. Thanks for being here.
Robb "Peo Sjobom" wrote: 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" wrote in message ... 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" Robb27 wrote: 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 a array formula to make blank cells move to the bottom of the list IF(ROW(A1:A6)-ROW(A1)+1COUNT(B1:B6),"",INDEX(A:A,SMALL(B1:B6,RO W(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 Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
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 "Debra Dalgleish" wrote: 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" Robb27 wrote: 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 a array formula to make blank cells move to the bottom of the list IF(ROW(A1:A6)-ROW(A1)+1COUNT(B1:B6),"",INDEX(A:A,SMALL(B1:B6,RO W(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 Contextures http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
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 "Robb27" wrote in message ... Sorry I misspelled your name Peo. Thanks for being here. Robb "Peo Sjobom" wrote: 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" wrote in message ... 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" Robb27 wrote: 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 a array formula to make blank cells move to the bottom of the list IF(ROW(A1:A6)-ROW(A1)+1COUNT(B1:B6),"",INDEX(A:A,SMALL(B1:B6,RO W(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 Contextures http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
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: 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 "Debra Dalgleish" wrote: 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" Robb27 wrote: 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 a array formula to make blank cells move to the bottom of the list IF(ROW(A1:A6)-ROW(A1)+1COUNT(B1:B6),"",INDEX(A:A,SMALL(B1:B6,RO W(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 Contextures http://www.contextures.com/tiptech.html |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
The real amazing thing about that guy's name is the way he pronounces it:
Tom Smith Debra Dalgleish wrote: 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: 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 "Debra Dalgleish" wrote: 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" Robb27 wrote: 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 a array formula to make blank cells move to the bottom of the list IF(ROW(A1:A6)-ROW(A1)+1COUNT(B1:B6),"",INDEX(A:A,SMALL(B1:B6,RO W(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 Contextures http://www.contextures.com/tiptech.html -- Dave Peterson |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
lol Dave. Nothing like it's spelled huh?
"Dave Peterson" wrote: The real amazing thing about that guy's name is the way he pronounces it: Tom Smith Debra Dalgleish wrote: 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: 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 "Debra Dalgleish" wrote: 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" Robb27 wrote: 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 a array formula to make blank cells move to the bottom of the list IF(ROW(A1:A6)-ROW(A1)+1COUNT(B1:B6),"",INDEX(A:A,SMALL(B1:B6,RO W(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 Contextures http://www.contextures.com/tiptech.html -- Dave Peterson |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
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 cant 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)+1COUNT(T2:T8),"",INDEX($A :$A,SMALL(T2:T8,ROW(INDIRECT("1:"&ROWS(A2:A8)))))) } to this: IF(ROW($A9:$A15)ROW(A9)+1COUNT(T9:T15),"",INDEX($ A:$A,SMALL(T9:T15,ROW(INDIRECT("1:"&ROWS(A9:A15))) ))) €“ (this changes every 8 rows) If Im 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. Its happening when I dont put some xs in some of the games the dealers deal. When I change the xs in your example set of 7, I dont have any problem. - I take away xs 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 dont have any missing xs 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 "Debra Dalgleish" wrote: 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: 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 "Debra Dalgleish" wrote: 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" Robb27 wrote: 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 a array formula to make blank cells move to the bottom of the list IF(ROW(A1:A6)-ROW(A1)+1COUNT(B1:B6),"",INDEX(A:A,SMALL(B1:B6,RO W(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 Contextures http://www.contextures.com/tiptech.html |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
Robb,
"Robb27" wrote in message ... 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)+1COUNT(T2:T8),"",INDEX($A :$A,SMALL(T2:T8,ROW(INDIRECT("1:"&ROWS(A2:A8)))))) } to this: IF(ROW($A9:$A15)ROW(A9)+1COUNT(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)+1COUNT(T2:T57),"",INDEX($A:$A,SMALL(T2:T5 7,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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide used names in drop downbox.
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. "Peo Sjoblom" wrote: Robb, "Robb27" wrote in message ... 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)+1COUNT(T2:T8),"",INDEX($A :$A,SMALL(T2:T8,ROW(INDIRECT("1:"&ROWS(A2:A8)))))) } to this: IF(ROW($A9:$A15)ROW(A9)+1COUNT(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)+1COUNT(T2:T57),"",INDEX($A:$A,SMALL(T2:T5 7,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limit drop down list and linking to other info | Excel Worksheet Functions | |||
drop down box leading to another drop down box | Excel Discussion (Misc queries) | |||
how do i make a drop down list of selected names no repeatition | Excel Worksheet Functions | |||
advanced: synchronizing data value across two worksheet drop boxes | Excel Worksheet Functions | |||
copying cell names | Excel Discussion (Misc queries) |