=NameCheck - Monday???

[Lost Cluster]

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

 

[T. Valko]

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,Monday,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.

 

[Lost Cluster]

Thank you, it works!!!

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,Monday,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





.

 

[T. Valko]

You're welcome. Thanks for the feedback!