Duplicate Entry

[chipgiii]

I have a spreadsheet that individual projects per row. As you go through the cells of the rows you can see the project status via a validation pull down selection. The column labels are week ending dates.

I want somewhere of making either a notification or not allowing a duplicate entry for: Construction Start (can only be one in the row), and the same for CP Approved, same for Construction Complete. Those three things can only happen once, they not continuous events during the project. Whereas, I have multiple Construction Boxes under week ending dates to illustrate the construction phase is ongoing until it says "construction complete."

Hope someone can help with vba or formula. Formula in cells may be a problem since each cell is a validation box permitting construction phase choices via pull down menu. Just don't want the three mentioned above to allow being selected twice.

If this doesn't make sense, let me know and I will try to clarify.

Thanks,
Chip

 

[MickG]

If your Drop Downs are in column "A" , try this:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A:A")) Is Nothing Then
    If Target = "Construction Start" Or Target = "CP Approved" Or Target = "Construction Completed" Then
        If Application.CountIf(Columns("A:A"), Target) > 1 Then
            Target = ""
        End If
    End If
End If
End Sub

 

[chipgiii]

Hi Mick,

The data goes in rows. Each cell of Row 7 for instance, has drop downs that allows user to select the project status that goes with the dates going across row 1. So if I have 20 projects, I have 20 rows of status, project dependent aligned with the dates in row 1. Each cell in the row has a pull down selector.

I am not sure what the right approach here. I was, prior to this, attempting to see if I could put a formula in the validation box, but that doesn't seem possible. So I thought maybe it was a vba solution. I also considered conditional formatting.

I tried changing your code (big time novice here) to rows, but realize it would be row specific. So if vba is possible it would have to be able to loop somehow since each row is an independent project.

Hopefully this can be accomplished as the spreadsheet user continually repeats certain entries, that should not, cannot be recorded twice (the ones we are already discussing). I don't know if there's a simple solution, perhaps not.

 

[MickG]

Try this:-
The code treats each row as an indiviual row with its own "Drop Down" and will only allow One of the special Selected items per row.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A" & Target.row & ":" & "Z" & Target.row)) Is Nothing Then
    If Target = "Construction Start" Or Target = "CP Approved" Or Target = "Construction Completed" Then
        If Application.CountIf(Range("A" & Target.row & ":" & "Z" & Target.row), Target) > 1 Then
            Target = ""
        End If
    End If
End If
End Sub

The columns Go from "A to Z" alter as required.

 

[chipgiii]

Mick,

That is perfect! Works like a charm. I struggle with VBA, can never devote enough time to it. The old too busy drowning to learn how to swim. This is going to save me a lot of aggravation, and the person who inputs the information a lot of frustration.

Thanks a million,
Chip