Laman

▼

Membuat Daftar Sheet Dengan Formula

Terkadang kita bekerja dengan menggunakan banyak Sheet, dan untuk mempermudah kita membuka sheet tertentu, akan lebih baik jika kita mempunyai Sheet Menu, dimana di dalam sheet tersebut berisikan daftar Sheet yang ada.
Proses manual tetap saja bisa dilakukan, tetapi untuk orang "malas" sudah bukan jamannya lagi pakai cara manual...

Nah bagaimana cara membuatnya ?
Ada 2 cara, yang pertama menggunakan macro, bagi macro mania pasti mudah sekali ya, cukup looping sejumlah sheet, kemudian tuliskan pada sheet Menu.
Cara kedua yang akan saya bahas disini adalah menggunakan Formula.

Caranya adalah sebagai berikut :
1. Buatlah Name dengan nama DaftarSheet, scopenya Workbook dan Formulanya adalah :
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
Syntax GET.WORKBOOK bisa anda lihat disini


2. Kemudian pada Sheet Menu, silahkan anda tuliskan formula berikut ini :
=IFERROR(INDEX(DaftarSheet,Row()),"")
Karena Formula ini menggunakan fungsi Row(), maka untuk mendapatkan nama Sheet yang pertama, formula ini harus diletakkan pada Row 1

3. Jika ingin meletakkan pada baris yang diinginkan, Row(), bisa diganti dengan cara manual, atau merujuk pada Cell tertentu, seperti pada contoh :
=IFERROR(INDEX(DaftarSheet,A5),"")
 Copy formula ini sebanyak Sheet anda.


Dengan cara ini maka anda akan mendapatkan hasil pada Kolom B
Tetapi untuk kebutuhan Menu anda, maka hal ini kurang membantu...benar ?
Untuk menambahkan link ke sheet yang lain, maka tambahkan formula :
=HYPERLINK("#" & B5 & "!A1","Go to Sheet")
 Kemudian copy formula tersebut sebanyak baris nama Sheet anda.



1 komentar:

  1. Mr. Kid31 Mei 2014 pukul 14.20

    siip...

    Sedikit urun...
    Penambahan sheet, penggantian nama sheet, pembuangan sheet, atau save workbook tidak akan secara otomatis memperbarui daftar sheet ketika formula sudah di-copy ke banyak baris melebihi jumlah sheet yang ada (misal formula sudah di-copy ke 20 baris untuk 20 sheet).

    Penggunaan fungsi volatile seperti Rand() bisa membantu pembaruan daftar sheet, setidaknya setelah user melakukan sesuatu yang memicu aktivitas fungsi volatile, seperti mengganti nama sheet, mengubah suatu nilai dalam cell, melakukan filter atau sort, insert atau delete column maupun row.

    Misal, rujukan ke A5 pada formula di cell B5 ditambahkan (rand()+1=0), maka formula akan dikalkulasi oleh Excel mengikuti 'rules' dari fungsi volatile.

    ;)
    moga-moga berguna...

    Wassalam,
    Kid.

    BalasHapus