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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

That just prevents duplicates. This application is for duplicates with a
condition.



--
Biff
Microsoft Excel MVP


"Chip Pearson" wrote in message
...
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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

Thanks!

I had to change the code to:
=IF(COUNTIF(A:A,A1)=1,TRUE,INDEX(L:L,MATCH(A1,A:A, 0))<"")

For some reason it wont accept a or < for the count if. I tested it with
the = 1 and it works even if their are multiple records with the same account
number that all duplicates has l active. So it works not sure why though?


"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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

Thanks for all your help. Can you help me with my other post.

I would like to simplify my command button click procedures. I want to call a
sub procedure from each click event. The click event will set a variable to
the command button I want disabled. The called sub will first endable all the
command buttons and then disable the one that I chose. Please see my example
of simplify below. my current code will follow.

EXAMPLE OF SIMPLIFY NOT WORKING:
Option Explicit
Dim Choice

Sub CommandButtonEnable()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Choice.Enabled = False
End Sub

Private Sub ActiveRecords_Click()
Choice=ActiveRecords
End Sub

Private Sub ReportPreview_Click()
Choice=ReportPreview
End SUb


EXSISTING CODE:
Option Explicit

Sub SortThisSheet()

Dim LastRow As Long

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D:D"), .Cells) Is Nothing Then
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub





Was this post helpful




"T. Valko" wrote:

That just prevents duplicates. This application is for duplicates with a
condition.



--
Biff
Microsoft Excel MVP


"Chip Pearson" wrote in message
...
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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

This is in validation code:
=IF(COUNTIF(A:A,A6)=1,TRUE,INDEX(L:L,MATCH(A6,A:A, 0))<"")

Okay its working, but its not working Please See the following examples.

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?

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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker
Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial
Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial
Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial
Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

I'm not a very good programmer (yet!).

--
Biff
Microsoft Excel MVP


"Kenny" wrote in message
...
Thanks for all your help. Can you help me with my other post.

I would like to simplify my command button click procedures. I want to
call a
sub procedure from each click event. The click event will set a variable
to
the command button I want disabled. The called sub will first endable all
the
command buttons and then disable the one that I chose. Please see my
example
of simplify below. my current code will follow.

EXAMPLE OF SIMPLIFY NOT WORKING:
Option Explicit
Dim Choice

Sub CommandButtonEnable()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Choice.Enabled = False
End Sub

Private Sub ActiveRecords_Click()
Choice=ActiveRecords
End Sub

Private Sub ReportPreview_Click()
Choice=ReportPreview
End SUb


EXSISTING CODE:
Option Explicit

Sub SortThisSheet()

Dim LastRow As Long

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D:D"), .Cells) Is Nothing Then
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub





Was this post helpful




"T. Valko" wrote:

That just prevents duplicates. This application is for duplicates with a
condition.



--
Biff
Microsoft Excel MVP


"Chip Pearson" wrote in message
...
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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd,
Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo"
Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo"
Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

okay I cannot get this to work, can you help me. Also I need it to work for
every roll possible to the last row in excel. I have to have this work in
2003, please 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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub

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

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1),
Order1:=xlAscending, _
Header:=xlYes,
Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For
Repo"
Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

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

Sub SortThisSheet()

Dim LastRow As Long

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1),
Order1:=xlAscending, _
Header:=xlYes,
Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For
Repo"
Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

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

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

Use Peo's suggestion. It works. I was over-complicating things!

--
Biff
Microsoft Excel MVP


"Kenny" wrote in message
...
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

=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