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
|
|||
|
|||
=NameCheck - Monday???
How do you write a formula for this:
=NameCheck - Monday I’m looking for the correct syntax for writing a formula that deletes or omits names from a list? For example I have a list with the names of all my employees called NameCheck and another list called Monday. I want the names in the Monday list to NOT show up on my data validation source that currently is =NameCheck. Thanks |
#2
|
|||
|
|||
=NameCheck - Monday???
You'd have to create a new list and use that as the source for your drop
down. One way... Let's assume NameCheck is in column A and Monday is in column B. Let's create the new list starting in cell D2. So, enter this array formula** in D2 and copy down until you get #NUM! errors: =INDEX(NameCheck,SMALL(IF(ISNA(MATCH(NameCheck,Mon day,0)),ROW(NameCheck)),ROWS(D$22))-MIN(ROW(NameCheck))+1) ** 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. Then, as the source for the drop down use: =$D$2:INDEX($D$2:$D$100,COUNTIF($D$2:$D$100,"*")) Adjust for a reasonable end of range D100 Or, give that formula a defined name like NewList and use =NewList as the source. -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... How do you write a formula for this: =NameCheck - Monday I'm looking for the correct syntax for writing a formula that deletes or omits names from a list? For example I have a list with the names of all my employees called NameCheck and another list called Monday. I want the names in the Monday list to NOT show up on my data validation source that currently is =NameCheck. Thanks |
#3
|
|||
|
|||
=NameCheck - Monday???
Thank you, it works!!!
"T. Valko" wrote: You'd have to create a new list and use that as the source for your drop down. One way... Let's assume NameCheck is in column A and Monday is in column B. Let's create the new list starting in cell D2. So, enter this array formula** in D2 and copy down until you get #NUM! errors: =INDEX(NameCheck,SMALL(IF(ISNA(MATCH(NameCheck,Mon day,0)),ROW(NameCheck)),ROWS(D$22))-MIN(ROW(NameCheck))+1) ** 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. Then, as the source for the drop down use: =$D$2:INDEX($D$2:$D$100,COUNTIF($D$2:$D$100,"*")) Adjust for a reasonable end of range D100 Or, give that formula a defined name like NewList and use =NewList as the source. -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... How do you write a formula for this: =NameCheck - Monday I'm looking for the correct syntax for writing a formula that deletes or omits names from a list? For example I have a list with the names of all my employees called NameCheck and another list called Monday. I want the names in the Monday list to NOT show up on my data validation source that currently is =NameCheck. Thanks . |
#4
|
|||
|
|||
=NameCheck - Monday???
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... Thank you, it works!!! "T. Valko" wrote: You'd have to create a new list and use that as the source for your drop down. One way... Let's assume NameCheck is in column A and Monday is in column B. Let's create the new list starting in cell D2. So, enter this array formula** in D2 and copy down until you get #NUM! errors: =INDEX(NameCheck,SMALL(IF(ISNA(MATCH(NameCheck,Mon day,0)),ROW(NameCheck)),ROWS(D$22))-MIN(ROW(NameCheck))+1) ** 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. Then, as the source for the drop down use: =$D$2:INDEX($D$2:$D$100,COUNTIF($D$2:$D$100,"*")) Adjust for a reasonable end of range D100 Or, give that formula a defined name like NewList and use =NewList as the source. -- Biff Microsoft Excel MVP "Lost Cluster" wrote in message ... How do you write a formula for this: =NameCheck - Monday I'm looking for the correct syntax for writing a formula that deletes or omits names from a list? For example I have a list with the names of all my employees called NameCheck and another list called Monday. I want the names in the Monday list to NOT show up on my data validation source that currently is =NameCheck. Thanks . |
Thread Tools | |
Display Modes | |
|
|