If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filtering or Formatting DataValidation List
In ColumnG I have the following formula array from row2 all the way down to
row100 =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fil teredMonday)))))) ColE = FilteredMonday (a list of my employees who work on Mondays) ColF = counter In ColumnM I have a drop down list with data validation and the source for it is ColumnG. The problem is I only have 43 names but I wanted it to have the capacity for 100 names, so rows 44-100 are either a 0 or #NUM!. How can I have the drop down list show only the names without cutting short my list in case I do decide to add more names later on? Thank you. |
#2
|
|||
|
|||
Filtering or Formatting DataValidation List
=IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"",
INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fi lteredMonday)))))) ColF = counter rows 44-100 are either a 0 or #NUM!. Isn't that why you have this portion of the formula: =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", What is the col F counter for? -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... In ColumnG I have the following formula array from row2 all the way down to row100 =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fil teredMonday)))))) ColE = FilteredMonday (a list of my employees who work on Mondays) ColF = counter In ColumnM I have a drop down list with data validation and the source for it is ColumnG. The problem is I only have 43 names but I wanted it to have the capacity for 100 names, so rows 44-100 are either a 0 or #NUM!. How can I have the drop down list show only the names without cutting short my list in case I do decide to add more names later on? Thank you. |
#3
|
|||
|
|||
Filtering or Formatting DataValidation List
ColA – LNames (Original list, all my employees)
ColE – Displays only the names of people who work on Monday. Array with formula =INDEX(LNames,SMALL(IF(ISNA(MATCH(LNames, Monday,0)), ROW(LNames)),ROWS(E$2:E2))-MIN(ROW(LNames))+1) that you so kindly helped me with I use the counter for the formula in ColG so when I select a name in my datavalidation drop down list the names disappear as I go down the list. Pete_UK referred me to a website about Excel Data Validation -- Hide Previously Used Items in Dropdown and that is where I got the formula for ColG. My goal is to create a dropdown list that has only the names of the people who work on that day and have those names disappear from the list as you select them so only 1 employee per assignment. As I'm approaching my goal, I now have this issue of extra data in my list because I want it to be expandable. If you have a better plan than what I did, any ideas are appreciated. Thanks. "T. Valko" wrote: =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fi lteredMonday)))))) ColF = counter rows 44-100 are either a 0 or #NUM!. Isn't that why you have this portion of the formula: =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", What is the col F counter for? -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... In ColumnG I have the following formula array from row2 all the way down to row100 =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fil teredMonday)))))) ColE = FilteredMonday (a list of my employees who work on Mondays) ColF = counter In ColumnM I have a drop down list with data validation and the source for it is ColumnG. The problem is I only have 43 names but I wanted it to have the capacity for 100 names, so rows 44-100 are either a 0 or #NUM!. How can I have the drop down list show only the names without cutting short my list in case I do decide to add more names later on? Thank you. . |
#4
|
|||
|
|||
Filtering or Formatting DataValidation List
Ok, let's assume...
A2:A21 = all names B2:B21 = M is entered to identify those employees that work Monday C2:Cn = list of employees that work Monday E2:E15 = drop down lists of those employees that work Monday As you make selections from the drop downs those employees will be removed from the list. Extract the names of those employees that work on Monday. This list will also be the source for the series of drop down lists in E2:E15. Array entered** in C2 and copied down to C21: =INDEX(A:A,SMALL(IF(B$2:B$21="m",IF(ISNA(MATCH(A$2 :A$21,E$2:E$15,0)),ROW(B$2:B$21))),ROWS(C$2:C2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Setup the drop down lists... Select the range E2:E15 As the source of the lists use: =IF(AND(ISERROR(C$2:C$21)),NA(),C$2:INDEX(C$2:C$21 ,COUNTIF(C$2:C$21,"*"))) I can post a sample file if you'd like. -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... ColA - LNames (Original list, all my employees) ColE - Displays only the names of people who work on Monday. Array with formula =INDEX(LNames,SMALL(IF(ISNA(MATCH(LNames, Monday,0)), ROW(LNames)),ROWS(E$2:E2))-MIN(ROW(LNames))+1) that you so kindly helped me with I use the counter for the formula in ColG so when I select a name in my datavalidation drop down list the names disappear as I go down the list. Pete_UK referred me to a website about Excel Data Validation -- Hide Previously Used Items in Dropdown and that is where I got the formula for ColG. My goal is to create a dropdown list that has only the names of the people who work on that day and have those names disappear from the list as you select them so only 1 employee per assignment. As I'm approaching my goal, I now have this issue of extra data in my list because I want it to be expandable. If you have a better plan than what I did, any ideas are appreciated. Thanks. "T. Valko" wrote: =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fi lteredMonday)))))) ColF = counter rows 44-100 are either a 0 or #NUM!. Isn't that why you have this portion of the formula: =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", What is the col F counter for? -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... In ColumnG I have the following formula array from row2 all the way down to row100 =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fil teredMonday)))))) ColE = FilteredMonday (a list of my employees who work on Mondays) ColF = counter In ColumnM I have a drop down list with data validation and the source for it is ColumnG. The problem is I only have 43 names but I wanted it to have the capacity for 100 names, so rows 44-100 are either a 0 or #NUM!. How can I have the drop down list show only the names without cutting short my list in case I do decide to add more names later on? Thank you. . |
#5
|
|||
|
|||
Filtering or Formatting DataValidation List
Hello Valko,
The formula for the source in Col E for Data Validation does not seem to filter the Monday only employees. At the moment it lists the first 4 people from my C Column but then as you select them it starts showing the other people who work on Monday one at a time and it also list the people who don’t work on Monday so it lists everyone but not at the same time. It also displays the #NUM! error, which was my original problem. I tried increasing the range to 100 instead of 21 but there was no change. I also modified the formula for Col C because I have my days off listed in a string of text. So at the moment Col C is showing the people who are off on Monday. Maybe we would modify the formula for C to not show the names that have an M in Col B. I tried the following syntax for col C but didn’t work I got a Value error =INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("m",B2))=TRUE, " ",IF(ISNA(MATCH(A$2:A$21,E$2:E$21,0)),ROW(B$2:B$21 ))),ROWS(C$2:C2))) Here is what my formulas look like after I modified them a bit. Col A all my employees Alejandro Aros (ICE) Avila Baker Balicki Bay (ICE) Beghin Boltz Bowman (ICE) Bratcher (ICE) Brito (ICE) Col B (Days off) m Sa/Su/M-W F/Sa/Su-Tu M/Tu/W-F Tu/W/Th-Sa F/Sa/Su-Tu Su/M/Tu-Th Tu/W/Th-Sa Th/F/Sa-M Su/M/Tu-Th M/Tu/W-F Col C – =INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("m",B2)),IF(IS NA(MATCH(A$2:A$21,E$2:E$21,0)),ROW(B$2:B$21))),ROW S(C$2:C2))) Filtered employess who have Monday off Alejandro Aros (ICE) #NUM! Baker #NUM! #NUM! Beghin #NUM! Bowman (ICE) Bratcher (ICE) Brito (ICE) #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! Col E =IF(AND(ISERROR(C$2:C$100)),NA(),C$2:INDEX(C$2:C$1 00,COUNTIF(C$2:C$100,"*"))) Under my drop down this is all it shows for the first cell E2 it doesn’t even give me the scroll down bar. After I select 2 names then it starts listing other employees who don’t belong in that list like “Balicki” refer to the list in Col C Cell E2 Alejandro Aros (ICE) #NUM! Baker #NUM! #NUM! Beghin Cell E4 Alejandro Aros (ICE) #NUM! Balicki #NUM! "T. Valko" wrote: Ok, let's assume... A2:A21 = all names B2:B21 = M is entered to identify those employees that work Monday C2:Cn = list of employees that work Monday E2:E15 = drop down lists of those employees that work Monday As you make selections from the drop downs those employees will be removed from the list. Extract the names of those employees that work on Monday. This list will also be the source for the series of drop down lists in E2:E15. Array entered** in C2 and copied down to C21: =INDEX(A:A,SMALL(IF(B$2:B$21="m",IF(ISNA(MATCH(A$2 :A$21,E$2:E$15,0)),ROW(B$2:B$21))),ROWS(C$2:C2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Setup the drop down lists... Select the range E2:E15 As the source of the lists use: =IF(AND(ISERROR(C$2:C$21)),NA(),C$2:INDEX(C$2:C$21 ,COUNTIF(C$2:C$21,"*"))) I can post a sample file if you'd like. -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... ColA - LNames (Original list, all my employees) ColE - Displays only the names of people who work on Monday. Array with formula =INDEX(LNames,SMALL(IF(ISNA(MATCH(LNames, Monday,0)), ROW(LNames)),ROWS(E$2:E2))-MIN(ROW(LNames))+1) that you so kindly helped me with I use the counter for the formula in ColG so when I select a name in my datavalidation drop down list the names disappear as I go down the list. Pete_UK referred me to a website about Excel Data Validation -- Hide Previously Used Items in Dropdown and that is where I got the formula for ColG. My goal is to create a dropdown list that has only the names of the people who work on that day and have those names disappear from the list as you select them so only 1 employee per assignment. As I'm approaching my goal, I now have this issue of extra data in my list because I want it to be expandable. If you have a better plan than what I did, any ideas are appreciated. Thanks. "T. Valko" wrote: =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fi lteredMonday)))))) ColF = counter rows 44-100 are either a 0 or #NUM!. Isn't that why you have this portion of the formula: =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", What is the col F counter for? -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... In ColumnG I have the following formula array from row2 all the way down to row100 =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fil teredMonday)))))) ColE = FilteredMonday (a list of my employees who work on Mondays) ColF = counter In ColumnM I have a drop down list with data validation and the source for it is ColumnG. The problem is I only have 43 names but I wanted it to have the capacity for 100 names, so rows 44-100 are either a 0 or #NUM!. How can I have the drop down list show only the names without cutting short my list in case I do decide to add more names later on? Thank you. . . |
#6
|
|||
|
|||
Filtering or Formatting DataValidation List
Sa/Su/M-W
F/Sa/Su-Tu M/Tu/W-F Tu/W/Th-Sa F/Sa/Su-Tu Su/M/Tu-Th Tu/W/Th-Sa Th/F/Sa-M Su/M/Tu-Th M/Tu/W-F I'm not sure how to read that. But, if you're using an ISNUMBER(SEARCH for "M" then these are the entries that should be matched: Sa/Su/M-W = matched F/Sa/Su-Tu M/Tu/W-F = matched Tu/W/Th-Sa F/Sa/Su-Tu Su/M/Tu-Th = matched Tu/W/Th-Sa Th/F/Sa-M = matched Su/M/Tu-Th = matched M/Tu/W-F = matched Do you want me to post a sample file? -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... Hello Valko, The formula for the source in Col E for Data Validation does not seem to filter the Monday only employees. At the moment it lists the first 4 people from my C Column but then as you select them it starts showing the other people who work on Monday one at a time and it also list the people who don't work on Monday so it lists everyone but not at the same time. It also displays the #NUM! error, which was my original problem. I tried increasing the range to 100 instead of 21 but there was no change. I also modified the formula for Col C because I have my days off listed in a string of text. So at the moment Col C is showing the people who are off on Monday. Maybe we would modify the formula for C to not show the names that have an M in Col B. I tried the following syntax for col C but didn't work I got a Value error =INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("m",B2))=TRUE, " ",IF(ISNA(MATCH(A$2:A$21,E$2:E$21,0)),ROW(B$2:B$21 ))),ROWS(C$2:C2))) Here is what my formulas look like after I modified them a bit. Col A all my employees Alejandro Aros (ICE) Avila Baker Balicki Bay (ICE) Beghin Boltz Bowman (ICE) Bratcher (ICE) Brito (ICE) Col B (Days off) m Sa/Su/M-W F/Sa/Su-Tu M/Tu/W-F Tu/W/Th-Sa F/Sa/Su-Tu Su/M/Tu-Th Tu/W/Th-Sa Th/F/Sa-M Su/M/Tu-Th M/Tu/W-F Col C - =INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("m",B2)),IF(IS NA(MATCH(A$2:A$21,E$2:E$21,0)),ROW(B$2:B$21))),ROW S(C$2:C2))) Filtered employess who have Monday off Alejandro Aros (ICE) #NUM! Baker #NUM! #NUM! Beghin #NUM! Bowman (ICE) Bratcher (ICE) Brito (ICE) #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! Col E =IF(AND(ISERROR(C$2:C$100)),NA(),C$2:INDEX(C$2:C$1 00,COUNTIF(C$2:C$100,"*"))) Under my drop down this is all it shows for the first cell E2 it doesn't even give me the scroll down bar. After I select 2 names then it starts listing other employees who don't belong in that list like "Balicki" refer to the list in Col C Cell E2 Alejandro Aros (ICE) #NUM! Baker #NUM! #NUM! Beghin Cell E4 Alejandro Aros (ICE) #NUM! Balicki #NUM! "T. Valko" wrote: Ok, let's assume... A2:A21 = all names B2:B21 = M is entered to identify those employees that work Monday C2:Cn = list of employees that work Monday E2:E15 = drop down lists of those employees that work Monday As you make selections from the drop downs those employees will be removed from the list. Extract the names of those employees that work on Monday. This list will also be the source for the series of drop down lists in E2:E15. Array entered** in C2 and copied down to C21: =INDEX(A:A,SMALL(IF(B$2:B$21="m",IF(ISNA(MATCH(A$2 :A$21,E$2:E$15,0)),ROW(B$2:B$21))),ROWS(C$2:C2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Setup the drop down lists... Select the range E2:E15 As the source of the lists use: =IF(AND(ISERROR(C$2:C$21)),NA(),C$2:INDEX(C$2:C$21 ,COUNTIF(C$2:C$21,"*"))) I can post a sample file if you'd like. -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... ColA - LNames (Original list, all my employees) ColE - Displays only the names of people who work on Monday. Array with formula =INDEX(LNames,SMALL(IF(ISNA(MATCH(LNames, Monday,0)), ROW(LNames)),ROWS(E$2:E2))-MIN(ROW(LNames))+1) that you so kindly helped me with I use the counter for the formula in ColG so when I select a name in my datavalidation drop down list the names disappear as I go down the list. Pete_UK referred me to a website about Excel Data Validation -- Hide Previously Used Items in Dropdown and that is where I got the formula for ColG. My goal is to create a dropdown list that has only the names of the people who work on that day and have those names disappear from the list as you select them so only 1 employee per assignment. As I'm approaching my goal, I now have this issue of extra data in my list because I want it to be expandable. If you have a better plan than what I did, any ideas are appreciated. Thanks. "T. Valko" wrote: =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fi lteredMonday)))))) ColF = counter rows 44-100 are either a 0 or #NUM!. Isn't that why you have this portion of the formula: =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", What is the col F counter for? -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... In ColumnG I have the following formula array from row2 all the way down to row100 =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fil teredMonday)))))) ColE = FilteredMonday (a list of my employees who work on Mondays) ColF = counter In ColumnM I have a drop down list with data validation and the source for it is ColumnG. The problem is I only have 43 names but I wanted it to have the capacity for 100 names, so rows 44-100 are either a 0 or #NUM!. How can I have the drop down list show only the names without cutting short my list in case I do decide to add more names later on? Thank you. . . |
#7
|
|||
|
|||
Filtering or Formatting DataValidation List
would you please post that sample file. i havent been able to get it to work
for me. thanks. "T. Valko" wrote: Sa/Su/M-W F/Sa/Su-Tu M/Tu/W-F Tu/W/Th-Sa F/Sa/Su-Tu Su/M/Tu-Th Tu/W/Th-Sa Th/F/Sa-M Su/M/Tu-Th M/Tu/W-F I'm not sure how to read that. But, if you're using an ISNUMBER(SEARCH for "M" then these are the entries that should be matched: Sa/Su/M-W = matched F/Sa/Su-Tu M/Tu/W-F = matched Tu/W/Th-Sa F/Sa/Su-Tu Su/M/Tu-Th = matched Tu/W/Th-Sa Th/F/Sa-M = matched Su/M/Tu-Th = matched M/Tu/W-F = matched Do you want me to post a sample file? -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... Hello Valko, The formula for the source in Col E for Data Validation does not seem to filter the Monday only employees. At the moment it lists the first 4 people from my C Column but then as you select them it starts showing the other people who work on Monday one at a time and it also list the people who don't work on Monday so it lists everyone but not at the same time. It also displays the #NUM! error, which was my original problem. I tried increasing the range to 100 instead of 21 but there was no change. I also modified the formula for Col C because I have my days off listed in a string of text. So at the moment Col C is showing the people who are off on Monday. Maybe we would modify the formula for C to not show the names that have an M in Col B. I tried the following syntax for col C but didn't work I got a Value error =INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("m",B2))=TRUE, " ",IF(ISNA(MATCH(A$2:A$21,E$2:E$21,0)),ROW(B$2:B$21 ))),ROWS(C$2:C2))) Here is what my formulas look like after I modified them a bit. Col A all my employees Alejandro Aros (ICE) Avila Baker Balicki Bay (ICE) Beghin Boltz Bowman (ICE) Bratcher (ICE) Brito (ICE) Col B (Days off) m Sa/Su/M-W F/Sa/Su-Tu M/Tu/W-F Tu/W/Th-Sa F/Sa/Su-Tu Su/M/Tu-Th Tu/W/Th-Sa Th/F/Sa-M Su/M/Tu-Th M/Tu/W-F Col C - =INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("m",B2)),IF(IS NA(MATCH(A$2:A$21,E$2:E$21,0)),ROW(B$2:B$21))),ROW S(C$2:C2))) Filtered employess who have Monday off Alejandro Aros (ICE) #NUM! Baker #NUM! #NUM! Beghin #NUM! Bowman (ICE) Bratcher (ICE) Brito (ICE) #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! Col E =IF(AND(ISERROR(C$2:C$100)),NA(),C$2:INDEX(C$2:C$1 00,COUNTIF(C$2:C$100,"*"))) Under my drop down this is all it shows for the first cell E2 it doesn't even give me the scroll down bar. After I select 2 names then it starts listing other employees who don't belong in that list like "Balicki" refer to the list in Col C Cell E2 Alejandro Aros (ICE) #NUM! Baker #NUM! #NUM! Beghin Cell E4 Alejandro Aros (ICE) #NUM! Balicki #NUM! "T. Valko" wrote: Ok, let's assume... A2:A21 = all names B2:B21 = M is entered to identify those employees that work Monday C2:Cn = list of employees that work Monday E2:E15 = drop down lists of those employees that work Monday As you make selections from the drop downs those employees will be removed from the list. Extract the names of those employees that work on Monday. This list will also be the source for the series of drop down lists in E2:E15. Array entered** in C2 and copied down to C21: =INDEX(A:A,SMALL(IF(B$2:B$21="m",IF(ISNA(MATCH(A$2 :A$21,E$2:E$15,0)),ROW(B$2:B$21))),ROWS(C$2:C2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Setup the drop down lists... Select the range E2:E15 As the source of the lists use: =IF(AND(ISERROR(C$2:C$21)),NA(),C$2:INDEX(C$2:C$21 ,COUNTIF(C$2:C$21,"*"))) I can post a sample file if you'd like. -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... ColA - LNames (Original list, all my employees) ColE - Displays only the names of people who work on Monday. Array with formula =INDEX(LNames,SMALL(IF(ISNA(MATCH(LNames, Monday,0)), ROW(LNames)),ROWS(E$2:E2))-MIN(ROW(LNames))+1) that you so kindly helped me with I use the counter for the formula in ColG so when I select a name in my datavalidation drop down list the names disappear as I go down the list. Pete_UK referred me to a website about Excel Data Validation -- Hide Previously Used Items in Dropdown and that is where I got the formula for ColG. My goal is to create a dropdown list that has only the names of the people who work on that day and have those names disappear from the list as you select them so only 1 employee per assignment. As I'm approaching my goal, I now have this issue of extra data in my list because I want it to be expandable. If you have a better plan than what I did, any ideas are appreciated. Thanks. "T. Valko" wrote: =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fi lteredMonday)))))) ColF = counter rows 44-100 are either a 0 or #NUM!. Isn't that why you have this portion of the formula: =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", What is the col F counter for? -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... In ColumnG I have the following formula array from row2 all the way down to row100 =IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"", INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fil teredMonday)))))) ColE = FilteredMonday (a list of my employees who work on Mondays) ColF = counter In ColumnM I have a drop down list with data validation and the source for it is ColumnG. The problem is I only have 43 names but I wanted it to have the capacity for 100 names, so rows 44-100 are either a 0 or #NUM!. How can I have the drop down list show only the names without cutting short my list in case I do decide to add more names later on? Thank you. . . . |
Thread Tools | |
Display Modes | |
|
|