I want to avoid duplicates entre from dropdown list i made using offset formula

[mosh1993]

I have created a dependent drop down list using this formula OFFSET($B$1,MATCH(P1,$A$2:$A$174,0),0,COUNTIF($A$2:$A$174,P1),1) as shown in the attached picture , when ever i change descreption i can change the serial nambers , but i want to make sure that i dont pick the same serial number twice , i want excel to stop me if i picked the same serial number , how can i do it

 

[Artik]

Maybe use conditional formatting where you have the ability to define duplicate values.

Artik

 

[mosh1993]

Maybe use conditional formatting where you have the ability to define duplicate values.

Artik

Already did that , but i want excel to stop me how can i do it

 

[Artik]

In the sheet module (not in the standard module!) insert the following event procedure

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim RngValue As Variant
    Dim lRow As Long
    Dim i As Long
    Dim oDic As Object

    Const MonitColumn As String = "R"

    Set oDic = CreateObject("Scripting.Dictionary")

    Set Rng = Intersect(Target, Me.Columns(MonitColumn).Cells)

    If Not Rng Is Nothing Then
        lRow = Me.Cells(Me.Rows.Count, MonitColumn).End(xlUp).Row

        RngValue = Me.Range(MonitColumn & "2:" & MonitColumn & lRow).Value

        Application.EnableEvents = False
        On Error Resume Next

        For i = 1 To UBound(RngValue)
            Err.Clear
            oDic.Add CStr(RngValue(i, 1)), Empty

            If Err.Number <> 0 Then
                MsgBox "Duplicate in cell " & MonitColumn & i + 1# & " !", vbExclamation
                Application.Undo
                Exit For
            End If
        Next i

        Application.EnableEvents = True
    End If
End Sub

Artik

 

[Peter_SSs]

Welcome to the MrExcel board!

I have created a dependent drop down list using this formula OFFSET($B$1,MATCH(P1,$A$2:$A$174,0),0,COUNTIF($A$2:$A$174,P1),1)

Is that really the formula used given that P1 contains "Description"? Of course I do not know what is in columns A & B or just what your circumstances are but it seems more likely to me that the drop-down choices would relate to P2, P3 etc

If I am on the right track then you can do it with Data validation and without vba if you are happy to use a number of helper columns

Below I have a very simple example of what I am guessing your structure is. For helper columns I have used from column AA and to the right. You will need as many helper columns as the longest list of Sn's for a single description in column A. So for my sample data I need 6 helper columns because Description "b" has 6 Sn's associated.
The formula shown in AA2 needs to be copied down as many rows as you want to have the Data validation (to row 15 for me)
The helper columns could then be hidden if you want.
Data Validation is set up as shown below my mini sheet.

24 12 09.xlsm
	A	B														P		R								Z	AA	AB	AC	AD	AE	AF	AG
1	Description	Sn														Description		Sn
2	a	1														b											4	5	6	7	8	9
3	a	2														a											1	2	3
4	a	3
5	b	4
6	b	5
7	b	6
8	b	7
9	b	8
10	b	9
11
12
13
14
15
DV No Dupes

Cell Formulas
Range	Formula
AA2:AF2,AA4:AA15,AA3:AC3	AA2	=TRANSPOSE(FILTER(B$2:B$10,(A$2:A$10=P2)*(COUNTIFS(P$2:P$95,A$2:A$10,R$2:R$95,B$2:B$10)=0),""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
R2:R15	List	=AA2#

Now, once I have filled in a few cells in column R, this is now what I see in the DV drop-down for cell R4. You see that 6 & 8 no longer appear in the list because they have already been used.

Apologies if I have completely misinterpreted what you are trying to do.

Also, for the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)