populate dynamic list in dropdown without duplicates items based on sheet name

[Hasson]

Hi,
I have two sheets contain INVOICE NUMBER in column D .
what I want when write sheet name in J2 cell for OUTCOME sheet then should populate list based on column D in K2 without duplicates items .

SO.xlsm
	A	B	C	D	E	F	G
1	ITEM	DATE	BATCH	INV NO	QTY	PRICE	BALANCE
2	1	01/01/2023	BTS00	INV-001	20	120	0
3	2	01/01/2023	BTS01	INV-001	50	110	0
4	3	01/01/2023	BTS02	INV-001	50	221	0
5	4	01/01/2023	BTS03	INV-001	60	122	0
6	BALANCE	01/01/2023		INV-001			0
7	1	03/01/2023	BTS01	INV-002	70	120	0
8	2	03/01/2023	BTS02	INV-002	80	115	0
9	3	03/01/2023	BTS00	INV-002	60	112	0
10	BALANCE	03/01/2023		INV-002			0
11	1	03/01/2023	BTS01	INV-003	80	122	0
12	2	04/01/2023	BTS02	INV-003	80	111	0
13	3	05/01/2023	BTS00	INV-003	80	120	0
14	BALANCE	06/01/2023		INV-003			0
15	1	03/02/2023	BTS01	INV-004	200	110	0
16	2	04/02/2023	BTS02	INV-004	100	120	0
17	3	05/02/2023	BTS00	INV-004	120	12	0
18	BALANCE			INV-004			0
SH1

Cell Formulas
Range	Formula
G15:G17,G11:G13,G7:G9,G2:G5	G2	=I2*J2
G6	G6	=SUM(K2:K5)
G10,G18,G14	G10	=SUM(K7:K9)

SO.xlsm
	A	B	C	D	E	F	G
1	ITEM	DATE	BATCH	INV NO	QTY	PRICE	BALANCE
2	1	01/01/2023	BTS00	STT-001	12	1222	0
3	2	01/01/2023	BTS01	STT-001	10	122	0
4	3	01/01/2023	BTS02	STT-001	5	244	0
5	4	01/01/2023	BTS03	STT-001	22	145	0
6	BALANCE	01/01/2023		STT-001			0
7	1	03/01/2023	BTS01	STT-002	12	130	0
8	2	03/01/2023	BTS02	STT-002	10	230	0
9	3	03/01/2023	BTS00	STT-002	5	150	0
10	BALANCE	03/01/2023		STT-002			0
11	1	03/01/2023	BTS01	STT-003	20	130	0
12	2	04/01/2023	BTS02	STT-003	12	230	0
13	3	05/01/2023	BTS00	STT-003	22	150	0
14	BALANCE	06/01/2023		STT-003			0
15	1	03/02/2023	BTS01	STT-003	20	130	0
16	2	04/02/2023	BTS02	STT-003	12	230	0
17	3	05/02/2023	BTS00	STT-003	22	150	0
18	BALANCE	06/02/2023		STT-003			0
19	1	03/02/2023	BTS01	STT-003	134	130	0
20	2	04/02/2023	BTS02	STT-003	24	230	0
21	3	05/02/2023	BTS00	STT-003	34	150	0
22	BALANCE	06/02/2023		STT-003			0
REW

Cell Formulas
Range	Formula
G19:G21,G15:G17,G11:G13,G7:G9,G2:G5	G2	=I2*J2
G6	G6	=SUM(K2:K5)
G10,G22,G18,G14	G10	=SUM(K7:K9)

example for SH1 is written in J2 in outcome sheet when select item from K2 should be in K2 like this

every time will add data and the invoices numbers could be 4000 rows for each sheet and the duplicates will be 14000 rows
thanks

 

[kevin9999]

Please try the following on a copy of your workbook. Put all the code below in the sheet code module of the sheet with the validation list.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$J$2" Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Dim sName As String: sName = Target
        If WorksheetExists(sName) Then
            Dim d As Object, arr, i As Long
            Set d = CreateObject("scripting.dictionary")
            arr = Worksheets(sName).Range("D2", Worksheets(sName).Cells(Rows.Count, "D").End(xlUp))
            For i = 1 To UBound(arr, 1)
                d(arr(i, 1)) = 1
            Next i
            With Range("K2")
                .ClearContents
                .Validation.Delete
                .Validation.Add Type:=xlValidateList, Formula1:=Join(d.keys, ",")
                .Select
                .Value = d.keys()(0)
            End With
        Else
            MsgBox "Sheet name " & sName & " does not exist!"
            Target.ClearContents
            Target.Select
        End If
    End If
Continue:
        Application.EnableEvents = True
        Exit Sub
Escape:
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Continue
End Sub
Function WorksheetExists(sName As String) As Boolean
    WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function

 

[Hasson]

it's perfect .
much appreciate for your help .

 

[kevin9999]

Happy to help & thanks for the feedback