Membuat Data Validation List dari Sheet Lain

Seringkali kita membuat Validation List pada beberapa sheet dengan isi yang sama, tetapi pada Excel (2003) kita tidak bisa select source list pada sheet yang berbeda.

Ada 2 cara untuk membuat Data Validation List yang sourcenya berasal dari Sheet yang lain.
1. Menggunakan Define
a. Select Range yang akan dibuat List (Source)
b. Pilih Menu Insert -> Name -> Define
c. Isikan Names In Workbook, Misal : DataKu
d. Pindah ke Sheet Lain
e. Pilih Cell yang akan di buat List
f. Kemudian pilih Menu Data -> Validation
g. Pada Kolom Allow pilih List
h. Kemudian pada kolom Source isikan =DataKu

2. Menggunakan Fungsi INDIRECT
Jika kita asumsikan range A1:A10 adalah source yang terletak pada Sheet1. Maka untuk membuat Data Validation List pada Sheet2 adalah sebagai berikut :
a. Pilih Cell yang akan di buat List
b. Kemudian pilih Menu Data -> Validation
c. Pada Kolom Allow pilih List
d. Kemudian pada kolom Source isikan =INDIRECT("Sheet1!$A$1:$A$10")

Untuk Indirect terdapat kelemahan pada saat nama Sheet Source di ubah, sehingga harus mengubah Data Validation Source pada Cell yang di setting sebagai Data Validation List

Misal Nama Sheet awal adalah Sheet1 , fungsi indirectnya adalah =INDIRECT("Sheet1!$A$1:$A$10")

Pada saat nama sheet1 diubah menjadi Master , maka fungsinya harus diubah menjadi =INDIRECT("Master!$A$1:$A$10")

Semoga bermanfaat....

Fungsi INDIRECT

Fungsi INDIRCT berfungsi untuk mengubah text menjadi formula.
Sebagai contoh :
Cell A1 diisi dengan angka 5
kemudian cell A2 diisi dengan =INDIRECT("A1")
maka hasilnya adalah angka 5

Fungsi indirect juga bisa digunakan untuk lintas sheet
misal =INDIRECT("Sheet1!A1")

Kelemahan fungsi INDIRECT adalah jika digunakan cukup banyak dalam satu file excel, maka kinerja excel akan sangat lambat, hal ini disebabkan pada saat kita melakukan perhitungan di excel, maka akan dijalankan secara otomatis autocalculation.

Fungsi INDIRECT ini bisa juga disebut sebagai volatile function
Selain INDIRECT ada beberapa volatile function antara lain :
CELL() 
OFFSET()  
TODAY()  
NOW()  
INFO()  
RAND()

Untuk menghindari penurunan performance, sebaiknya seminimal mungkin menggunakan volatile function tersebut

Thanks
Ferry Setiawan

Disable Cut, Copy, Paste

Jika anda ingin data pada worksheet anda tidak bisa dicut, copy dan paste, caranya sangat mudah...
Tambahkan macro berikut pada worksheet anda :

Procedure yang pertama berguna untuk membatalkan perintah klik kanan pada mouse

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub

Procedure yang kedua berguna untuk membatalkan perintah cut atau copy

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Mudah kan ?
Semoga bermanfaat...

Fungsi MATCH dalam VLOOKUP

Seringkali dalam membuat suatu perhitungan , kita menggunakan fungsi VLOOKUP untuk mendapatkan nilai sesuai dengan lookup_value yang kita kehendaki

Syntax : VLOOKUP(lookup_value, array_date,column_index,[range_lookup])

Lookup_Value : Nilai yang akan dicari posisinya
Array_Data : Array (sekumpulan) data
Column_Index : Nomor kolom berdasarkan array_data
Range_Lookup :
-. Jika diisi dengan True, maka hasilnya akan sesuai dengan lookup_value, tetapi jika lookup_value tidak ditemukan dalam array_data maka hasilnya adalah nilai terbesar yang paling mendekati dengan lookup_value yang akan muncul
-. Jika diisi dengan False, maka hasilnya akan sesuai dengan lookup_value, tetapi jika lookup_value tidak ditemukan dalam array_data maka hasilnya adalah #N/A

Bagaimana jika dilakukan insert column pada column C ?

hasilnya adalah sebagai berikut :

Pada column Result hasilnya menjadi kacau, hal ini disebabkan karena Column_Index bersifat statis, tidak bisa mengikuti perubahan column.

Bagaimana solusinya ?

Solusinya kita bisa menggunakan Fungsi MATCH di dalam VLOOKUP

Setelah di lakukan insert Column C, hasilnya adalah sebagai berikut

Hasilnya sama dengan sebelum di lakukan insert column

Semoga bermanfaat...

Fungsi MATCH

Fungsi MATCH berfungsi untuk mendapatkan posisi suatu nilai dalam suatu array.

Syntax : MATCH(Lookup_Value, Array_Data, Match_Type)

Lookup_Value : Nilai yang akan dicari posisinya
Array_Data : Array (sekumpulan) data

Match Type :

-. Jika 1, hasilnya adalah posisi dimana Lookup_Value berada, atau posisi nilai terbesar yang paling mendekati dengan Lookup_Value, syaratnya adalah array_data harus berurutan (kecil ke besar)

-. Jika 0, hasilnya adalah posisi dimana lookup_value berada, dan datanya tidak harus berurutan

-. Jika -1, hasilnya adalah posisi dimana Lookup_Value berada, atau posisi nilai terkecil yang paling mendekati dengan Lookup_Value, syaratnya adalah array_data harus berurutan (besar ke kecil)

-. Jika tidak diisi maka secara default akan dianggap 1

Contoh

Thanks

Ferry Setiawan

Fungsi INDEX

Fungsi INDEX dalam excel dapat kita gunakan untuk mendapatkan suatu nilai dari baris dan kolom yang kita kehendaki.

ntax:

INDEX(array, row, column)












array : adalah range untuk data , dalam contoh ini adalah di column B
row : adalah baris data dari array
column : adalah kolom data dari array, dalam contoh dituliskan 1, karena array data hanya 1 kolom (perhitungan nomor kolom tidak dihitung dari kolom A, tetapi dimulai berdasarkan kolom dalam range data, dalam contoh adalah kolom B)

Mengubah Baris Menjadi Kolom

Bagaimana cara untuk mengubah tampilan row data menjadi column data ?

Ada beberapa cara :
1. Manual, yaitu dengan menggunakan copy & paste special ,aktifkan option transpose
2. Formula, misal data yang ada berada dalam range (A1:A5), kemudian akan di mengubah menjadi column data (misal range(B1:F1)), lakukan langkah langkah berikut :

• Block range(B1:F1)
• ketikkan formula "=transpose(B1:F1)"
• tahan Ctrl+Shift
• masih dalam keadaan menekan tombol Ctrl+Shift, tekan tombol enter