Rabu, 29 April 2015

[belajar-excel] Digest Number 3405

15 Messages

Digest #3405
1c
Bls: [belajar-excel] Hiden Otomatis by "Toby kenzo" tobykenzo
2
Absensi Extra by adm_padhi
3a
pertanyaan by "Mase Agus" maseaguscum
3b
Re: pertanyaan by "hendrik karnadi" hendrikkarnadi
3c
Bls: [belajar-excel] pertanyaan by "Toby kenzo" tobykenzo
4c
Re: Mengelompokkan nilai sesuai tema [1 Attachment] by "Mr. Kid" nmkid.family@ymail.com
4e
Re: Mengelompokkan nilai sesuai tema [1 Attachment] by "Mr. Kid" nmkid.family@ymail.com
5a
Rumus Hlookup by "heru mulyono"
5b
Re: Rumus Hlookup [1 Attachment] by "uharyanto" uharyanto@ymail.com

Messages

Tue Apr 28, 2015 3:08 am (PDT) . Posted by:

"Toby kenzo" tobykenzo

Dear DD, Berikut Filenya,semoga bisa membantu. TerimaKasih

Pada Selasa, 28 April 2015 15:53, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Dear BeExceller,   Bisakan sebuah ROW dan COLUMN ter"Hide" otomatis ? Ini pengembangan kasus hyperlink tadi, semoga teman-teman tidak bosan yah... Tinggal satu langkah lagi untuk menyempurnakan report terlampir, yaitu dengan meng"Hide" otomatis baris kosong di sheet "View" dan kolom yang tidak ada datanya pada saat saya klik kode subdist di sheet "List Kirim GT" apakah hal itu memungkinkan ? Mohon pencerahannya dari para master sekalian.   Salam, DD
This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual to whom it is addressed. If you have received this email in error please send it back to the person that sent it to you. Any views or opinions presented are solely those of its author and do not necessarily represent those of NABATI or any of its subsidiary companies. Unauthorized publication, use, dissemination, forwarding, printing or copying of this email and its associated attachments is strictly prohibited.
#yiv2362484861 #yiv2362484861 -- #yiv2362484861ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2362484861 #yiv2362484861ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2362484861 #yiv2362484861ygrp-mkp #yiv2362484861hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv2362484861 #yiv2362484861ygrp-mkp #yiv2362484861ads {margin-bottom:10px;}#yiv2362484861 #yiv2362484861ygrp-mkp .yiv2362484861ad {padding:0 0;}#yiv2362484861 #yiv2362484861ygrp-mkp .yiv2362484861ad p {margin:0;}#yiv2362484861 #yiv2362484861ygrp-mkp .yiv2362484861ad a {color:#0000ff;text-decoration:none;}#yiv2362484861 #yiv2362484861ygrp-sponsor #yiv2362484861ygrp-lc {font-family:Arial;}#yiv2362484861 #yiv2362484861ygrp-sponsor #yiv2362484861ygrp-lc #yiv2362484861hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2362484861 #yiv2362484861ygrp-sponsor #yiv2362484861ygrp-lc .yiv2362484861ad {margin-bottom:10px;padding:0 0;}#yiv2362484861 #yiv2362484861actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2362484861 #yiv2362484861activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2362484861 #yiv2362484861activity span {font-weight:700;}#yiv2362484861 #yiv2362484861activity span:first-child {text-transform:uppercase;}#yiv2362484861 #yiv2362484861activity span a {color:#5085b6;text-decoration:none;}#yiv2362484861 #yiv2362484861activity span span {color:#ff7900;}#yiv2362484861 #yiv2362484861activity span .yiv2362484861underline {text-decoration:underline;}#yiv2362484861 .yiv2362484861attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv2362484861 .yiv2362484861attach div a {text-decoration:none;}#yiv2362484861 .yiv2362484861attach img {border:none;padding-right:5px;}#yiv2362484861 .yiv2362484861attach label {display:block;margin-bottom:5px;}#yiv2362484861 .yiv2362484861attach label a {text-decoration:none;}#yiv2362484861 blockquote {margin:0 0 0 4px;}#yiv2362484861 .yiv2362484861bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv2362484861 .yiv2362484861bold a {text-decoration:none;}#yiv2362484861 dd.yiv2362484861last p a {font-family:Verdana;font-weight:700;}#yiv2362484861 dd.yiv2362484861last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2362484861 dd.yiv2362484861last p span.yiv2362484861yshortcuts {margin-right:0;}#yiv2362484861 div.yiv2362484861attach-table div div a {text-decoration:none;}#yiv2362484861 div.yiv2362484861attach-table {width:400px;}#yiv2362484861 div.yiv2362484861file-title a, #yiv2362484861 div.yiv2362484861file-title a:active, #yiv2362484861 div.yiv2362484861file-title a:hover, #yiv2362484861 div.yiv2362484861file-title a:visited {text-decoration:none;}#yiv2362484861 div.yiv2362484861photo-title a, #yiv2362484861 div.yiv2362484861photo-title a:active, #yiv2362484861 div.yiv2362484861photo-title a:hover, #yiv2362484861 div.yiv2362484861photo-title a:visited {text-decoration:none;}#yiv2362484861 div#yiv2362484861ygrp-mlmsg #yiv2362484861ygrp-msg p a span.yiv2362484861yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2362484861 .yiv2362484861green {color:#628c2a;}#yiv2362484861 .yiv2362484861MsoNormal {margin:0 0 0 0;}#yiv2362484861 o {font-size:0;}#yiv2362484861 #yiv2362484861photos div {float:left;width:72px;}#yiv2362484861 #yiv2362484861photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv2362484861 #yiv2362484861photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2362484861 #yiv2362484861reco-category {font-size:77%;}#yiv2362484861 #yiv2362484861reco-desc {font-size:77%;}#yiv2362484861 .yiv2362484861replbq {margin:4px;}#yiv2362484861 #yiv2362484861ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv2362484861 #yiv2362484861ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2362484861 #yiv2362484861ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2362484861 #yiv2362484861ygrp-mlmsg select, #yiv2362484861 input, #yiv2362484861 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv2362484861 #yiv2362484861ygrp-mlmsg pre, #yiv2362484861 code {font:115% monospace;}#yiv2362484861 #yiv2362484861ygrp-mlmsg * {line-height:1.22em;}#yiv2362484861 #yiv2362484861ygrp-mlmsg #yiv2362484861logo {padding-bottom:10px;}#yiv2362484861 #yiv2362484861ygrp-msg p a {font-family:Verdana;}#yiv2362484861 #yiv2362484861ygrp-msg p#yiv2362484861attach-count span {color:#1E66AE;font-weight:700;}#yiv2362484861 #yiv2362484861ygrp-reco #yiv2362484861reco-head {color:#ff7900;font-weight:700;}#yiv2362484861 #yiv2362484861ygrp-reco {margin-bottom:20px;padding:0px;}#yiv2362484861 #yiv2362484861ygrp-sponsor #yiv2362484861ov li a {font-size:130%;text-decoration:none;}#yiv2362484861 #yiv2362484861ygrp-sponsor #yiv2362484861ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv2362484861 #yiv2362484861ygrp-sponsor #yiv2362484861ov ul {margin:0;padding:0 0 0 8px;}#yiv2362484861 #yiv2362484861ygrp-text {font-family:Georgia;}#yiv2362484861 #yiv2362484861ygrp-text p {margin:0 0 1em 0;}#yiv2362484861 #yiv2362484861ygrp-text tt {font-size:120%;}#yiv2362484861 #yiv2362484861ygrp-vital ul li:last-child {border-right:none !important;}#yiv2362484861

Tue Apr 28, 2015 3:23 am (PDT) . Posted by:

"Dede"

Dear Mr Toby,

Luarrr Biasa....,

Saya mah gak pernah kepikiran klo masalah saya ini ternyata ada solusinya. Ckckckckck....

Saya haturkan banyak terima kasih Mr Toby, semoga ilmu Anda bermanfaaat dan membawa kebaikan.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:08
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Berikut Filenya,semoga bisa membantu.

TerimaKasih

Pada Selasa, 28 April 2015 15:53, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear BeExceller,

Bisakan sebuah ROW dan COLUMN ter"Hide" otomatis ?

Ini pengembangan kasus hyperlink tadi, semoga teman-teman tidak bosan yah...

Tinggal satu langkah lagi untuk menyempurnakan report terlampir, yaitu dengan meng"Hide" otomatis baris kosong di sheet "View" dan kolom yang tidak ada datanya pada saat saya klik kode subdist di sheet "List Kirim GT" apakah hal itu memungkinkan ?

Mohon pencerahannya dari para master sekalian.

Salam,

DD

_____

This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual to whom it is addressed. If you have received this email in error please send it back to the person that sent it to you. Any views or opinions presented are solely those of its author and do not necessarily represent those of NABATI or any of its subsidiary companies. Unauthorized publication, use, dissemination, forwarding, printing or copying of this email and its associated attachments is strictly prohibited.

Tue Apr 28, 2015 3:26 am (PDT) . Posted by:

"Toby kenzo" tobykenzo

Dear DD, Sama-sama...asal ada kemauan dan kerja keras, ngga ada yang ga mungkin untuk di pecahkan.. TerimaKasih.

Pada Selasa, 28 April 2015 17:23, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  DearMr Toby,   LuarrrBiasa...., Sayamah gak pernah kepikiran klo masalah saya ini ternyata ada solusinya. Ckckckckck.... Sayahaturkan banyak terima kasih Mr Toby, semoga ilmu Anda bermanfaaat dan membawakebaikan.   Salam, DD   From: Toby kenzotobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:08
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]     Dear DD,   Berikut Filenya,semoga bisa membantu.   TerimaKasih     Pada Selasa, 28 April 2015 15:53,"Dede pmaho_log4@nabatisnack.co.id [belajar-excel]"<belajar-excel@yahoogroups.com> menulis:     Dear BeExceller,   Bisakan sebuah ROW danCOLUMN ter"Hide" otomatis ? Ini pengembangan kasushyperlink tadi, semoga teman-teman tidak bosan yah... Tinggal satu langkahlagi untuk menyempurnakan report terlampir, yaitu dengan meng"Hide" otomatisbaris kosong di sheet "View" dan kolom yang tidak ada datanya pada saat sayaklik kode subdist di sheet "List Kirim GT" apakah hal itu memungkinkan ? Mohon pencerahannyadari para master sekalian.   Salam, DD   This e-mail and any files transmittedwith it are confidential and intended solely for the use of the individual towhom it is addressed. If you have received this email in error please send itback to the person that sent it to you. Any views or opinions presented aresolely those of its author and do not necessarily represent those of NABATI orany of its subsidiary companies. Unauthorized publication, use, dissemination,forwarding, printing or copying of this email and its associated attachments isstrictly prohibited.   #yiv6102240011 #yiv6102240011 -- #yiv6102240011ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6102240011 #yiv6102240011ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6102240011 #yiv6102240011ygrp-mkp #yiv6102240011hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv6102240011 #yiv6102240011ygrp-mkp #yiv6102240011ads {margin-bottom:10px;}#yiv6102240011 #yiv6102240011ygrp-mkp .yiv6102240011ad {padding:0 0;}#yiv6102240011 #yiv6102240011ygrp-mkp .yiv6102240011ad p {margin:0;}#yiv6102240011 #yiv6102240011ygrp-mkp .yiv6102240011ad a {color:#0000ff;text-decoration:none;}#yiv6102240011 #yiv6102240011ygrp-sponsor #yiv6102240011ygrp-lc {font-family:Arial;}#yiv6102240011 #yiv6102240011ygrp-sponsor #yiv6102240011ygrp-lc #yiv6102240011hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6102240011 #yiv6102240011ygrp-sponsor #yiv6102240011ygrp-lc .yiv6102240011ad {margin-bottom:10px;padding:0 0;}#yiv6102240011 #yiv6102240011actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6102240011 #yiv6102240011activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6102240011 #yiv6102240011activity span {font-weight:700;}#yiv6102240011 #yiv6102240011activity span:first-child {text-transform:uppercase;}#yiv6102240011 #yiv6102240011activity span a {color:#5085b6;text-decoration:none;}#yiv6102240011 #yiv6102240011activity span span {color:#ff7900;}#yiv6102240011 #yiv6102240011activity span .yiv6102240011underline {text-decoration:underline;}#yiv6102240011 .yiv6102240011attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv6102240011 .yiv6102240011attach div a {text-decoration:none;}#yiv6102240011 .yiv6102240011attach img {border:none;padding-right:5px;}#yiv6102240011 .yiv6102240011attach label {display:block;margin-bottom:5px;}#yiv6102240011 .yiv6102240011attach label a {text-decoration:none;}#yiv6102240011 blockquote {margin:0 0 0 4px;}#yiv6102240011 .yiv6102240011bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv6102240011 .yiv6102240011bold a {text-decoration:none;}#yiv6102240011 dd.yiv6102240011last p a {font-family:Verdana;font-weight:700;}#yiv6102240011 dd.yiv6102240011last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6102240011 dd.yiv6102240011last p span.yiv6102240011yshortcuts {margin-right:0;}#yiv6102240011 div.yiv6102240011attach-table div div a {text-decoration:none;}#yiv6102240011 div.yiv6102240011attach-table {width:400px;}#yiv6102240011 div.yiv6102240011file-title a, #yiv6102240011 div.yiv6102240011file-title a:active, #yiv6102240011 div.yiv6102240011file-title a:hover, #yiv6102240011 div.yiv6102240011file-title a:visited {text-decoration:none;}#yiv6102240011 div.yiv6102240011photo-title a, #yiv6102240011 div.yiv6102240011photo-title a:active, #yiv6102240011 div.yiv6102240011photo-title a:hover, #yiv6102240011 div.yiv6102240011photo-title a:visited {text-decoration:none;}#yiv6102240011 div#yiv6102240011ygrp-mlmsg #yiv6102240011ygrp-msg p a span.yiv6102240011yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6102240011 .yiv6102240011green {color:#628c2a;}#yiv6102240011 .yiv6102240011MsoNormal {margin:0 0 0 0;}#yiv6102240011 o {font-size:0;}#yiv6102240011 #yiv6102240011photos div {float:left;width:72px;}#yiv6102240011 #yiv6102240011photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv6102240011 #yiv6102240011photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv6102240011 #yiv6102240011reco-category {font-size:77%;}#yiv6102240011 #yiv6102240011reco-desc {font-size:77%;}#yiv6102240011 .yiv6102240011replbq {margin:4px;}#yiv6102240011 #yiv6102240011ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv6102240011 #yiv6102240011ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv6102240011 #yiv6102240011ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv6102240011 #yiv6102240011ygrp-mlmsg select, #yiv6102240011 input, #yiv6102240011 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv6102240011 #yiv6102240011ygrp-mlmsg pre, #yiv6102240011 code {font:115% monospace;}#yiv6102240011 #yiv6102240011ygrp-mlmsg * {line-height:1.22em;}#yiv6102240011 #yiv6102240011ygrp-mlmsg #yiv6102240011logo {padding-bottom:10px;}#yiv6102240011 #yiv6102240011ygrp-msg p a {font-family:Verdana;}#yiv6102240011 #yiv6102240011ygrp-msg p#yiv6102240011attach-count span {color:#1E66AE;font-weight:700;}#yiv6102240011 #yiv6102240011ygrp-reco #yiv6102240011reco-head {color:#ff7900;font-weight:700;}#yiv6102240011 #yiv6102240011ygrp-reco {margin-bottom:20px;padding:0px;}#yiv6102240011 #yiv6102240011ygrp-sponsor #yiv6102240011ov li a {font-size:130%;text-decoration:none;}#yiv6102240011 #yiv6102240011ygrp-sponsor #yiv6102240011ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv6102240011 #yiv6102240011ygrp-sponsor #yiv6102240011ov ul {margin:0;padding:0 0 0 8px;}#yiv6102240011 #yiv6102240011ygrp-text {font-family:Georgia;}#yiv6102240011 #yiv6102240011ygrp-text p {margin:0 0 1em 0;}#yiv6102240011 #yiv6102240011ygrp-text tt {font-size:120%;}#yiv6102240011 #yiv6102240011ygrp-vital ul li:last-child {border-right:none !important;}#yiv6102240011

Wed Apr 29, 2015 12:48 am (PDT) . Posted by:

"Dede"

Daear Mr Toby,

Setelah saya pakai dan sedikit di refresh data yang ada di sheet "Rekap", ternyata masih belum sesuai harapan. Mungkin sayanya kurang detail penjelasannya.

Begini rulenya Mr...,

Kita bicara kode 1000596 yah.

Syarat Kolom : 1. Jika X58>0 dan AK58>0 dan AX58>0, maka jangan ada kolom yang di hide

2. Jika X58>0 dan AK58>0 dan AX58=0, maka kolom AL s/d AX di hide

3. Jika X58>0 dan AK58=0 dan AX58=0, maka kolom Y s/d AX di hide

Syarat Baris : 1. Sembunyikan mulai di baris berapa saja tergantung dari akhir kode di kolom C sheet "View".

Dan begitu juga untuk kode 1000597 dan 1000616.

Demikian penjelasannya semoga tidak bingung dan dapat membantu kembali.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:27
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis

Dear DD,

Sama-sama...asal ada kemauan dan kerja keras, ngga ada yang ga mungkin untuk di pecahkan..

TerimaKasih.

Pada Selasa, 28 April 2015 17:23, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear Mr Toby,

Luarrr Biasa....,

Saya mah gak pernah kepikiran klo masalah saya ini ternyata ada solusinya. Ckckckckck....

Saya haturkan banyak terima kasih Mr Toby, semoga ilmu Anda bermanfaaat dan membawa kebaikan.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:08
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Berikut Filenya,semoga bisa membantu.

TerimaKasih

Pada Selasa, 28 April 2015 15:53, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear BeExceller,

Bisakan sebuah ROW dan COLUMN ter"Hide" otomatis ?

Ini pengembangan kasus hyperlink tadi, semoga teman-teman tidak bosan yah...

Tinggal satu langkah lagi untuk menyempurnakan report terlampir, yaitu dengan meng"Hide" otomatis baris kosong di sheet "View" dan kolom yang tidak ada datanya pada saat saya klik kode subdist di sheet "List Kirim GT" apakah hal itu memungkinkan ?

Mohon pencerahannya dari para master sekalian.

Salam,

DD

_____

This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual to whom it is addressed. If you have received this email in error please send it back to the person that sent it to you. Any views or opinions presented are solely those of its author and do not necessarily represent those of NABATI or any of its subsidiary companies. Unauthorized publication, use, dissemination, forwarding, printing or copying of this email and its associated attachments is strictly prohibited.

Tue Apr 28, 2015 1:08 pm (PDT) . Posted by:

adm_padhi

dh Excel,
Mohon bantuan untuk mengatasi permasalahan perihal absensi di kantor saya
Management kami akan memberlakukan permasalah absensi dengan denda sesuai dengan keterlambatan masuk kerjadi hitung per'menit bervariasi
*Soal dan denda terlampir

 Terima Kasih
Hormat Saya
CaturBP
089603973154

Tue Apr 28, 2015 2:33 pm (PDT) . Posted by:

"Mase Agus" maseaguscum

Assalamu'alaikum
Saya belajar membuat aplikasi raport sederhana yang terdiri dari : sheet 1 :menu navigasi sheet 2 : data siswa sheet 3 : daftar nilai dan setrusya keinginan saya adalah yang terlihat di tampilan adalah sheet 1 saja yang lain tidak terlihat (tersembunyi). bagaiman cara membuat aplikasi tersebut ? mohon pencerahannya terima kasih

Tue Apr 28, 2015 6:04 pm (PDT) . Posted by:

"hendrik karnadi" hendrikkarnadi

Terlampir file yang saya ambil dari buku saya.Pwdnya saya ganti : "maseagus" (tanpa apostrop).
Jangan lupa aktifkan macronya.
Pada saat file dibuka yang tampil hanya Sheet1.Jika ingin membuka Sheet lainnya maka anda harus menjalankan macro Buka dan memasukkan Pwd di atas.
Pada saat file disave/ditutup maka tampilan dikembalikan ke Sheet1.
Semoga sesuai kebutuhan.
SalamHK
From: "Mase Agus maseaguscum@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
Sent: Wednesday, 29 April 2015, 4:29
Subject: [belajar-excel] pertanyaan

  Assalamu'alaikum
Saya belajar membuat aplikasi raport sederhana yang terdiri dari : sheet 1 :menu navigasi sheet 2 : data siswa sheet 3 : daftar nilai dan setrusya keinginan saya adalah yang terlihat di tampilan adalah sheet 1 saja yang lain tidak terlihat (tersembunyi). bagaiman cara membuat aplikasi tersebut ? mohon pencerahannya terima kasih
#yiv0403279266 #yiv0403279266 -- #yiv0403279266ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0403279266 #yiv0403279266ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0403279266 #yiv0403279266ygrp-mkp #yiv0403279266hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv0403279266 #yiv0403279266ygrp-mkp #yiv0403279266ads {margin-bottom:10px;}#yiv0403279266 #yiv0403279266ygrp-mkp .yiv0403279266ad {padding:0 0;}#yiv0403279266 #yiv0403279266ygrp-mkp .yiv0403279266ad p {margin:0;}#yiv0403279266 #yiv0403279266ygrp-mkp .yiv0403279266ad a {color:#0000ff;text-decoration:none;}#yiv0403279266 #yiv0403279266ygrp-sponsor #yiv0403279266ygrp-lc {font-family:Arial;}#yiv0403279266 #yiv0403279266ygrp-sponsor #yiv0403279266ygrp-lc #yiv0403279266hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0403279266 #yiv0403279266ygrp-sponsor #yiv0403279266ygrp-lc .yiv0403279266ad {margin-bottom:10px;padding:0 0;}#yiv0403279266 #yiv0403279266actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0403279266 #yiv0403279266activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0403279266 #yiv0403279266activity span {font-weight:700;}#yiv0403279266 #yiv0403279266activity span:first-child {text-transform:uppercase;}#yiv0403279266 #yiv0403279266activity span a {color:#5085b6;text-decoration:none;}#yiv0403279266 #yiv0403279266activity span span {color:#ff7900;}#yiv0403279266 #yiv0403279266activity span .yiv0403279266underline {text-decoration:underline;}#yiv0403279266 .yiv0403279266attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv0403279266 .yiv0403279266attach div a {text-decoration:none;}#yiv0403279266 .yiv0403279266attach img {border:none;padding-right:5px;}#yiv0403279266 .yiv0403279266attach label {display:block;margin-bottom:5px;}#yiv0403279266 .yiv0403279266attach label a {text-decoration:none;}#yiv0403279266 blockquote {margin:0 0 0 4px;}#yiv0403279266 .yiv0403279266bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv0403279266 .yiv0403279266bold a {text-decoration:none;}#yiv0403279266 dd.yiv0403279266last p a {font-family:Verdana;font-weight:700;}#yiv0403279266 dd.yiv0403279266last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0403279266 dd.yiv0403279266last p span.yiv0403279266yshortcuts {margin-right:0;}#yiv0403279266 div.yiv0403279266attach-table div div a {text-decoration:none;}#yiv0403279266 div.yiv0403279266attach-table {width:400px;}#yiv0403279266 div.yiv0403279266file-title a, #yiv0403279266 div.yiv0403279266file-title a:active, #yiv0403279266 div.yiv0403279266file-title a:hover, #yiv0403279266 div.yiv0403279266file-title a:visited {text-decoration:none;}#yiv0403279266 div.yiv0403279266photo-title a, #yiv0403279266 div.yiv0403279266photo-title a:active, #yiv0403279266 div.yiv0403279266photo-title a:hover, #yiv0403279266 div.yiv0403279266photo-title a:visited {text-decoration:none;}#yiv0403279266 div#yiv0403279266ygrp-mlmsg #yiv0403279266ygrp-msg p a span.yiv0403279266yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0403279266 .yiv0403279266green {color:#628c2a;}#yiv0403279266 .yiv0403279266MsoNormal {margin:0 0 0 0;}#yiv0403279266 o {font-size:0;}#yiv0403279266 #yiv0403279266photos div {float:left;width:72px;}#yiv0403279266 #yiv0403279266photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv0403279266 #yiv0403279266photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0403279266 #yiv0403279266reco-category {font-size:77%;}#yiv0403279266 #yiv0403279266reco-desc {font-size:77%;}#yiv0403279266 .yiv0403279266replbq {margin:4px;}#yiv0403279266 #yiv0403279266ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv0403279266 #yiv0403279266ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0403279266 #yiv0403279266ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0403279266 #yiv0403279266ygrp-mlmsg select, #yiv0403279266 input, #yiv0403279266 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv0403279266 #yiv0403279266ygrp-mlmsg pre, #yiv0403279266 code {font:115% monospace;}#yiv0403279266 #yiv0403279266ygrp-mlmsg * {line-height:1.22em;}#yiv0403279266 #yiv0403279266ygrp-mlmsg #yiv0403279266logo {padding-bottom:10px;}#yiv0403279266 #yiv0403279266ygrp-msg p a {font-family:Verdana;}#yiv0403279266 #yiv0403279266ygrp-msg p#yiv0403279266attach-count span {color:#1E66AE;font-weight:700;}#yiv0403279266 #yiv0403279266ygrp-reco #yiv0403279266reco-head {color:#ff7900;font-weight:700;}#yiv0403279266 #yiv0403279266ygrp-reco {margin-bottom:20px;padding:0px;}#yiv0403279266 #yiv0403279266ygrp-sponsor #yiv0403279266ov li a {font-size:130%;text-decoration:none;}#yiv0403279266 #yiv0403279266ygrp-sponsor #yiv0403279266ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv0403279266 #yiv0403279266ygrp-sponsor #yiv0403279266ov ul {margin:0;padding:0 0 0 8px;}#yiv0403279266 #yiv0403279266ygrp-text {font-family:Georgia;}#yiv0403279266 #yiv0403279266ygrp-text p {margin:0 0 1em 0;}#yiv0403279266 #yiv0403279266ygrp-text tt {font-size:120%;}#yiv0403279266 #yiv0403279266ygrp-vital ul li:last-child {border-right:none !important;}#yiv0403279266

Tue Apr 28, 2015 11:37 pm (PDT) . Posted by:

"Toby kenzo" tobykenzo

Dear Agus,  Mungkin file ini bisa membantu,file ini menggunakan VBA. TerimaKasih.

Pada Rabu, 29 April 2015 4:33, "Mase Agus maseaguscum@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Assalamu'alaikum
Saya belajar membuat aplikasi raport sederhana yang terdiri dari : sheet 1 :menu navigasi sheet 2 : data siswa sheet 3 : daftar nilai dan setrusya keinginan saya adalah yang terlihat di tampilan adalah sheet 1 saja yang lain tidak terlihat (tersembunyi). bagaiman cara membuat aplikasi tersebut ? mohon pencerahannya terima kasih
#yiv3380786204 #yiv3380786204 -- #yiv3380786204ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3380786204 #yiv3380786204ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3380786204 #yiv3380786204ygrp-mkp #yiv3380786204hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3380786204 #yiv3380786204ygrp-mkp #yiv3380786204ads {margin-bottom:10px;}#yiv3380786204 #yiv3380786204ygrp-mkp .yiv3380786204ad {padding:0 0;}#yiv3380786204 #yiv3380786204ygrp-mkp .yiv3380786204ad p {margin:0;}#yiv3380786204 #yiv3380786204ygrp-mkp .yiv3380786204ad a {color:#0000ff;text-decoration:none;}#yiv3380786204 #yiv3380786204ygrp-sponsor #yiv3380786204ygrp-lc {font-family:Arial;}#yiv3380786204 #yiv3380786204ygrp-sponsor #yiv3380786204ygrp-lc #yiv3380786204hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3380786204 #yiv3380786204ygrp-sponsor #yiv3380786204ygrp-lc .yiv3380786204ad {margin-bottom:10px;padding:0 0;}#yiv3380786204 #yiv3380786204actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3380786204 #yiv3380786204activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3380786204 #yiv3380786204activity span {font-weight:700;}#yiv3380786204 #yiv3380786204activity span:first-child {text-transform:uppercase;}#yiv3380786204 #yiv3380786204activity span a {color:#5085b6;text-decoration:none;}#yiv3380786204 #yiv3380786204activity span span {color:#ff7900;}#yiv3380786204 #yiv3380786204activity span .yiv3380786204underline {text-decoration:underline;}#yiv3380786204 .yiv3380786204attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3380786204 .yiv3380786204attach div a {text-decoration:none;}#yiv3380786204 .yiv3380786204attach img {border:none;padding-right:5px;}#yiv3380786204 .yiv3380786204attach label {display:block;margin-bottom:5px;}#yiv3380786204 .yiv3380786204attach label a {text-decoration:none;}#yiv3380786204 blockquote {margin:0 0 0 4px;}#yiv3380786204 .yiv3380786204bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3380786204 .yiv3380786204bold a {text-decoration:none;}#yiv3380786204 dd.yiv3380786204last p a {font-family:Verdana;font-weight:700;}#yiv3380786204 dd.yiv3380786204last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3380786204 dd.yiv3380786204last p span.yiv3380786204yshortcuts {margin-right:0;}#yiv3380786204 div.yiv3380786204attach-table div div a {text-decoration:none;}#yiv3380786204 div.yiv3380786204attach-table {width:400px;}#yiv3380786204 div.yiv3380786204file-title a, #yiv3380786204 div.yiv3380786204file-title a:active, #yiv3380786204 div.yiv3380786204file-title a:hover, #yiv3380786204 div.yiv3380786204file-title a:visited {text-decoration:none;}#yiv3380786204 div.yiv3380786204photo-title a, #yiv3380786204 div.yiv3380786204photo-title a:active, #yiv3380786204 div.yiv3380786204photo-title a:hover, #yiv3380786204 div.yiv3380786204photo-title a:visited {text-decoration:none;}#yiv3380786204 div#yiv3380786204ygrp-mlmsg #yiv3380786204ygrp-msg p a span.yiv3380786204yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3380786204 .yiv3380786204green {color:#628c2a;}#yiv3380786204 .yiv3380786204MsoNormal {margin:0 0 0 0;}#yiv3380786204 o {font-size:0;}#yiv3380786204 #yiv3380786204photos div {float:left;width:72px;}#yiv3380786204 #yiv3380786204photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv3380786204 #yiv3380786204photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3380786204 #yiv3380786204reco-category {font-size:77%;}#yiv3380786204 #yiv3380786204reco-desc {font-size:77%;}#yiv3380786204 .yiv3380786204replbq {margin:4px;}#yiv3380786204 #yiv3380786204ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3380786204 #yiv3380786204ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3380786204 #yiv3380786204ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3380786204 #yiv3380786204ygrp-mlmsg select, #yiv3380786204 input, #yiv3380786204 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3380786204 #yiv3380786204ygrp-mlmsg pre, #yiv3380786204 code {font:115% monospace;}#yiv3380786204 #yiv3380786204ygrp-mlmsg * {line-height:1.22em;}#yiv3380786204 #yiv3380786204ygrp-mlmsg #yiv3380786204logo {padding-bottom:10px;}#yiv3380786204 #yiv3380786204ygrp-msg p a {font-family:Verdana;}#yiv3380786204 #yiv3380786204ygrp-msg p#yiv3380786204attach-count span {color:#1E66AE;font-weight:700;}#yiv3380786204 #yiv3380786204ygrp-reco #yiv3380786204reco-head {color:#ff7900;font-weight:700;}#yiv3380786204 #yiv3380786204ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3380786204 #yiv3380786204ygrp-sponsor #yiv3380786204ov li a {font-size:130%;text-decoration:none;}#yiv3380786204 #yiv3380786204ygrp-sponsor #yiv3380786204ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3380786204 #yiv3380786204ygrp-sponsor #yiv3380786204ov ul {margin:0;padding:0 0 0 8px;}#yiv3380786204 #yiv3380786204ygrp-text {font-family:Georgia;}#yiv3380786204 #yiv3380786204ygrp-text p {margin:0 0 1em 0;}#yiv3380786204 #yiv3380786204ygrp-text tt {font-size:120%;}#yiv3380786204 #yiv3380786204ygrp-vital ul li:last-child {border-right:none !important;}#yiv3380786204

Tue Apr 28, 2015 3:57 pm (PDT) . Posted by:

"Fatimah Nazmi"

Assalamu'alaikum Warahmatullahi Wabarakatuh
Tolong pencerahan dari rekan-rekan milis, bagaimana rumus agar nilai
dapat dikelompokkan sesuai tema seperti file terlampir. terima
kasih......

Tue Apr 28, 2015 5:26 pm (PDT) . Posted by:

"Winov X" winovx

sambil menunggu pak master,bisa dilihat versi cupu dari saya
pake row bantu

 
__Winarno__

Pada Selasa, 28 April 2015 15:58, "Fatimah Nazmi fatimahnazmy@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Assalamu'alaikum Warahmatullahi Wabarakatuh
Tolong pencerahan dari rekan-rekan milis, bagaimana rumus agar nilai
dapat dikelompokkan sesuai tema seperti file terlampir. terima
kasih......
#yiv3961091502 #yiv3961091502 -- #yiv3961091502ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3961091502 #yiv3961091502ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3961091502 #yiv3961091502ygrp-mkp #yiv3961091502hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3961091502 #yiv3961091502ygrp-mkp #yiv3961091502ads {margin-bottom:10px;}#yiv3961091502 #yiv3961091502ygrp-mkp .yiv3961091502ad {padding:0 0;}#yiv3961091502 #yiv3961091502ygrp-mkp .yiv3961091502ad p {margin:0;}#yiv3961091502 #yiv3961091502ygrp-mkp .yiv3961091502ad a {color:#0000ff;text-decoration:none;}#yiv3961091502 #yiv3961091502ygrp-sponsor #yiv3961091502ygrp-lc {font-family:Arial;}#yiv3961091502 #yiv3961091502ygrp-sponsor #yiv3961091502ygrp-lc #yiv3961091502hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3961091502 #yiv3961091502ygrp-sponsor #yiv3961091502ygrp-lc .yiv3961091502ad {margin-bottom:10px;padding:0 0;}#yiv3961091502 #yiv3961091502actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3961091502 #yiv3961091502activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3961091502 #yiv3961091502activity span {font-weight:700;}#yiv3961091502 #yiv3961091502activity span:first-child {text-transform:uppercase;}#yiv3961091502 #yiv3961091502activity span a {color:#5085b6;text-decoration:none;}#yiv3961091502 #yiv3961091502activity span span {color:#ff7900;}#yiv3961091502 #yiv3961091502activity span .yiv3961091502underline {text-decoration:underline;}#yiv3961091502 .yiv3961091502attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3961091502 .yiv3961091502attach div a {text-decoration:none;}#yiv3961091502 .yiv3961091502attach img {border:none;padding-right:5px;}#yiv3961091502 .yiv3961091502attach label {display:block;margin-bottom:5px;}#yiv3961091502 .yiv3961091502attach label a {text-decoration:none;}#yiv3961091502 blockquote {margin:0 0 0 4px;}#yiv3961091502 .yiv3961091502bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3961091502 .yiv3961091502bold a {text-decoration:none;}#yiv3961091502 dd.yiv3961091502last p a {font-family:Verdana;font-weight:700;}#yiv3961091502 dd.yiv3961091502last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3961091502 dd.yiv3961091502last p span.yiv3961091502yshortcuts {margin-right:0;}#yiv3961091502 div.yiv3961091502attach-table div div a {text-decoration:none;}#yiv3961091502 div.yiv3961091502attach-table {width:400px;}#yiv3961091502 div.yiv3961091502file-title a, #yiv3961091502 div.yiv3961091502file-title a:active, #yiv3961091502 div.yiv3961091502file-title a:hover, #yiv3961091502 div.yiv3961091502file-title a:visited {text-decoration:none;}#yiv3961091502 div.yiv3961091502photo-title a, #yiv3961091502 div.yiv3961091502photo-title a:active, #yiv3961091502 div.yiv3961091502photo-title a:hover, #yiv3961091502 div.yiv3961091502photo-title a:visited {text-decoration:none;}#yiv3961091502 div#yiv3961091502ygrp-mlmsg #yiv3961091502ygrp-msg p a span.yiv3961091502yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3961091502 .yiv3961091502green {color:#628c2a;}#yiv3961091502 .yiv3961091502MsoNormal {margin:0 0 0 0;}#yiv3961091502 o {font-size:0;}#yiv3961091502 #yiv3961091502photos div {float:left;width:72px;}#yiv3961091502 #yiv3961091502photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv3961091502 #yiv3961091502photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3961091502 #yiv3961091502reco-category {font-size:77%;}#yiv3961091502 #yiv3961091502reco-desc {font-size:77%;}#yiv3961091502 .yiv3961091502replbq {margin:4px;}#yiv3961091502 #yiv3961091502ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3961091502 #yiv3961091502ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3961091502 #yiv3961091502ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3961091502 #yiv3961091502ygrp-mlmsg select, #yiv3961091502 input, #yiv3961091502 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3961091502 #yiv3961091502ygrp-mlmsg pre, #yiv3961091502 code {font:115% monospace;}#yiv3961091502 #yiv3961091502ygrp-mlmsg * {line-height:1.22em;}#yiv3961091502 #yiv3961091502ygrp-mlmsg #yiv3961091502logo {padding-bottom:10px;}#yiv3961091502 #yiv3961091502ygrp-msg p a {font-family:Verdana;}#yiv3961091502 #yiv3961091502ygrp-msg p#yiv3961091502attach-count span {color:#1E66AE;font-weight:700;}#yiv3961091502 #yiv3961091502ygrp-reco #yiv3961091502reco-head {color:#ff7900;font-weight:700;}#yiv3961091502 #yiv3961091502ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3961091502 #yiv3961091502ygrp-sponsor #yiv3961091502ov li a {font-size:130%;text-decoration:none;}#yiv3961091502 #yiv3961091502ygrp-sponsor #yiv3961091502ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3961091502 #yiv3961091502ygrp-sponsor #yiv3961091502ov ul {margin:0;padding:0 0 0 8px;}#yiv3961091502 #yiv3961091502ygrp-text {font-family:Georgia;}#yiv3961091502 #yiv3961091502ygrp-text p {margin:0 0 1em 0;}#yiv3961091502 #yiv3961091502ygrp-text tt {font-size:120%;}#yiv3961091502 #yiv3961091502ygrp-vital ul li:last-child {border-right:none !important;}#yiv3961091502

Tue Apr 28, 2015 6:45 pm (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Wa'alaikumussalam wr wb

Asumsi : jumlah itam per tema dan jumlah temanya tetap.

Coba pada cell hasil pertama :
=INDEX($C4:$AT4,MOD(COLUMNS($AW$2:AW$2)-1,4)*11+COUNTA($AW$2:AW$2))
Copy formula ke kanan dan kebawah.

*** jika regional setting komputer setempat adalah Indonesian, maka ganti
seluruh karakter koma dalam formula menjadi titik koma.
*** syarat dan ketentuan berlaku

Wassalamu'alaikum wr wb
Kid.

2015-04-29 8:40 GMT+10:00 Fatimah Nazmi fatimahnazmy@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Assalamu'alaikum Warahmatullahi Wabarakatuh
> Tolong pencerahan dari rekan-rekan milis, bagaimana rumus agar nilai
> dapat dikelompokkan sesuai tema seperti file terlampir. terima
> kasih......
>
>

Tue Apr 28, 2015 9:52 pm (PDT) . Posted by:

"Fatimah Nazmi"

Terima Kasih kepada mas winarno yang telah memberi solusi rumus serta
kolom bantunya, jadi terinspirasi versi cupu yang lain pake kolom
bantu juga.....
Mr Kid ini, rumusnya bikin deg-degan......
hasil qiroah sementara rumus Mr.... di rumus ada :
-1 trus
,4 --- jumlah kolom mapel pada tabel 2 yang di merge sel
*11 --- jumlah kolom mapel pada tabel 1 trus
ikut-ikutan lagi COUNTA ......
Mr tolong jelasin rumusnya dong...... biar bisa tidur nyenyak malam
ni... he... he...

Tue Apr 28, 2015 10:24 pm (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

e...
formula :
=INDEX($C4:$AT4,MOD(COLUMNS($AW$2:AW$2)-1,4)*11+COUNTA($AW$2:AW$2))

area data yang diambil : $C4:$AT4 -> 1 baris sajah... jadi struktur index
yang dipakai Index( area , nomor_item )
nomor_item yang akan diambil mengikuti pola :
setiap blok berisi 4 item. Item pertama setiap blok ada diposisi nomor
bloknya.
Setiap blok, ambil 4 item dengan selisih jarak per 11 titik data sebaris
Tersusunlah pola :
*1,12, 23, 34* , *2, 13, 24, 35*, *dst*

Pola ini dibentuk oleh bagian :
MOD(COLUMNS($AW$2:AW$2)-1,4)*11+COUNTA($AW$2:AW$2)
dengan
COUNTA($AW$2:AW$2) adalah nomor blok yang bisa diwakili jumlah cell berisi
data pada baris data header dengan cells ter-merge, yang akan membentuk
larik 1,1,1,1,2,2,2,2,dst (mengikuti pola di atas)

Pola *1,12, 23, 34* , *2, 13, 24, 35*,
*dst*
hasil nomor blok oleh counta 1, 1, 1, 1, 2, 2, 2, 2,
blok_berikutnya
perlu dilengkapi dengan pola 0, 11,22,33, 0, 11, 22, 33,
dst-blok_berikutnya
yang disederhanakan : 11 * (0, 1, 2 , 3 , 0 , 1 , 2 , 3,
dst-blok_berikutnya/11)

berhubung kolom hasil dimulai dari kolom hasil ke-1,ke-2,dst, berarti ada
suatu deret bilangan bulat :
1 , 2, 3, 4, 5 , 6, 7 , 8
, kolomnya
agar susunan nomor kolom menjadi pola 0,1,2,3,0,1,2,3,dst maka nomor
kolomnya dikurangi 1 (-1) agar mulai dari 0
menjadi : 0 , 1, 2, 3 , 4 , 5 , 6 ,7 ,
kolomnya-1

agar susunan kolomnya-1 bisa berubah menjadi pola 0,1,2,3,0,1,2,3,dst maka
diperlukan hasil sisa bagi dengan 4 (jumlah item per blok), sehingga
tersusun :
0/4 sisa 0 , 1,/4 sisa 1, 2/4 sisa 2, 3/4 sisa 3 , 4/4
sisa 0 , 5/4 sisa 1 , 6/4 sisa 2 ,7/4 sisa 3 , (kolomnya-1)/4 sisa n
yang menghasilkan susunan sisa bagi :
0, 1, 2, 3 , 0 , 1, 2, 3, dst

Bagian :
MOD(COLUMNS($AW$2:AW$2)-1,4)*11 adalah item yang akan diambil
COLUMNS($AW$2:AW$2) -> menghasilkan larik nomor kolom 1,2,3,4,5,dst
-1 -> membuat larik nomor kolom agar dimulai dari 0
Mod( larik_nomor_kolom - 1 , 4 ) menghasilkan sisa bagi dengan 4
* 11 karena 11 * (0, 1, 2 , 3 , 0 , 1 , 2 , 3, dst-blok_berikutnya/11)

Hasil bagian Mod ditambah dengan COunta menghasilkan pola :
*1,12, 23, 34* , *2, 13, 24, 35*, *dst*

Gitu ya....

Wassalam,
Kid

2015-04-29 13:59 GMT+10:00 Fatimah Nazmi fatimahnazmy@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Terima Kasih kepada mas winarno yang telah memberi solusi rumus serta
> kolom bantunya, jadi terinspirasi versi cupu yang lain pake kolom
> bantu juga.....
> Mr Kid ini, rumusnya bikin deg-degan......
> hasil qiroah sementara rumus Mr.... di rumus ada :
> -1 trus
> ,4 --- jumlah kolom mapel pada tabel 2 yang di merge sel
> *11 --- jumlah kolom mapel pada tabel 1 trus
> ikut-ikutan lagi COUNTA ......
> Mr tolong jelasin rumusnya dong...... biar bisa tidur nyenyak malam
> ni... he... he...
>
>

Tue Apr 28, 2015 9:53 pm (PDT) . Posted by:

"heru mulyono"

Yth, Be-Exceller

Mohon bantuan masalah rumus Hlookup, file terlampir
Terimakasih

Tue Apr 28, 2015 10:08 pm (PDT) . Posted by:

"uharyanto" uharyanto@ymail.com

Coba pake ini =INDEX($I$6:$M$6,MATCH(O7,$I7:$M7,0))

tks

Salam,
uharyanto

On 29/04/2015 11:06 AM, heru mulyono hmulyono24@gmail.com
[belajar-excel] wrote:
> Yth, Be-Exceller
>
> Mohon bantuan masalah rumus Hlookup, file terlampir
> Terimakasih
>

============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com

posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com

berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com

keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Tidak ada komentar:

Poskan Komentar