Formula umum
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Ringkasan
Untuk melarang input yang berisi salah satu dari banyak hal, Anda dapat menggunakan aturan validasi data khusus berdasarkan fungsi SEARCH.
Pada contoh yang ditampilkan, validasi data yang diterapkan ke B5: B11 adalah:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Penjelasan
Aturan validasi data dipicu saat pengguna menambahkan atau mengubah nilai sel.
Rumus ini menggunakan fungsi SEARCH untuk menguji masukan pengguna untuk setiap nilai dalam rentang bernama "daftar". Logika pencarian adalah "berisi" - ketika nilai dari "daftar" ditemukan, SEARCH mengembalikan posisi nilai sebagai angka. Jika tidak ditemukan, SEARCH mengembalikan kesalahan.
Fungsi ISNUMBER kemudian mengonversi angka menjadi TRUE dan kesalahan menjadi FALSE, dan operator negatif ganda mengubah nilai TRUE FALSE menjadi 1 dan nol. Karena rentang bernama "daftar" berisi 5 nilai, kami mendapatkan kembali 5 hasil dalam larik seperti ini:
(0; 0; 0; 0; 0)
SUMPRODUCT kemudian meringkas item dalam array dan hasilnya diuji terhadap nol. Selama semua item nol, SUMPRODUCT mengembalikan nol dan validasi berhasil. Jika SUMPRODUCT mengembalikan nomor lain (yaitu ketika item dalam "daftar" ditemukan) rumus mengembalikan FALSE dan validasi gagal.
Catatan: Referensi sel dalam rumus validasi data relatif terhadap sel kiri atas dalam rentang yang dipilih saat aturan validasi ditentukan, dalam hal ini B5.
