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

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

7 Comments

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