How do you write a formula for this:

=NameCheck - Monday

Im 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

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$2:D2))-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

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$2:D2))-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



.

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$2:D2))-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



.