=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Kenny wrote:

This worked great also and is simpler. Could you please explain how the
formula works. I dont get it at all. What are all the - for? THANKS SO MUCH!

"Peo Sjoblom" wrote:

Well I am not going to go back and read all the threads but if that really
worked for you then something like this should work in earlier versions

=SUMPRODUCT(--($A$1:$A$65535=A6),--($L$1:$L$65535=""))=1

For 2007 you can test it with this

=SUMPRODUCT(--(A:A=A6),--(L:L=""))=1


If I were you I would probably try to tweak something from Biff's answer, I
am surprised the COUNTIFS worked



--

Regards,

Peo Sjoblom









"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

--

Dave Peterson