How to make a drop down list that hides previously used items in just the same row

Assuming your data is from A1:N4, where row 1 is the header and column A is the time slot.

I assume that requirement is "Not to allow repetition in the same row as well as in the same column"

It can achieved by data validation.

Select the range B2:N4,  cursor on cell B2.  Go to data validation/In Allow tab choose Custom and in formula tab give this formula

=AND(COUNTIF($B2:$N2,B2)=1,COUNTIF(B$2:B$4,B2)=1)

Then click Ok and come out of Data Validation

It will ensure that no item is repeated in same row as well as same column. 

You may also give error message, if desired

Range can be changed as per requirement.

Also find a link to see it.

http://sdrv.ms/18gKuUA

That works, though I have to change the formula for each row, is there a way to use this function and
have a drop down list at the time? That would be helpful.

Trying to make a drop down list that will hide a previously used item in the same row,........

Hi,

it's not what you want to accomplish
but try this...

Instead of hide a previously used item, from a drop down list

If you try to add an existing item, twice,
then you have a message...

[update: July 23, 2017....]

Private Sub Worksheet_Change(ByVal Target As Range)

'horizontal

On Error Resume Next
Dim c1 As Long, c2 As Long
Dim rng As Range
Set rng = Range("A1:D5") 'range/drop down list
c1 = rng.Column
c2 = rng.Columns.Count
If Not Intersect(Target, rng) Is Nothing Then
For Each r In Cells(Target.Row, c1).Resize(, c2) 'new line
If Target.Value = "" Then Exit Sub
If r.Address <> Target.Address And r.Value = Target.Value Then
MsgBox "wrong"
Target.Value = ""
Exit Sub
End If
Next
End If
On Error GoTo 0
End Sub

...http://youtu.be/6Ooglyh3clM

Hi, Thank you for the source for VB, but I am wondering how would I edit this same code so instead of reducing duplicate through row (horizontal), it prevent duplicate across column (vertical) instead? 

Hi,

try this approach....

(I assume that you have a drop down list with 5 or more unique items

since you have 5 rows)

[update: July 23, 2017.....]

Private Sub Worksheet_Change(ByVal Target As Range)
'vertical
On Error Resume Next
Dim r1 As Long, r2 As Long
Dim rng As Range
Set rng = Range("A1:D5") '<< range/drop down list 
r1 = rng.Row
r2 = rng.Rows.Count
If Not Intersect(Target, rng) Is Nothing Then
For Each r In Cells(r1, Target.Column).Resize(r2) 'new line
If Target.Value = "" Then Exit Sub
If r.Address <> Target.Address And r.Value = Target.Value Then
MsgBox "wrong"
Target.Value = ""
Exit Sub
End If
Next
End If
On Error GoTo 0
End Sub

https://youtu.be/d6wN93esJqQ