Cara membuat drop down dinamis bertingkat excel

• Di sheet List : Data Jenis Barang akan muncul sesuai kategori yang dipilih.
• Saya pengen drop down nya dinamis agar jika ada penambahan langsung muncul.

dan bagaimana solusi cepatnya jika data kategori dan Jenis barang lebih dari 20

File terlampir

mohon bantuanya Master @Caton

File terlampir

Misi suhu @manweljs_ bisa bantu tidak ya?

Like this ?

Alternatif pakai VBA , Dropdown Bertingkat Dinamis Jika kategori dan Jenis barang Banyak seperti ini

untuk kak @SunnyAlv , ketika rumus berikut

=IF(INDEX('Master Data'!B2:F9;;MATCH(List!B2;'Master Data'!B1:F1;0))=0;"";INDEX('Master Data'!B2:F9;;MATCH(List!B2;'Master Data'!B1:F1;0)))

saya buat Name Manager, lalu saya buat data validation hasilnya eror kak.
Cara aplikasikannya gimana ya? mohon bantuannya.

halo kak @Fika56

coba pakai rumus ini aja yaa, pakai rumus dibawah untuk Name Manager nya

=INDEX('Master Data'!B2:F9,,MATCH(List!B2,'Master Data'!B1:F1,0))

saya coba dulu kak @SunnyAlv

Kak @SunnyAlv untuk data yang kosongnya masih muncul.

untuk kak @Herry , dropdown dinamisnya bisa.
tapi ketika pindah sheet atau di klik pada cell B2, validation B3 nya hilang ya.

kak @Herry

Lalu kalau cell target nya diubah seperti gambar terlampir, code VBA nya gimana. saya coba begini tidak berhasil.

Private Sub Worksheet_Activate()

Dim Sw As Worksheet, Hw As Worksheet, range1 As Range, rng As Range, RowSumber As Long

Application.ScreenUpdating = False

Set Sw = Worksheets("Master Data")
Set Hw = Worksheets("List")
RowSumber = Sw.Range("A" & Rows.Count).End(xlUp).Row
Set range1 = Sw.Range("A2:A" & RowSumber)
Set rng = Hw.Range("A9")

Hw.Range("A9:B9") = vbNullString

With rng.Validation
  .Delete
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='" & Sw.Name & "'!" & range1.Address
End With

Application.ScreenUpdating = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Sw As Worksheet, Hw As Worksheet, range1 As Range, rng As Range, RowSumber As Long, LastColumn As Long
Dim Awal As Long, ColumnSumber As Variant

Application.ScreenUpdating = False

Set Sw = Worksheets("Master Data")
Set Hw = Worksheets("List")

If Target.Address = "$A$9" Then Hw.Range("B9") = vbNullString

If Target.Address = "$B$9" Then
   With Sw
     LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
     For Awal = 2 To LastColumn
        If Hw.Range("A9") = .Cells(1, Awal) Then
           ColumnSumber = Split((Columns(Awal).Address(, 0)), ":")(0) '===> convert Angka to Abjad Column
           RowSumber = Sw.Range(ColumnSumber & Rows.Count).End(xlUp).Row
           Set range1 = .Range(ColumnSumber & "2:" & ColumnSumber & RowSumber)
           Set rng = Hw.Range("B9")
           If RowSumber > 2 Then
             With rng.Validation
               .Delete
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='" & Sw.Name & "'!" & range1.Address
             End With
           ElseIf RowSumber = 2 Then
             X = .Cells(2, ColumnSumber)
             With rng.Validation
               .Delete
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=X
             End With
           Else
             MsgBox "Tidak Ada Jenis Barang Di Kategori ini"
           End If
        End If
   Next Awal
  End With
End If

Application.ScreenUpdating = True

End Sub

klu mau pindah sheet atau di klik pada cell B2, validation B3 nya tidak hilang maka kode
di Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$2" Then Hw.Range("B3") = vbNullString dihapus saja

untuk kodenya saya coba berhasil klu di ganti cell targetnya

klu mau pindah sheet atau di klik pada cell B2, validation B3 nya tidak hilang maka kode
di Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$2" Then Hw.Range("B3") = vbNullString dihapus saja

untuk kodenya saya coba berhasil klu di ganti cell targetnya

ketinggalan filenya

kak @Herry ,
hampir berhasil kak. Ketika di sheet list kursor diletakkan dimana saja data tidak hilang.

tapi ketika pindah sheet datanya masih hilang kak.

kak @Fika56 adanya blank itu karena mengikuti jumlah array formulanya

Kak @SunnyAlv , apakah tidak ada kombinasi menggunakan rumus offset?

tapi ketika saya menggunakan rumus berikut juga tidak berhasil,

=OFFSET('Sumber Data'!$A$1;1;0;COUNTA('Sumber Data'!$A:$A)-1;1)

saya coba cari rumus bagusnya, pakai excel versi berapa ya klo boleh tau @Fika56

Private Sub Worksheet_Activate()
Hw.Range("A9:B9") = vbNullString dihapus biar pindah sheet tidak hilang textnya

hi kak @Fika56

udah nemu nih rumusnya, pake ini yaa

=OFFSET(INDEX('Master Data'!B2:F9,,MATCH(List!B2,'Master Data'!B1:F1,0)),,,COUNTA(INDEX('Master Data'!B2:F9,,MATCH(List!B2,'Master Data'!B1:F1,0))))