@D-HUNTER

mungkin yang dimaksud seperti file terlampir

@D-HUNTER ... Halo, bang @Caton n' master2 excel yang lainnya, lama tak bersua ...

Halo juga bro @D-HUNTER ... Kemana saja ... :) Terlampir alternatif solusinya.

@abdulmunibf ... cara buat nya gimana nih bang, step step nya ...

Caranya susah-susah mudah ... :D Logika dasarnya begini:

Ambil range nilai (data) dari kolom KELURAHAN (kolom C) dimana baris awal data dimulai dari baris pada kolom KECAMATAN (kolom B) yang nilainya sama dengan nilai pada sel H2, sampai dengan baris setelah baris awal pada kolom KECAMATAN (kolom B) yang nilainya tidak sama dengan nilai pada sel H2 atau sampai akhir baris data pada kolom KELURAHAN (kolom C).

Dari logika tersebut, maka yang harus dilakukan adalah menentukan range datanya. Ambil contoh untuk Kecamatan Ajung dimana range datanya adalah C2:C8. Untuk menentukan range acuan, dapat digunakan fungsi OFFSET atau fungsi INDIRECT. Dalam hal ini, akan digunakan fungsi INDIRECT. Jika fungsi INDIRECT digunakan secara langsung ke dalam DropDown List Data Validation, ± seperti berikut:

Sperti itu target yang diharapkan, namun harus berupa DropDown List dinamis. Tahapannya:

[1]. Menentukan baris awal data. Oleh karena range data berada pada kolom C, maka yang perlu dicari adalah indeks barisnya, yakni 2 dan 8. Untuk itu bisa digunakan fungsi MATCH karena bisa digunakan untuk pencarian menggunakan Wildcard. Formulanya:

= MATCH("Kecamatan " & dd.Kecamatan.A & "*", Sheet1!$B$1:$B$250, 0)
= MATCH("Kecamatan " & "Ajung" & "*", Sheet1!$B$1:$B$250, 0)
= MATCH("Kecamatan Ajung*", Sheet1!$B$1:$B$250, 0)
= 2

Pada formula tersebut, dd.Kecamatan.A merupakan nama range yang saya tetapkan ke sel H2. Jadi formula tersebut pada dasarnya sama dengan:

= MATCH("Kecamatan " & $H$2 & "*", Sheet1!$B$1:$B$250, 0)

Oleh karena formula intinya menggunakan fungsi INDIRECT, maka parameter sel atau range targetnya harus berupa notasi alamat sel atau range. Untuk baris awal data, karena berada pada kolom C, maka ditambahkan notasi "C" agar menjadi C2 seperti berikut:

= "C" & MATCH("Kecamatan " & dd.Kecamatan.A & "*", Sheet1!$B$1:$B$250, 0)
= "C2"

[2]. Menentukan baris akhir data. Formula untuk mendapatkan baris akhir data ini sedikit panjang oleh karena ada 2 kondisi yang harus diperiksa:

— Baris akhir data harus lebih besar dari baris awal data.
— Setiap nilai pada kolom B harus dimulai dengan kata Kecamatan.

Untuk menentukan data yang masuk kedua kriteria tersebut, digunakan fungsi AND (atau dengan menggunakan operasi logika yakni dengan tanda *), dengan ketentuan hasil yang diinginkan adalah indeks baris data kelurahan dari kecamatan selanjutnya dikurangi 1 baris.

Untuk kondisi pertama, yang dibandingkan adalah indeks baris aktual dengan indeks baris awal data. Formula yang digunakan adalah:

= (ROW(Sheet1!$C$1:$C$248)>MATCH("Kecamatan " & dd.Kecamatan.A & "*", Sheet1!$B$1:$B$248, 0))

Pada formula di atas, formulai:

... ROW(Sheet1!$C$1:$C$248) ...

akan menghasilkan nilai indeks baris mulai dari 1 sampai dengan 248. Nilai-nilai tersebut dibandingkan dengan hasil dari fungsi MATCH (lihat penjelasannya pada poin 1 di atas) yang bernilai 2. Maka, jika dilakukan komparasi:

= {1, 2, 3, 4, 5, ...} > 2
= {FALSE, FALSE, TRUE, TRUE, TRUE, ...}

dari hasil tersebut, yang memenuhi kondisi pertama adalah indeks ke-3 (contoh hasil kalkulasi di atas sengaja dipotong karena terlalu panjang).

Untuk kondisi kedua, yang dibandingkan adalah nilai setiap baris pada kolom KECAMATAN (kolom B) yang mengandung kata Kecamatan. Untuk ini, digunakan fungsi SEARCH. Masalahnya, fungsi SEARCH akan menghasilkan kesalahan jika teks yang dicari tidak ditemukan. Oleh karenanya, digunakan fungsi ISNUMBER untuk memastikan apakah teks ditemukan atau tidak. Formulanya:

= ISNUMBER(SEARCH("Kecamatan*", Sheet1!$B$1:$B$248))

Formula tersebut akan menghasilkan nilai TRUE jika teks ditemukan, dan FALSE jika teks tidak ditemukan. ± terlihat seperti berikut:

= {TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, ...}

dari hasil tersebut, yang memenuhi kondisi kedua adalah indeks ke-1, ke-2, ke-9 dan seterusnya (contoh hasil kalkulasi di atas sengaja dipotong karena terlalu panjang). Jika kedua kondisi komparasikan dengan logika AND, ± terlihat sebagai berikut:

= (ROW(Sheet1!$C$1:$C$248)>MATCH("Kecamatan " & dd.Kecamatan.A & "*", Sheet1!$B$1:$B$248, 0)) *
  ISNUMBER(SEARCH("Kecamatan*", Sheet1!$B$1:$B$248))
= {FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, ...} *
  {TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, ...}
= {0, 0, 0, 0, 0, 0, 0, 0, 1, 0, ...}

dari hasil tersebut, yang menjadi target adalah indeks barisnya. Oleh karena nilai dari hasil tersebut belum berupa indeks baris yang ditargetkan, maka fungsi IF digunakan sebagai jalan keluarnya. Sehingga jika nilai dari hasil tersebut adalah 1, maka yang dihasilkan adalah indeks barisnya, jika 0 (nol), maka nilai yang dikembalikan adalah kosong. Jika diformulasikan:

= IF({Hasil dari formula di atas}, ROW(Sheet1!$B$1:$B$248)-1, "")
= IF({0, 0, 0, 0, 0, 0, 0, 0, 1, 0, ...}, (1, 2, 3, 4 ,5 ,6, 7 ,8 , 9, 10, ...}-1, "")
= {"", "", "", "", "", "", "", "", 8, "", ...}

Oleh karena fungsi ROW pada formula tersebut akan menghasilkan indeks baris sesuai acuannya, maka untuk membuat hasil akhirnya berupa indeks baris data sebelum data kecamatan selanjutnya, maka hasilnya dikurangi 1. Masalah selanjutnya adalah menentukan hasil akhir berupa indeks baris yang ditargetkan tersebut. Karena hasil dari fungsi IF sudah diketahui, selanjutnya adalah mengambil nilai terkecil yang dihasilkan dari deretan nilai indeks baris tersebut:

= MIN({"", "", "", "", "", "", "", "", 8, "", ...}, 248)
= 8

Notasi 248 pada formula tersebut bertujuan untuk mengatasi masalah saat nilai acuan pada sel H2 merupakan data terakhir. Jika nilai pada sel H2 merupakan indeks terakhir, hasil dari dari fungsi IF di atas adalah Empty Value (""), yang berarti tidak ada satupun kondisi yang terpenuhi. Sehingga, dengan menentapkan nilai minimum pada indeks baris data terakhir, maka fungsi MIN tersebut akan mengembalikan nilai 248 saat nilai acuan pada sel H2 merupakan data terakhir.

Oleh karena formula intinya menggunakan fungsi INDIRECT, maka parameter sel atau range targetnya harus berupa notasi alamat sel atau range. Untuk baris akhir data ini, karena berada pada kolom C, maka ditambahkan notasi "C" agar menjadi C8. Namun, karena hasil dari tahap kedua ini akan digabungkan dengan hasil tahap pertama, maka ditambahkan notas ":" pada notasi kolom C menjadi ":C":

= ":C" & MIN(IF((ROW(Sheet1!$C$1:$C$248)>MATCH("Kecamatan " & dd.Kecamatan.A & "*", Sheet1!$B$1:$B$248, 0)) *
  ISNUMBER(SEARCH("Kecamatan*", Sheet1!$B$1:$B$248)), ROW(Sheet1!$B$1:$B$248)-1, ""), 248)

[3]. Gabungkan notasi alamat range data menjadi range target . Hasil dari poin ke-1 dan poin ke-2 jika digabungkan ± akan menghasilkan nilai berupa:

= INDIRECT("C2" & ":C8")
= INDIRECT("C2:C8")
= {"Desa Wirowongso.", "Desa Klompangan.", "Desa Mangaran.", "Desa Pancakarya.",  "Desa Sukamakmur.",
  "Desa Rowoindah.", "Desa Ajung."}

Karena hasil dari fungsi INDIRECT akan digunakan sebagai sumber data pada DropDown List Data Validation, formula seutuhnya di atas tidak dapat dimasukkan secara langsung ke dalam DropDown List Data Validation dikarenakan DropDown List Data Validation pada dasarnya tidak menerima formula yang masih membutuhkan proses kalkulasi. Oleh karena itu, harus ada penampung hasil dari formula tersebut. Dalam hal ini, digunakan nama range dd.Kelurahan.A.

Untuk DropDown dinamis menggunakan kontrol DropDown List, konsepnya hampir sama. Hanya saja untuk mendapatkan nama kecamatan yang dicari, saya gunakan nama range dd.Kecamatan.B yang pada dasarnya merupakan fungsi INDEX untuk mengambil data pada kolom E berdasarkan nilai indeks yang dihasilkan oleh kontrol DropDown List pertama.

Demikian, semoga bermanfaat dan tidak menambah bingung.