Data Validation dengan Kata Kunci

Assalammualaikum Pembaca yang budiman…

Pada kesempatan kali ini saya mencoba untuk berbagi tips seputar pekerjaan yg saya alami dalam keseharian, yaitu data validation yang dapat berubah-ubah sesuai kata kunci yg diketik. Ide ini bermula dari diri saya sendiri saat mencoba untuk membuat aplikasi accounting sederhana via excel, dan disaat memasukkan contoh transaksi saya rasakan perasaan yang jenuh saat harus scrolling berkali-kali untuk menemukan akun yang akan saya pilih.

Permasalahan diatas sebenarnya hal ini dapat diatasi dengan membuat combo box dengan fitur search engine seperti yang pernah saya tulis disini 

Combo Box dengan fitur Search Engine

Namun dikarenakan saya menginginkan setiap baris kolom harus bisa meng-akomodir hal tersebut ditambah keingginan saya untuk meminimalisir penggunaan VBA tercetuslah ide sesuai judul artikel kali ini, nah hasilnya adalah setiap kali saya ingin input akun misalkan akun pendapatan, saya cukup ketik kata kunci “pend” atau angka “40” kemudian data validation yg muncul adalah list yg isinya sesuai kata kunci, seperti dibawah ini.

Cuplikan data validation dengan kata kunci berupa huruf

Cuplikan data validation dengan kata kunci berupa angka

Oke jika dengan gambar diatas anda telah paham dengan maksud saya, segera kita mulai langkah-langkah untuk dapat membuat data validation dengan kata kunci.

Please note : Setting regional computer saya adalah Indonesia, sehingga character pemisah rumus yg akan saya tulis  menggunakan tanda titik koma ( ; ) Jika computer anda setting regionalnya adalah engglish maka solusinya adalah mengganti nya menjadi Indonesia, atau merubah rumus yg saya tulis disini dari karakter ( ; ) menjadi koma ( , )

Langkah 1

Buat file workbook dengan dua sheet, sheet pertama namanya sheet1 dan sheet kedua diisi dengan nama sheet2, kemudian siapkan satu set daftar akun seperti cuplikan dibawah ini

Daftar Akun

Langkah 2

Pada Kolom C di cell C1 Silahkan anda buat judul kolom yg berjudul “Gabungan”

Kemudian di cell C2 anda ketik rumus =A2&"-"&B2 kemudian copy rumus tersebut sampai batas paling bawah (sesuai dengan banyaknya daftar akun anda), berikut tampilannya

Gabungan antara nomor dan nama akun

Langkah 3

Pada Kolom D di cell D1 Silahkan anda buat judul kolom yg berjudul “bantu 1”

Kemudian di cell D2 anda ketik rumus

 =IF(ISNUMBER(IFERROR(SEARCH($G$1;C2);""))=TRUE;1;0)

kemudian copy rumus tersebut sampai batas paling bawah (sesuai dengan banyaknya daftar akun anda), adapun rumus tersebut fungsinya adalah membaca cell G1 dan mencocokkannya dengan isi dari kolom C, Jika sesuai atau mengandung kata/angka kunci maka akan mengeluarkan output angka 1 dan 0 jika tidak ada.

Langkah 4

Pada Kolom E di cell E1 Silahkan anda buat judul kolom yg berjudul “bantu 2”

Kemudian di cell E2 anda ketik rumus

=IF(D2=1;SUM($D$2:D2);"")

kemudian copy rumus tersebut sampai batas paling bawah (sesuai dengan banyaknya daftar akun anda), adapun rumus tersebut fungsinya adalah penjumlahan pada kolom D.

Langkah 5

Pada Kolom F di cell F1 Silahkan anda buat judul kolom yg berjudul “bantu 3”

Kemudian di cell F2 anda ketik rumus

=IFERROR(INDEX($C$2:$C$90;MATCH(ROWS($E$2:E2);$E$2:$E$90;0));"")

kemudian copy rumus tersebut sampai batas paling bawah (sesuai dengan banyaknya daftar akun anda), adapun rumus tersebut intinya adalah fungsi INDEX untuk menampilkan akun terpilih dimana referensi barisnya/row didapat dari penggunaan fungsi match dan rows.

Langkah 6

Pada kolom G cell G1 anda ketikkan rumus

=CELL("contents")

Fungsi rumus diatas adalah agar menampilkan isi dari cell terakhir yg kita isi, adapun trigger nya adalah enter, tab, double klik dll.

Bila sampai disini anda telah selesai seperti dibawah ini cuplikannya

Sampai disini kita harus melakukan testing pada workbook yg telah kita buat dari langkah 1 s/d 6 diatas, dengan cara mengisi cell G3 ( Cell G3 tidak bersifat tetap dan bebas, cell mana saja bisa selama masih satu workbook ) dengan kata kunci “piu” kemudian tekan enter ( kata kunci bebas terserah anda, yg penting kata tersebut ada di daftar akun tersebut, bisa penuh atau hanya sebagian)

Berikut tampilan nya jika berhasil…

Langkah 7

Langkah terakhir adalah membuat data validation yg rujukannya adalah kolom F yang telah kita beri nama kolom “bantu 3”

Silahkan buat sheet baru, kemudian masukkan judul dan isi tabel seperti dibawah ini:

Bentuk dan tabel serta isian terserah anda, namun agar mudah menjelaskan pada contoh kali ini sesuai contoh gambar diatas data validation yg akan saya buat adalah kolom E dengan judul kolom “kode Akun” dari cell E2 s/d E8

Pilih dan blok cell E2 s/d E8 kemudian klik Data validation pada tabulasi DATA, seperti contoh dibawah ini:

Akan muncul jendela baru yg bernama data validation, kemudian pada tabulasi setting pilih field Allow: menjadi “List” dan field Source: di isi dengan rumus dibawah ini

'=OFFSET(Sheet1!$F$2;0;0;COUNTA(Sheet1!$F:$F)-1)

NB : Perhatikan bahwa workbook saya hanya berisi 2 sheet saja, sheet pertama bernama sheet1 dan sheet ke dua bernama sheet 2

Setelah selesai, lanjut ke tabulasi Error Alert lalu hilangkan tanda centang pada field “Show error alert after invalid data is entered” kemudian Klik OK

Selamat,…sampai disini selesai lah pekerjaan kita, adapun untuk mengetahui berhasil atau tidaknya silahkan lakukan testing pada cell E2 s/d E8 dengan memilih salah satu cell tersebut lalu ketik kata kunci kemudian tekan enter dilanjutkan dengan memilih panah data validation.

Seperti biasa file yg telah jadi dapat anda download disini.