Hi,
This may be obvious, but not to me...
I want to prevent a few specific words from being entered in a column, but
except for these exclusions, anything goes. I have only been able to figure
out how I can ALLOW certain entries in a list, but this is kind of the
reverse situation. Any ideas?

Select your cell (assume A1 for this example)
<Data<Validation
In the "Allow:" drop down box choose "Custom"
Enter the following formula. Change text1 and text2 to the values you don't
want to allow, or you can add as many other conditions as you wish.

=NOT(OR(A1="text1",A1="text2"))

--
Regards,
Dave


"a-one-and-a-two" wrote:

Hi,
This may be obvious, but not to me...
I want to prevent a few specific words from being entered in a column, but
except for these exclusions, anything goes. I have only been able to figure
out how I can ALLOW certain entries in a list, but this is kind of the
reverse situation. Any ideas?

David Billigmeier wrote...
....
=NOT(OR(A1="text1",A1="text2"))
....

Shorter and more easily extensible as

=AND(A1<{"text1";"text2";...})

=AND(A1<{"text1";"text2";...})

DV won't accept array constants.

List the words in a range. Name the range List.

=AND(A1<List)

Biff

"Harlan Grove" wrote in message
ups.com...
David Billigmeier wrote...
...
=NOT(OR(A1="text1",A1="text2"))
...

Shorter and more easily extensible as

=AND(A1<{"text1";"text2";...})

Having a little trouble here...Am I using incorrect syntax? I am validating
column K in my worksheet, so I highlight the entire column, then for my
formula I am typing =AND(K:K<{"Brown";"Duffy"}), where Brown & Duffy are two
of the words I don't want to allow as entries.
I keep getting error that says "you may not use unions, intersections, or
arrray constants for data validation criteria" (!!!!!)
thanks.

"Harlan Grove" wrote:

David Billigmeier wrote...
....
=NOT(OR(A1="text1",A1="text2"))
....

Shorter and more easily extensible as

=AND(A1<{"text1";"text2";...})

Yes, As T. Valko said you CANNOT use array functions in data validation, so
Harlan's post will not work. My original post will work, I'll update to
correspond to your range and values:

Highlight the entire column K and enter either of the following (make sure
K1 is the active cell):
=NOT(OR(K1="Brown",K1="Duffy"))
=AND(K1<"Brown",K1<"Duffy")

--
Regards,
Dave


"a-one-and-a-two" wrote:

Having a little trouble here...Am I using incorrect syntax? I am validating
column K in my worksheet, so I highlight the entire column, then for my
formula I am typing =AND(K:K<{"Brown";"Duffy"}), where Brown & Duffy are two
of the words I don't want to allow as entries.
I keep getting error that says "you may not use unions, intersections, or
arrray constants for data validation criteria" (!!!!!)
thanks.

"Harlan Grove" wrote:

David Billigmeier wrote...
....
=NOT(OR(A1="text1",A1="text2"))
....

Shorter and more easily extensible as

=AND(A1<{"text1";"text2";...})

I am not getting any data entry error message at all when using the formulas
below and entering the "bad" names. One question to help me understand:what
do you mean make sure K1 is the active cell? I want the validation to apply
to the entire column.
P.S. I did not mention that I am using excel 2000, if that matters.

"David Billigmeier" wrote:

Yes, As T. Valko said you CANNOT use array functions in data validation, so
Harlan's post will not work. My original post will work, I'll update to
correspond to your range and values:

Highlight the entire column K and enter either of the following (make sure
K1 is the active cell):
=NOT(OR(K1="Brown",K1="Duffy"))
=AND(K1<"Brown",K1<"Duffy")

--
Regards,
Dave


"a-one-and-a-two" wrote:

Having a little trouble here...Am I using incorrect syntax? I am validating
column K in my worksheet, so I highlight the entire column, then for my
formula I am typing =AND(K:K<{"Brown";"Duffy"}), where Brown & Duffy are two
of the words I don't want to allow as entries.
I keep getting error that says "you may not use unions, intersections, or
arrray constants for data validation criteria" (!!!!!)
thanks.

"Harlan Grove" wrote:

David Billigmeier wrote...
....
=NOT(OR(A1="text1",A1="text2"))
....

Shorter and more easily extensible as

=AND(A1<{"text1";"text2";...})

Whenever you select a range of cells there is always an "active" cell, it's
the one that is white colored (the rest are a grey color). If K1 isn't the
active cell and you reference it in the formula, Excel will point to the
wrong location...

For example, say K3 is the active cell and you enter
=AND(K1<"Brown",K1<"Duffy"). You will only get an error iff you first set
K1 equal to Brown or Duffy and then make a change to K3 (and ONLY in that
exact sequence... complicated, I know)

Can you try it again making sure K1 is the active cell in your range?

I don't know of any reason this shouldn't work in Excel 2000, however I'm
not entirely sure, I use 2003.
--
Regards,
Dave


"a-one-and-a-two" wrote:

I am not getting any data entry error message at all when using the formulas
below and entering the "bad" names. One question to help me understand:what
do you mean make sure K1 is the active cell? I want the validation to apply
to the entire column.
P.S. I did not mention that I am using excel 2000, if that matters.

"David Billigmeier" wrote:

Yes, As T. Valko said you CANNOT use array functions in data validation, so
Harlan's post will not work. My original post will work, I'll update to
correspond to your range and values:

Highlight the entire column K and enter either of the following (make sure
K1 is the active cell):
=NOT(OR(K1="Brown",K1="Duffy"))
=AND(K1<"Brown",K1<"Duffy")

--
Regards,
Dave


"a-one-and-a-two" wrote:

Having a little trouble here...Am I using incorrect syntax? I am validating
column K in my worksheet, so I highlight the entire column, then for my
formula I am typing =AND(K:K<{"Brown";"Duffy"}), where Brown & Duffy are two
of the words I don't want to allow as entries.
I keep getting error that says "you may not use unions, intersections, or
arrray constants for data validation criteria" (!!!!!)
thanks.

"Harlan Grove" wrote:

David Billigmeier wrote...
....
=NOT(OR(A1="text1",A1="text2"))
....

Shorter and more easily extensible as

=AND(A1<{"text1";"text2";...})

Got it! Works like a charm. Many Thanks.

"David Billigmeier" wrote:

Whenever you select a range of cells there is always an "active" cell, it's
the one that is white colored (the rest are a grey color). If K1 isn't the
active cell and you reference it in the formula, Excel will point to the
wrong location...

For example, say K3 is the active cell and you enter
=AND(K1<"Brown",K1<"Duffy"). You will only get an error iff you first set
K1 equal to Brown or Duffy and then make a change to K3 (and ONLY in that
exact sequence... complicated, I know)

Can you try it again making sure K1 is the active cell in your range?

I don't know of any reason this shouldn't work in Excel 2000, however I'm
not entirely sure, I use 2003.
--
Regards,
Dave


"a-one-and-a-two" wrote:

I am not getting any data entry error message at all when using the formulas
below and entering the "bad" names. One question to help me understand:what
do you mean make sure K1 is the active cell? I want the validation to apply
to the entire column.
P.S. I did not mention that I am using excel 2000, if that matters.

"David Billigmeier" wrote:

Yes, As T. Valko said you CANNOT use array functions in data validation, so
Harlan's post will not work. My original post will work, I'll update to
correspond to your range and values:

Highlight the entire column K and enter either of the following (make sure
K1 is the active cell):
=NOT(OR(K1="Brown",K1="Duffy"))
=AND(K1<"Brown",K1<"Duffy")

--
Regards,
Dave


"a-one-and-a-two" wrote:

Having a little trouble here...Am I using incorrect syntax? I am validating
column K in my worksheet, so I highlight the entire column, then for my
formula I am typing =AND(K:K<{"Brown";"Duffy"}), where Brown & Duffy are two
of the words I don't want to allow as entries.
I keep getting error that says "you may not use unions, intersections, or
arrray constants for data validation criteria" (!!!!!)
thanks.

"Harlan Grove" wrote:

David Billigmeier wrote...
....
=NOT(OR(A1="text1",A1="text2"))
....

Shorter and more easily extensible as

=AND(A1<{"text1";"text2";...})

Phew! Good to hear. No problem

--
Regards,
Dave


"a-one-and-a-two" wrote:

Got it! Works like a charm. Many Thanks.

"David Billigmeier" wrote:

Whenever you select a range of cells there is always an "active" cell, it's
the one that is white colored (the rest are a grey color). If K1 isn't the
active cell and you reference it in the formula, Excel will point to the
wrong location...

For example, say K3 is the active cell and you enter
=AND(K1<"Brown",K1<"Duffy"). You will only get an error iff you first set
K1 equal to Brown or Duffy and then make a change to K3 (and ONLY in that
exact sequence... complicated, I know)

Can you try it again making sure K1 is the active cell in your range?

I don't know of any reason this shouldn't work in Excel 2000, however I'm
not entirely sure, I use 2003.
--
Regards,
Dave


"a-one-and-a-two" wrote:

I am not getting any data entry error message at all when using the formulas
below and entering the "bad" names. One question to help me understand:what
do you mean make sure K1 is the active cell? I want the validation to apply
to the entire column.
P.S. I did not mention that I am using excel 2000, if that matters.

"David Billigmeier" wrote:

Yes, As T. Valko said you CANNOT use array functions in data validation, so
Harlan's post will not work. My original post will work, I'll update to
correspond to your range and values:

Highlight the entire column K and enter either of the following (make sure
K1 is the active cell):
=NOT(OR(K1="Brown",K1="Duffy"))
=AND(K1<"Brown",K1<"Duffy")

--
Regards,
Dave


"a-one-and-a-two" wrote:

Having a little trouble here...Am I using incorrect syntax? I am validating
column K in my worksheet, so I highlight the entire column, then for my
formula I am typing =AND(K:K<{"Brown";"Duffy"}), where Brown & Duffy are two
of the words I don't want to allow as entries.
I keep getting error that says "you may not use unions, intersections, or
arrray constants for data validation criteria" (!!!!!)
thanks.

"Harlan Grove" wrote:

David Billigmeier wrote...
....
=NOT(OR(A1="text1",A1="text2"))
....

Shorter and more easily extensible as

=AND(A1<{"text1";"text2";...})

As T. Valko said you CANNOT use array functions in data validation

That's not what I said at all! <bg

DV won't accept array constants.

DV will accept array formulas but not array constants. These are not the
same!

An array constant can be identified by the squiggly braces around an array
of values used as an argument to a function:

=AND(K:K<{"Brown";"Duffy"})

This is an example of an array formula:

=AND(A1<List)

If an array formula is entered in a *worksheet cell* it must be entered with
the key combination of CTRL,SHIFT,ENTER (not just ENTER). Excel will enclose
the formula in the same squiggly braces but this is not an array constant:

{=AND(A1<List)}

Now, when you use an array formula in a refedit (Data Validation,
Conditional Formatting, InsertNameDefine, etc) the formula is
automatically evaluated as an array and doesn't need to be entered with the
array key combination. In fact, using the key combo will have no effect.

Biff

"David Billigmeier" wrote in message
...
Yes, As T. Valko said you CANNOT use array functions in data validation,
so
Harlan's post will not work. My original post will work, I'll update to
correspond to your range and values:

Highlight the entire column K and enter either of the following (make sure
K1 is the active cell):
=NOT(OR(K1="Brown",K1="Duffy"))
=AND(K1<"Brown",K1<"Duffy")

--
Regards,
Dave


"a-one-and-a-two" wrote:

Having a little trouble here...Am I using incorrect syntax? I am
validating
column K in my worksheet, so I highlight the entire column, then for my
formula I am typing =AND(K:K<{"Brown";"Duffy"}), where Brown & Duffy are
two
of the words I don't want to allow as entries.
I keep getting error that says "you may not use unions, intersections, or
arrray constants for data validation criteria" (!!!!!)
thanks.

"Harlan Grove" wrote:

David Billigmeier wrote...
....
=NOT(OR(A1="text1",A1="text2"))
....

Shorter and more easily extensible as

=AND(A1<{"text1";"text2";...})