CARA MEMBUAT DATA VALIDATION LIST EXCEL 2 KOLOM [MACRO VBA EXCEL TUTORIAL SERIES] - Belajar Microsoft Excel : Tips, Tricks & Tutorial
Iklan

CARA MEMBUAT DATA VALIDATION LIST EXCEL 2 KOLOM [MACRO VBA EXCEL TUTORIAL SERIES]

Assalamualaikum Warohmatullahi Wabarokatuh

Sodara2 sekaliYan para pembatja jang boediman … Microsoft Office Excel telah dilengkapi dengan fitur Data Validation yang cukup powerful … cara membuat Data validation di Excel sudah pernah saya bikinkan artikelnya … silahkan sOdara ceKilk disini ….. pada artikel tersebut sudah dijelaskan langkah2 menggunakan Data Validation … serta beberapa trik seputar validasi data di Excel antara lain tentang cara menggunakan rumus/formula Data Validation , cara membuat DropDown List Excel untuk beda sheet, serta validasi data custom misal untuk mencegah duplikasi data / data kembar dalam 1 kolom dengan bantuan rumus/formula COUNTIF

selain itu saya jugaK sudah pernah bikin artikel cara membuat data validasi bertingkat di excel … dimana ada dua atau lebih cell yang divalidasi dengan tipe List … dimana isi item2 pada DropDown List cell ke-2  akan bergantung pada nilai cell sebelumnya (cell ke-1) … sehinggan saat nilai cell ke-1 berubah maka item2 pada DropDown List cell ke-2 jugaK akan menyesuaikan … begitu seterusnya …. silahkan sOdara ceKilk disini

tipe Data Validation yang disediakan Excel mungkin yang paling populer dipakai oleh pengguna Excel adalah Tipe List … data yang bisa dientry pada cell Excel yang divalidasi dengan tipe ini dibatasi hanya item2 yang terdapat dalam List / Daftar saja … List bisa bersumber dari Range yang terdiri atas cell2 yang berurutan dalam 1 Baris/Row (horizontal) …. atau cell2 yang berurutan dalam 1 Kolom (Vertikal)

validasi tipe List tidak cuman bisa dipakai untuk mencegah kesalahan entry data yang tidak sesuai (tidak valid) saja … tipe validasi ini jugak mempunyai efek samping manpaat lainnya dalam membantu memudahkan sOdara dalam proses entry data … validasi tipe List akan menampilkan dropdown list yang berisi item2 yang bisa dipilih dengan cukup ceKlik saja … jadi sOdara2 sekalian ndak perlu mengetik langsung datanya dan bisa lebih memudahkan dalam proses entry data … namun sayang disayang Excel tidak menyediakan validasi yang berupa List / daftar yang bersumber dari tabel yang lebih dari 1 kolom / 1 baris … jadi topik inilah yang akan menjadi Problema de Excellente artikel ini

Sebenarnya Data Validation List bawaan Excel bisa bisa dikembangkan lagi untuk membuat validasi lebih dari 1 kolom … tentunya diperlukan sedikit “sentuhan” … dalam hal ini “sentuhan” yang diperlukan adalah dengan membuat cell bantuan atau kalau mau repot bisa menggunakan macro VBA Excel

Contoh kasus validasi List lebih dari 1 kolom bisa kita temui jika kita ingin membuat validasi dari tabel sumber yang terdiri dari 2 kolom yaitu ID dan NAMA seperti gambar dibawah ini  … Validasi Data Excel hanya bisa melakukan validasi berdasarkan kolom ID  saja  (Kolom B) atau kolom NAMA saja  (Kolom C) secara terpisah … sedangkan antara ID dan NAMA tidak boleh tertukar pasangannya … sehingga sebaiknya ID dan NAMA digabung menjadi 1 dalam sebuah List / Daftar  dan hal ini tidak bisa dilakukan dengan Validasi List standar Excel … untuk mengatasinya bisa saja dibuat Dummy Cell / Cell Bantuan yang berisi rumus / formula untuk menggabungkan 2 kolom tersebut  (Kolom E) … dan cell bantuan inilah yang nantinya digunakan sebagai sumber data validasi … seperti penampakan gambar berikut

Tabel sumber telah siap digunakan dalam validasi data kayak gambar diatas …. validasi akan diterapkan pada kolom G , I dan K

  • kolom G akan divalidasi dengan item2 ID (Kolom B)
  • kolom I divalidasi dengan item2 NAMA (Kolom C)
  • kolom K menggunakan validasi gabungan ID dan NAMA(Kolom E)

Validation List Formula dinamis

Seperti yang terlihat pada gambar … untuk sumber Listnya menggunakan Named Formula yang menggunakan fungsi OFFSETNamed Formula bisa sOdara bikin dengan ceKlik Tab Formula >> Defined Names >> Name Manager >> New … pada isian Name isi dengan “ID_LIST” (kalau ndak suka silahkan diganti sesuai selera sOdara) … dan pada Refers to isi dengan formula berikut

=OFFSET(‘Validasi List Standar’!$B$5;0;0;COUNTA(‘Validasi List Standar’!$B:$B)-1;1)

untuk membuat validasinya lakukan langkah2 berikut ini :

  • Select Range yang akan diberi validasi … contoh yang pertama untuk kolom G menempati G5:G14
  • ceKlik Tab Data >> Data Tools >> Data Validation … dialog box  Data Validation akan nongol … lakukan pengaturan seperti gambar berikut

Validation List Formula

penggunaan Named Formula sebagai source tujuannya agar tabel sumber data validasi bisa dinamis mengikuti bertambah ataupun berkurangnya item ID di kolom B … untuk tabel sumber yang statis bisa langsung tulis Range Address dari tabel sumbernya … seperti penampakan berikut

Data Validation static source

untuk kolom I validasi dipasang pada range I5:I14 … formulanya seperti gambar berikut

Data Validation Dynamic Source

dan kolom K yang akan berisi item2 gabungan ID dan NAMA … range yang akan divalidasi adalah K5:K14 formula yang digunakan kayak gambar berikut

Data Validation List 2 Column Dynamic Source

fungsi utama yang dipakai untuk menyusun validasi diatas adalah fungsi OFFSET … kalau belum jelas tetang fungsi ini silahkan baca artikelnya dengan ceKlik disini

pada kolom K validasi data List telah menampilkan item2 gabungan ID dan NAMA  … namun hasil yang diinginkan agar kolom ID dan NAMA tetap terpisah seperti pada kolom G dan I belum bisa terpenuhi … untuk memecahnya bisa menggunakan rumus / formula seperti gambar berikut

 

Jika dengan solusi diatas sOdara merasa belum cukup puas … berarti saatnya sekarang sOdara gunakan sentuhan VBA Excel untuk mencari sOlusi yang lebih repot 🙂 🙂 🙂 … cuman jangan sekarang yah … tunggu aja minggu depan untuk kelanjutan artikel ini yang akan membahas tentang cara membuat Data Validation List yang bisa menampilkan Drop Down yang berisi daftar item2 dari 2 kolom … atau pelajari secara mandiri contoh file Validasi2Kolom.xlsm … silahkan di download gratis dari folder 2017 >> 09_SEP … sekiyan semoga manpaat dan MDLMDL 🙂 😉

 

bingung cara downloadnya ??

Baca aja halaman download dengan klik icon di samping

feed , email and my social media

Iklan
8 Comments

Jika sOdara menemukan sesuatu yang bermanpaat di marih .. atau pengen kasih masukan .... silahkan kasih komennya

This site uses Akismet to reduce spam. Learn how your comment data is processed.