you rock! that worked. the one I made only works in 2007. I had to have it
work in 2003. I wish you could help me understand how that formula works. Its
very confusing. THANKS!


"T. Valko" wrote:

Well I could not get that to work at all

Hmmm...

It works on my end. I'm pretty sure I understand (now) what you want.

This workbook has to work in 2003 as well.
It has to work for the entire sheet all the way to the last row

Are you using *every* row for data? Do you have a header row?

The formula I suggested can be shortened very slightly by using OR instead
of IF and you can use SUMPRODUCT for the entire column less 1 row so if you
have a header row:

=OR(COUNTIF(A:A,A2)<=1,COUNTIF(A:A,A2)-1=SUMPRODUCT(--(A$2:A$65536=A2),--(L$2:L$65536<"")))

However, using SUMPRODUCT on that large of a range may slow things down a
little.

--
Biff
Microsoft Excel MVP


"Kenny" wrote in message
...
This workbook has to work in 2003 as well. Can you please tell me how to
make
the sum product work. It has to work for the entire sheet all the way to
the
last row. I tried and tried but it will not work? Please help!


"Peo Sjoblom" wrote:

You can't use that method anywhere else but Excel 2007, however
it is certainly possible to get the same result using SUMPRODUCT


--

Regards,

Peo Sjoblom




"Kenny" wrote in message
...
Well I could not get that to work at all, but it did give me some
insight.
Check this out.

=COUNTIFS(A:A,A6,L:L,"")=1

This works awsome!!!!
Thanks for your help!!!


"T. Valko" wrote:

Ok, so we need to test *every* instance of the account number...

Try this:

=IF(COUNTIF(A:A,A1)<=1,TRUE,COUNTIF(A:A,A1)-1=SUMPRODUCT(--(A$1:A$100=A1),--(L$1:L$100<"")))

SUMPRODUCT will not accept entire columns as range references (unless
you're
using Excel 2007) so we need to define a specific range.

Scenario 1 allows you to enter the acct # in A1

......A.....L
1...1.......

Scenario 2 does not allow you to enter the same acct # in A2

......A.....L
1...1.......
2...1.......

Scenario 3 allows you to enter the same acct # in A2

......A.....L
1...1.....x
2...1

Scenario 4 does not allow you to enter the same acct # in A3

......A.....L
1...1.....x
2...1.......
3...1.......


--
Biff
Microsoft Excel MVP


"Kenny" wrote in message
...
Okay its working, but its not working See example.

Scanario 1:
Col A Col L
Account Number Deactive
023-64000 Deactive

Result: I enter the same account number it lets me. This is correct.

Scanario 2:
Col A Col L
Account Number Deactive
023-64000 *NOTHING ENTERD*

Result: I enter the same account number it will not let me. This is
correct.

Scanario 3:
Col A Col L
Account Number Deactive
023-64000 Deactive
023-64000 *NOTHING ENTERD*

Result: I enter the same account number it lets me enter the account
number
again This is incorrect. Our validation only checks 1 instance to
see
if l
is
populated. It needs to check all instances of duplicate account
numbers
if
L
is empty in any of the instances it will not let me enter the
account
number
again?






"T. Valko" wrote:

Try this:

DataValidation
Allow: Custom
Formula:

=IF(COUNTIF(A:A,A1)<=1,TRUE,INDEX(L:L,MATCH(A1,A:A ,0))<"")

Note that this will only work if the data is entered from the top
down
due
to how MATCH works.

--
Biff
Microsoft Excel MVP


"Kenny" wrote in message
...
Chip thanks this would work in a normal circumstance, but not
working
for
what I need. This is what I need to happen.

If Account Number being entered into Col A matches a exsisting
record
in
col
A and the exsisting record's col L is populated with any value
then
you
can
enter the duplicate account number.

Else Col L of exsisting record is empty then clear current cell A
and
msg
box This account number is active.

In otherwords Col L decided wether a record is active or not. If
the
record
is active then you cannot enter the account number again. But if
it
is
populated then the record is deactivated and you can enter it
again.
Thanks!

"Chip Pearson" wrote:

You can do this with data validation *if* the data entered in
column
A
is
entered from the top down.

You can do it even if the values are entered into column A in an
arbitrary
manner; there is no need for the "top down" restriction. Suppose
you
can
safely say that ALL the data will be entered somewhere within
the
range
A1:A20. Even if you don't use all the cells in that range, you
can
be
sure
than no data will be entered in other rows. Given that, select
A1:A20,
go
to
the Data menu, choose Validation, and then Custom in the Allow
list.
There,
enter the formula:

=COUNTIF($A$1:$A$20,A1)=1

This will prevent duplicates in A1:A20 regardless in the order
in
which
they
are ordered.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"T. Valko" wrote in message
...
You can do this with data validation *if* the data entered in
column
A
is
entered from the top down. For example, you enter an account
number
in
A1
then A2 then A3 etc. It won't work if you enter the data
randomly
like
in
A10 then A2.

Interested in that?

--
Biff
Microsoft Excel MVP


"Kenny" wrote in message
...
I have a spreadsheet Column A is the account number. I want to
write
a
procedure that if the user enters the same account number in
column
a
that
already exsits and that exsisting record has nothing in
column L
then
it
clears the cell and msgbox them record already exsists. If
column l
of
the
exsisting record does contain data it will allow them to add
the
account
number over again. Here is my code and I have not started
this
procedure
anywhere in this code. Thanks!

Option Explicit

Sub SortThisSheet()

Dim LastRow As Long