dropdown list bertingkat

  1. 4 minggu lalu

    Selamat sore,
    saya mau tanya mengenai Drop Down List Bertingkat..
    Pada sheet kategori, Saya buat define name dengan fungsi =OFFSET(kategori!$B$3;1;0;COUNTA(kategori!$B:$B)-1;1) ...
    kemudian di sheet Data, untuk data validation ke-2 nya saya pakai rumus, =INDIRECT(SUBSTITUTE(SUBSTITUTE($B$4;" ";"_");"&";"_")) ... Tapi ternyata tidak muncul list nya..

    Namun saat saya ganti define name seperti biasa, tidak pakai fungsi Offset. List nya muncul..
    Apa yang salah ya?

    Terimakasih

    @Fika56 ...

    Fungsi INDIRECT yang digunakan pada formula:

    =INDIRECT(SUBSTITUTE(SUBSTITUTE($B$4;" ";"_");"&";"_"))

    tidak akan menghasilkan apapun, selain angka yang ada pada kolom B. Saya tidak tahu apakah fungsi tersebut memang dibutuhkan dikarenakan teks pada kolom B aktualnya harus dibersihkan dahulu (menggunakan fungsi SUBSTITUTE). Pada contoh yang diberikan, kolom B hanya berisi indeks baris data saja, sehingga tidak ada teks atau karakter yang perlu dibersihkan, dan tidak ada nama sheet atau nama range yang dirujuk.

    Terlepas dari hal di atas, dalam menyusun DropDown List bertingkat, nilai yang dihasilkan dari DropDown level 1 akan dijadikan acuan untuk menentukan data isian DropDown List level 2, dan demikian seterusnya. Dalam contoh yang diberikan, seharusnya nilai pada kolom C (KATEGORI) menjadi acuan dalam menentukan isi daftar DropDown pada kolom D (JENIS). Formulasinya bisa beragam, namun umumnya menggunakan fungsi OFFSET.

    Pada contoh solusi terlampir, untuk mendapatkan daftar isian DropDown List pada kolom D (JENIS), secara alur dapat dijelaskan sebagai berikut:

    [1]. Idenya dasarnya adalah menentukan terlebih dahulu range data sesuai kategori terpilih. Sesuai bentuk tabel Jenis, maka yang diperlukan adalah menentukan dahulu indeks kolom data sesuai kategori terpilih menggunakan formula:

    =MATCH(DATA!$C4;KATEGORI!$D$3:$Z$3;0)

    Pada formula di atas, nilai yang dicari ditentukan secara relatif (DATA!$C4), sehingga saat formula disalin ke bawah, maka posisi kolom akan tetap, namun posisi baris akan berubah. Ini membuat formula tersebut menjadi dinamis. Pada formula tersebut, range pencarian nama kategori sengaja saya buatkan melebar (KATEGORI!$D$3:$Z$3) dengan tujuan saat menambahkan data untuk kategori yang baru (misalkan pada kolom H, kolom I dan seterusnya), tidak perlu repot lagi mengubah formula...

    Tips: uji formula, misalkan dengan menyusun formula pada sel F4 pada sheet DATA. Salin formula ke bawah, dan pastikan formula menghasilkan indeks kolom yang sesuai pada tabel JENIS sheet KATEGORI.

    [2]. Oleh karena tujuan ide dasarnya adalah untuk menentukan range data sesuai kategori yang ditentukan (dipilih) pada kolom C sheet DATA, maka selanjutnya akan digunakan fungsi OFFSET. Pada tahap ini, tujuannya adalah membuat range data bersifat dinamis. Jadi, saat ada perubahan pilihan kategori, maka range data secara otomatis akan merujuk ke kolom yang tepat. Dalam hal ini, range data akan dibentuk dengan menggeser posisi awal dari kolom C sebanyak n-kolom, dimana jumlah kolom yang akan digeser ditentukan melalui fungsi MATCH pada poin 1 di atas.

    Dengan asumsi bahwa nantinya jumlah data jenis pada masing-masing kolom kategori berbeda-beda dikarenakan penambahan atau pengurangan data, maka pada tahap awal jumlah baris range data yang akan dibuat ditentukan secukupnya sesuai estimasi datanya, misalkan 1000 baris. Maka, formulanya bisa kita buat menjadi:

    =OFFSET(KATEGORI!$C$3;1;MATCH(DATA!$C4;KATEGORI!$D$3:$Z$3;0);1000;1)

    [3]. Meski fungsi OFFSET dapat digunakan pada DropDown Validation List, namun formula pada poin 2 di atas tidak akan bisa digunakan secara langsung dalam formula pada DropDown Validation List, dikarenakan pada formula tersebut terdapat rujukan ke sheet berbeda (yakni pada formulasi dengan fungsi MATCH). Untuk itu, formula pada poin 2 di atas akan dijadikan sebuah range melalui fitur Named Range.

    Oleh karena posisi DropDown List nantinya akan dibuat pada sheet DATA mulai dari baris ke-4, dan nantinya akan disalin ke baris selanjutnya, maka langkah pada tahap ini penting untuk dipahami. Untuk membuat range dinamis melalui formula pada poin 2 di atas:

    >>> Langkah pertama adalah dengan meletakkan pointer pada baris ke-4 (sesuai baris data pertama yang akan dicari yakni pada sel C4) pada sheet DATA. Kemudian buka jendela Name Manager, buat nama baru (misalkan JENIS.DATA) dan masukkan formula pada poin 2 di atas.

    >>> Langkah kedua, adalah dengan menguji nama range yang sudah dibuat tersebut apakah sudah menghasilkan nilai yang tepat dan dinamis, misalnya dengan membuat formula berikut pada sel F4 sheet DATA:

    =INDEX(JENIS.DATA;2)

    Salin formula pada sel F4 tersebut ke baris berikutnya. Jika benar, maka masing-masing baris akan menghasilkan nilai berupa data ke-2 dari masing-masing kolom tabel pada sheet KATEGORI sesuai pilihan kategori pada kolom C sheet DATA.

    [4]. Jika proses sampai poin 3 di atas selesai, maka yang dilakukan selanjutnya adalah membentuk daftar data aktualnya sesuai pilihan kategorinya (Clean List). Oleh karena pada formula poin 2 di atas baris kosong akan ikut masuk ke dalam range, maka baris-baris kosong harus dibuang. Untuk itu, digunakan formula:

    =OFFSET(JENIS.DATA;0;0;COUNTA(JENIS.DATA);1)

    Sampai tahap ini, maka formula di atas bisa digunakan langsung pada DropDown Validation List, atau bisa juga dibuatkan nama range seperti pada tahapan pada poin 3 di atas (misalkan dengan nama JENIS.VALIDASI).

    Semoga penjelasan di atas dapat dipahami untuk diterapkan saat membuat nama range dinamis lainnya. Contoh solusi terlampir.

    Demikian.

  2. ini file contohnya..
    mohon pencerahannya para suhu, hehe
    @Caton

  3. Caton

    Agu 19 Terverifikasi Jawaban Terpilih Indonesia + 14.352 Poin

    @Fika56 ...

    Fungsi INDIRECT yang digunakan pada formula:

    =INDIRECT(SUBSTITUTE(SUBSTITUTE($B$4;" ";"_");"&";"_"))

    tidak akan menghasilkan apapun, selain angka yang ada pada kolom B. Saya tidak tahu apakah fungsi tersebut memang dibutuhkan dikarenakan teks pada kolom B aktualnya harus dibersihkan dahulu (menggunakan fungsi SUBSTITUTE). Pada contoh yang diberikan, kolom B hanya berisi indeks baris data saja, sehingga tidak ada teks atau karakter yang perlu dibersihkan, dan tidak ada nama sheet atau nama range yang dirujuk.

    Terlepas dari hal di atas, dalam menyusun DropDown List bertingkat, nilai yang dihasilkan dari DropDown level 1 akan dijadikan acuan untuk menentukan data isian DropDown List level 2, dan demikian seterusnya. Dalam contoh yang diberikan, seharusnya nilai pada kolom C (KATEGORI) menjadi acuan dalam menentukan isi daftar DropDown pada kolom D (JENIS). Formulasinya bisa beragam, namun umumnya menggunakan fungsi OFFSET.

    Pada contoh solusi terlampir, untuk mendapatkan daftar isian DropDown List pada kolom D (JENIS), secara alur dapat dijelaskan sebagai berikut:

    [1]. Idenya dasarnya adalah menentukan terlebih dahulu range data sesuai kategori terpilih. Sesuai bentuk tabel Jenis, maka yang diperlukan adalah menentukan dahulu indeks kolom data sesuai kategori terpilih menggunakan formula:

    =MATCH(DATA!$C4;KATEGORI!$D$3:$Z$3;0)

    Pada formula di atas, nilai yang dicari ditentukan secara relatif (DATA!$C4), sehingga saat formula disalin ke bawah, maka posisi kolom akan tetap, namun posisi baris akan berubah. Ini membuat formula tersebut menjadi dinamis. Pada formula tersebut, range pencarian nama kategori sengaja saya buatkan melebar (KATEGORI!$D$3:$Z$3) dengan tujuan saat menambahkan data untuk kategori yang baru (misalkan pada kolom H, kolom I dan seterusnya), tidak perlu repot lagi mengubah formula...

    Tips: uji formula, misalkan dengan menyusun formula pada sel F4 pada sheet DATA. Salin formula ke bawah, dan pastikan formula menghasilkan indeks kolom yang sesuai pada tabel JENIS sheet KATEGORI.

    [2]. Oleh karena tujuan ide dasarnya adalah untuk menentukan range data sesuai kategori yang ditentukan (dipilih) pada kolom C sheet DATA, maka selanjutnya akan digunakan fungsi OFFSET. Pada tahap ini, tujuannya adalah membuat range data bersifat dinamis. Jadi, saat ada perubahan pilihan kategori, maka range data secara otomatis akan merujuk ke kolom yang tepat. Dalam hal ini, range data akan dibentuk dengan menggeser posisi awal dari kolom C sebanyak n-kolom, dimana jumlah kolom yang akan digeser ditentukan melalui fungsi MATCH pada poin 1 di atas.

    Dengan asumsi bahwa nantinya jumlah data jenis pada masing-masing kolom kategori berbeda-beda dikarenakan penambahan atau pengurangan data, maka pada tahap awal jumlah baris range data yang akan dibuat ditentukan secukupnya sesuai estimasi datanya, misalkan 1000 baris. Maka, formulanya bisa kita buat menjadi:

    =OFFSET(KATEGORI!$C$3;1;MATCH(DATA!$C4;KATEGORI!$D$3:$Z$3;0);1000;1)

    [3]. Meski fungsi OFFSET dapat digunakan pada DropDown Validation List, namun formula pada poin 2 di atas tidak akan bisa digunakan secara langsung dalam formula pada DropDown Validation List, dikarenakan pada formula tersebut terdapat rujukan ke sheet berbeda (yakni pada formulasi dengan fungsi MATCH). Untuk itu, formula pada poin 2 di atas akan dijadikan sebuah range melalui fitur Named Range.

    Oleh karena posisi DropDown List nantinya akan dibuat pada sheet DATA mulai dari baris ke-4, dan nantinya akan disalin ke baris selanjutnya, maka langkah pada tahap ini penting untuk dipahami. Untuk membuat range dinamis melalui formula pada poin 2 di atas:

    >>> Langkah pertama adalah dengan meletakkan pointer pada baris ke-4 (sesuai baris data pertama yang akan dicari yakni pada sel C4) pada sheet DATA. Kemudian buka jendela Name Manager, buat nama baru (misalkan JENIS.DATA) dan masukkan formula pada poin 2 di atas.

    >>> Langkah kedua, adalah dengan menguji nama range yang sudah dibuat tersebut apakah sudah menghasilkan nilai yang tepat dan dinamis, misalnya dengan membuat formula berikut pada sel F4 sheet DATA:

    =INDEX(JENIS.DATA;2)

    Salin formula pada sel F4 tersebut ke baris berikutnya. Jika benar, maka masing-masing baris akan menghasilkan nilai berupa data ke-2 dari masing-masing kolom tabel pada sheet KATEGORI sesuai pilihan kategori pada kolom C sheet DATA.

    [4]. Jika proses sampai poin 3 di atas selesai, maka yang dilakukan selanjutnya adalah membentuk daftar data aktualnya sesuai pilihan kategorinya (Clean List). Oleh karena pada formula poin 2 di atas baris kosong akan ikut masuk ke dalam range, maka baris-baris kosong harus dibuang. Untuk itu, digunakan formula:

    =OFFSET(JENIS.DATA;0;0;COUNTA(JENIS.DATA);1)

    Sampai tahap ini, maka formula di atas bisa digunakan langsung pada DropDown Validation List, atau bisa juga dibuatkan nama range seperti pada tahapan pada poin 3 di atas (misalkan dengan nama JENIS.VALIDASI).

    Semoga penjelasan di atas dapat dipahami untuk diterapkan saat membuat nama range dinamis lainnya. Contoh solusi terlampir.

    Demikian.

  4. 3 minggu lalu

    terimakasih penjelasannya. Ini sangat membantu...

 

atau Mendaftar untuk ikut berdiskusi!