-------------------------------------------- On Sun, 9/14/14, 'Bagus' [email protected] [belajar-excel] <[email protected]> wrote:
Subject: Re: [belajar-excel] rekap bon 3 kriteria [1 Attachment] To: [email protected] Date: Sunday, September 14, 2014, 6:57 PM terima kasih mas atas formulanya, sangat menambah wawasan, saya makin senang belajar rumus-rumus excel. untuk sementara ide pengembangan versi 3 masih di coba-coba sambil mempelajari rumus mas bagus, ntar kalau uda mentok baru bertanya lagi...... Mbak Fat; Sila lihat file terlampir.. oia, helper ada dikolom A Wassalam ~ Bagus ~ ----- Original Message ----- From: fatimah nazmy [email protected] [belajar-excel] To: [email protected] Sent: Saturday, September 13, 2014 4:19 PM Subject: Re: [belajar-excel] rekap bon 3 kriteria [1 Attachment] terimah kasih mas atas solusi rumusnya. saya jadi mengetahui ada rumus seperti itu. mohon bantuannya lagi, dari rumus tersebut saya ingin mengembangkan laporan itu lagi, semula saya ingin setelah pertanyaan saya kemarin terjawab saya ingin membuat tabel bantu untuk mengambil seluruh data rekap nama bahan, banyaknya, total dan jumlah menjadi tidak ada yang duplikat. seperti solusi dari mas bagus dimana nama bahan, banyaknya dan total sudah sesuai keinginan. lalu saya coba utak atik rumus mas bagus mungkin bisa kalau langsung membuat nama bahan dan kolom lainnya tidak ada yang duplikat. namun saya menemui kendala, mohon bimbingannya lagi. file terlampir On Friday, September 12, 2014 1:57 AM, "'Bagus' [email protected] [belajar-excel]" <[email protected]> wrote: Wa'alaykum salam warohmatullohi wabarokatuhu.. Mbak Fat; Coba begini, Array formula ya: =IFERROR(INDEX(master!C$2:C$428,SMALL(IF(TEXT(master!$B$2:$B$428,"[$-421]mmmm")=$C$3,ROW(master!$B$2:$B$428)-ROW(master!$B$1)),ROW(1:1))),"") Copas kekanan dan kebawah. Nb. Pada kolom total, ganti rujukan Indexnya dari INDEX(master!F$2:F$428, menjadi INDEX(master!G$2:G$428, Wassalam ~ Bagus ~ ----- Original Message ----- From: fatimah nazmy [email protected] [belajar-excel] To: [email protected] Sent: Friday, September 12, 2014 2:59 PM Subject: [belajar-excel] rekap bon 3 kriteria [1 Attachment] Assalamu 'alaikum Wr.Wb Salam kenal semuanya rekan2 milis belajar excel. saya pendatang baru ingin bantuan dari para rekan2. 1. di kolom nama bahan otomatis muncul nama-nama bahan yang dimasukkan di sheet master dan sesuai bulan yang di pilih2. begitu juga di kolom banyaknya, dan total diambil dari banyaknya dan total di sheet master dengan nama bahan serta bulan yang di tentukan. file terlampir. #yiv8698673319 #yiv8698673319 -- #yiv8698673319ygrp-mkp { border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;} #yiv8698673319 #yiv8698673319ygrp-mkp hr { border:1px solid #d8d8d8;} #yiv8698673319 #yiv8698673319ygrp-mkp #yiv8698673319hd { color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;} #yiv8698673319 #yiv8698673319ygrp-mkp #yiv8698673319ads { margin-bottom:10px;} #yiv8698673319 #yiv8698673319ygrp-mkp .yiv8698673319ad { padding:0 0;} #yiv8698673319 #yiv8698673319ygrp-mkp .yiv8698673319ad p { margin:0;} #yiv8698673319 #yiv8698673319ygrp-mkp .yiv8698673319ad a { color:#0000ff;text-decoration:none;} #yiv8698673319 #yiv8698673319ygrp-sponsor #yiv8698673319ygrp-lc { font-family:Arial;} #yiv8698673319 #yiv8698673319ygrp-sponsor #yiv8698673319ygrp-lc #yiv8698673319hd { margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;} #yiv8698673319 #yiv8698673319ygrp-sponsor #yiv8698673319ygrp-lc .yiv8698673319ad { margin-bottom:10px;padding:0 0;} #yiv8698673319 #yiv8698673319actions { font-family:Verdana;font-size:11px;padding:10px 0;} #yiv8698673319 #yiv8698673319activity { background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;} #yiv8698673319 #yiv8698673319activity span { font-weight:700;} #yiv8698673319 #yiv8698673319activity span:first-child { text-transform:uppercase;} #yiv8698673319 #yiv8698673319activity span a { color:#5085b6;text-decoration:none;} #yiv8698673319 #yiv8698673319activity span span { color:#ff7900;} #yiv8698673319 #yiv8698673319activity span .yiv8698673319underline { text-decoration:underline;} #yiv8698673319 .yiv8698673319attach { clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;} #yiv8698673319 .yiv8698673319attach div a { text-decoration:none;} #yiv8698673319 .yiv8698673319attach img { border:none;padding-right:5px;} #yiv8698673319 .yiv8698673319attach label { display:block;margin-bottom:5px;} #yiv8698673319 .yiv8698673319attach label a { text-decoration:none;} #yiv8698673319 blockquote { margin:0 0 0 4px;} #yiv8698673319 .yiv8698673319bold { font-family:Arial;font-size:13px;font-weight:700;} #yiv8698673319 .yiv8698673319bold a { text-decoration:none;} #yiv8698673319 dd.yiv8698673319last p a { font-family:Verdana;font-weight:700;} #yiv8698673319 dd.yiv8698673319last p span { margin-right:10px;font-family:Verdana;font-weight:700;} #yiv8698673319 dd.yiv8698673319last p span.yiv8698673319yshortcuts { margin-right:0;} #yiv8698673319 div.yiv8698673319attach-table div div a { text-decoration:none;} #yiv8698673319 div.yiv8698673319attach-table { width:400px;} #yiv8698673319 div.yiv8698673319file-title a, #yiv8698673319 div.yiv8698673319file-title a:active, #yiv8698673319 div.yiv8698673319file-title a:hover, #yiv8698673319 div.yiv8698673319file-title a:visited { text-decoration:none;} #yiv8698673319 div.yiv8698673319photo-title a, #yiv8698673319 div.yiv8698673319photo-title a:active, #yiv8698673319 div.yiv8698673319photo-title a:hover, #yiv8698673319 div.yiv8698673319photo-title a:visited { text-decoration:none;} #yiv8698673319 div#yiv8698673319ygrp-mlmsg #yiv8698673319ygrp-msg p a span.yiv8698673319yshortcuts { font-family:Verdana;font-size:10px;font-weight:normal;} #yiv8698673319 .yiv8698673319green { color:#628c2a;} #yiv8698673319 .yiv8698673319MsoNormal { margin:0 0 0 0;} #yiv8698673319 o { font-size:0;} #yiv8698673319 #yiv8698673319photos div { float:left;width:72px;} #yiv8698673319 #yiv8698673319photos div div { border:1px solid #666666;height:62px;overflow:hidden;width:62px;} #yiv8698673319 #yiv8698673319photos div label { color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;} #yiv8698673319 #yiv8698673319reco-category { font-size:77%;} #yiv8698673319 #yiv8698673319reco-desc { font-size:77%;} #yiv8698673319 .yiv8698673319replbq { margin:4px;} #yiv8698673319 #yiv8698673319ygrp-actbar div a:first-child { margin-right:2px;padding-right:5px;} #yiv8698673319 #yiv8698673319ygrp-mlmsg { font-size:13px;font-family:Arial, helvetica, clean, sans-serif;} #yiv8698673319 #yiv8698673319ygrp-mlmsg table { font-size:inherit;font:100%;} #yiv8698673319 #yiv8698673319ygrp-mlmsg select, #yiv8698673319 input, #yiv8698673319 textarea { font:99% Arial, Helvetica, clean, sans-serif;} #yiv8698673319 #yiv8698673319ygrp-mlmsg pre, #yiv8698673319 code { font:115% monospace;} #yiv8698673319 #yiv8698673319ygrp-mlmsg * { line-height:1.22em;} #yiv8698673319 #yiv8698673319ygrp-mlmsg #yiv8698673319logo { padding-bottom:10px;} #yiv8698673319 #yiv8698673319ygrp-msg p a { font-family:Verdana;} #yiv8698673319 #yiv8698673319ygrp-msg p#yiv8698673319attach-count span { color:#1E66AE;font-weight:700;} #yiv8698673319 #yiv8698673319ygrp-reco #yiv8698673319reco-head { color:#ff7900;font-weight:700;} #yiv8698673319 #yiv8698673319ygrp-reco { margin-bottom:20px;padding:0px;} #yiv8698673319 #yiv8698673319ygrp-sponsor #yiv8698673319ov li a { font-size:130%;text-decoration:none;} #yiv8698673319 #yiv8698673319ygrp-sponsor #yiv8698673319ov li { font-size:77%;list-style-type:square;padding:6px 0;} #yiv8698673319 #yiv8698673319ygrp-sponsor #yiv8698673319ov ul { margin:0;padding:0 0 0 8px;} #yiv8698673319 #yiv8698673319ygrp-text { font-family:Georgia;} #yiv8698673319 #yiv8698673319ygrp-text p { margin:0 0 1em 0;} #yiv8698673319 #yiv8698673319ygrp-text tt { font-size:120%;} #yiv8698673319 #yiv8698673319ygrp-vital ul li:last-child { border-right:none !important; } #yiv8698673319
