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))))

kak @Herry , terimakasih. Code berhasil.

sebenarnya saya kurang paham terkait VBA.

Yang ingin saya tanyakan lagi bagaimana cara nya code tersebut di copy ke file yang baru? dan apabila ada perubahan nama sheet dan letak cell target validation, cara nya gimana ya?

saya sudah coba langkah berikut :

1. Open VBA
2. Insert Module di List Pengeluaran
3. Copy paste Code
4. Tutup editor VBA
5. Save Excel

tapi tidak ada perubahan apapun di file excel yang baru. Saya pengen memahami sedikit makanya saya ubah-ubah letak cell dan file nya. Mohon penjelasannya sedikit ya kak.

Sekarang saya ubah nama Sheet nya menjadi :

• Master Data
• List Pengeluaran

Data Validationnya di List Pengeluaran (Gambar terlampir):

• Kategori : Kolom C9 hingga seterusnya
• Jenis Barang : Kolom D9 hingga seterusnya

ini kode yang saya gunakan.

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 Pengeluaran")
RowSumber = Sw.Range("A" & Rows.Count).End(xlUp).Row
Set range1 = Sw.Range("A2:A" & RowSumber)
Set rng = Hw.Range("C9")

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 Pengeluaran")

'If Target.Address = "$B$2" Then Hw.Range("B3") = vbNullString '==> klu textnya dikolom B3 Tidak Mau Hilang Kode Ini Dibatalkan

If Target.Address = "$D$9" Then
   With Sw
     LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
     For Awal = 2 To LastColumn
        If Hw.Range("C9") = .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("D9")
           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

Kak @SunnyAlv saya coba dulu ya kak.

itu naroh rumusnya di Name Managernya ya?

iyaa, barusan saya coba berhasil

Kak @SunnyAlv baik kak berhasil terimakasih ya.

oh ya untuk tanda separator , kenapa ada beberapa kali ya? tapi tidak ada reference nya?

Sama sama kak @Fika56

Berarti itu menunjukan tidak ada spesifik/referensi valuenya atau bisa merujuk angka "0" jika merujuk pada rumus kak Fika yang offset tadi

Kak @SunnyAlv kalau diapliaksikan data validationnya ke bawah kok gak bisa ya kak

@Fika56 di saya aman ko bisa untuk list validasi, kalo ga bisa coba jangan pake Name Manager, langsung taruh rumusnya di list validasi

Kak @SunnyAlv, sudah bisa kak. Tadi belum kunci beberapa rumus nya.

terimakasih kak sangat membantu

Copy Code di Sheet2(List) ke Sheet di workbook baru yang mau ditempatkan data validationnya (Jangan insert module). karena perlu event trigger untuk aktifkan kode
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ==> Aktif kodenya jika kursor di sheet tersebut berubah posisi

klu mau tiap ada data baru di sheet list muncul validationnya kodenya sudah saya rubah + ada penjelasannya (Triggernya klu Ada Data tambahan di kolom A maka otomatis klu kursor digeser ke kolom B maka kolom B ada validationnya)

kak @Herry , terimakasih penjelasannya.

saya coba pelajari ya...

Kak @SunnyAlv permisi, saya buat drop list di name manager rumusnya seperti dibawah ini. Tapi kenapa masih muncul baris kosong ya?

=OFFSET(INDEX('Master Data'!$AR$2:$AW$9000;;MATCH('List Pengeluaran'!$C9;'Master Data'!$AR$1:$AW$1;0));;;COUNTA(INDEX('Master Data'!$AR$2:$AW$9000;;MATCH('List Pengeluaran'!$C9;'Master Data'!$AR$1:$AW$1;0))))

@Fika56 coba lempar filenya kak

Coba pakai ini

=LET(c,FILTER('Master Data'!$AR$2:$AW$9000,'Master Data'!$AR$1:$AW$1='List Pengeluaran'!$C9),FILTER(c,c<>"",""))

Kak @SunnyAlv terimaaksih sudah bisa :)