Terimakasih Banyak Pak Odong
Pada Rabu, 28 Oktober 2015 17:55, "odong nando odongna...@yahoo.com
[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
yoo...hairul,bisa dicoba di cell
:G3=IF($F$20="";MID(INDIRECT("C"&COLUMNS($F$2:F2)+2);9;10);IF(($F$20<>"")*(COUNTIF($B$3:$B$2528;$F$20)>=COLUMNS($F$2:F2));MID(INDEX($C$3:$C$2528;MATCH($F$20;$B$3:$B$2528;0)+COLUMNS($F$2:F2)-1);9;10);""))
G4=IF($F$20="";IF(INDIRECT("D"&COLUMNS($F$2:F2)+2)=0;"";1);IF(($F$20<>"")*(COUNTIF($B$3:$B$2528;$F$20)>=COLUMNS($F$2:F2));IF(INDEX($D$3:$D$2528;MATCH($F$20;$B$3:$B$2528;0)+COLUMNS($F$2:F2)-1);1;"");""))
G6=IF($F$20="";MID(INDIRECT("C"&COLUMNS($F$2:F2)+64);9;10);IF(($F$20<>"")*(COUNTIF($B$3:$B$2528;$F$20)>=COLUMNS($F$2:F2)+62);MID(INDEX($C$3:$C$2528;MATCH($F$20;$B$3:$B$2528;0)+COLUMNS($F$2:F2)+61);9;10);""))
G7=IF($F$20="";IF(INDIRECT("D"&COLUMNS($F$2:F2)+64)=0;"";1);IF(($F$20<>"")*(COUNTIF($B$3:$B$2528;$F$20)>=COLUMNS($F$2:F2)+62);IF(INDEX($D$3:$D$2528;MATCH($F$20;$B$3:$B$2528;0)+COLUMNS($F$2:F2)+61);1;"");""))
perhatikan daerah yang di arsir kuning dan ungu, G3 menjadi G6 dan G4 menjadi
G7untuk G9,G10,G12,G13,G15 & G16, pada daerah terarsir ungu dapat di tambah
masing2 62, sesuai dengan jumlah kolom dari G s.d BP (62 kolom)
bila regional setting anda english maka ubah titikkoma ";" menjadi koma ",".
br,
odong nando
Pada Rabu, 28 Oktober 2015 15:01, "HAIRUL ALAM ha_m...@yahoo.co.id
[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Yth. Para Master Excel
Assalamualaikum Wr. Wb.
Saya mengalami problem seperti data terlampir, Mohon Pencerahannya, untuk
bekerja secara formula dan otomatis agar Excel bekerja tidak berat karena
kebanyakan formula yang memakan memory
Wassalamualaikum Wr. Wb.
TerimakasihHairul Alam
#yiv1907295043 #yiv1907295043 -- #yiv1907295043ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1907295043
#yiv1907295043ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1907295043
#yiv1907295043ygrp-mkp #yiv1907295043hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv1907295043 #yiv1907295043ygrp-mkp #yiv1907295043ads
{margin-bottom:10px;}#yiv1907295043 #yiv1907295043ygrp-mkp .yiv1907295043ad
{padding:0 0;}#yiv1907295043 #yiv1907295043ygrp-mkp .yiv1907295043ad p
{margin:0;}#yiv1907295043 #yiv1907295043ygrp-mkp .yiv1907295043ad a
{color:#0000ff;text-decoration:none;}#yiv1907295043 #yiv1907295043ygrp-sponsor
#yiv1907295043ygrp-lc {font-family:Arial;}#yiv1907295043
#yiv1907295043ygrp-sponsor #yiv1907295043ygrp-lc #yiv1907295043hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1907295043
#yiv1907295043ygrp-sponsor #yiv1907295043ygrp-lc .yiv1907295043ad
{margin-bottom:10px;padding:0 0;}#yiv1907295043 #yiv1907295043actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1907295043
#yiv1907295043activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1907295043
#yiv1907295043activity span {font-weight:700;}#yiv1907295043
#yiv1907295043activity span:first-child
{text-transform:uppercase;}#yiv1907295043 #yiv1907295043activity span a
{color:#5085b6;text-decoration:none;}#yiv1907295043 #yiv1907295043activity span
span {color:#ff7900;}#yiv1907295043 #yiv1907295043activity span
.yiv1907295043underline {text-decoration:underline;}#yiv1907295043
.yiv1907295043attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv1907295043 .yiv1907295043attach div a
{text-decoration:none;}#yiv1907295043 .yiv1907295043attach img
{border:none;padding-right:5px;}#yiv1907295043 .yiv1907295043attach label
{display:block;margin-bottom:5px;}#yiv1907295043 .yiv1907295043attach label a
{text-decoration:none;}#yiv1907295043 blockquote {margin:0 0 0
4px;}#yiv1907295043 .yiv1907295043bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv1907295043
.yiv1907295043bold a {text-decoration:none;}#yiv1907295043 dd.yiv1907295043last
p a {font-family:Verdana;font-weight:700;}#yiv1907295043 dd.yiv1907295043last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1907295043
dd.yiv1907295043last p span.yiv1907295043yshortcuts
{margin-right:0;}#yiv1907295043 div.yiv1907295043attach-table div div a
{text-decoration:none;}#yiv1907295043 div.yiv1907295043attach-table
{width:400px;}#yiv1907295043 div.yiv1907295043file-title a, #yiv1907295043
div.yiv1907295043file-title a:active, #yiv1907295043
div.yiv1907295043file-title a:hover, #yiv1907295043 div.yiv1907295043file-title
a:visited {text-decoration:none;}#yiv1907295043 div.yiv1907295043photo-title a,
#yiv1907295043 div.yiv1907295043photo-title a:active, #yiv1907295043
div.yiv1907295043photo-title a:hover, #yiv1907295043
div.yiv1907295043photo-title a:visited {text-decoration:none;}#yiv1907295043
div#yiv1907295043ygrp-mlmsg #yiv1907295043ygrp-msg p a
span.yiv1907295043yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1907295043
.yiv1907295043green {color:#628c2a;}#yiv1907295043 .yiv1907295043MsoNormal
{margin:0 0 0 0;}#yiv1907295043 o {font-size:0;}#yiv1907295043
#yiv1907295043photos div {float:left;width:72px;}#yiv1907295043
#yiv1907295043photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv1907295043
#yiv1907295043photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1907295043
#yiv1907295043reco-category {font-size:77%;}#yiv1907295043
#yiv1907295043reco-desc {font-size:77%;}#yiv1907295043 .yiv1907295043replbq
{margin:4px;}#yiv1907295043 #yiv1907295043ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv1907295043 #yiv1907295043ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1907295043
#yiv1907295043ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1907295043
#yiv1907295043ygrp-mlmsg select, #yiv1907295043 input, #yiv1907295043 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv1907295043
#yiv1907295043ygrp-mlmsg pre, #yiv1907295043 code {font:115%
monospace;}#yiv1907295043 #yiv1907295043ygrp-mlmsg *
{line-height:1.22em;}#yiv1907295043 #yiv1907295043ygrp-mlmsg #yiv1907295043logo
{padding-bottom:10px;}#yiv1907295043 #yiv1907295043ygrp-msg p a
{font-family:Verdana;}#yiv1907295043 #yiv1907295043ygrp-msg
p#yiv1907295043attach-count span {color:#1E66AE;font-weight:700;}#yiv1907295043
#yiv1907295043ygrp-reco #yiv1907295043reco-head
{color:#ff7900;font-weight:700;}#yiv1907295043 #yiv1907295043ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv1907295043 #yiv1907295043ygrp-sponsor
#yiv1907295043ov li a {font-size:130%;text-decoration:none;}#yiv1907295043
#yiv1907295043ygrp-sponsor #yiv1907295043ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv1907295043
#yiv1907295043ygrp-sponsor #yiv1907295043ov ul {margin:0;padding:0 0 0
8px;}#yiv1907295043 #yiv1907295043ygrp-text
{font-family:Georgia;}#yiv1907295043 #yiv1907295043ygrp-text p {margin:0 0 1em
0;}#yiv1907295043 #yiv1907295043ygrp-text tt {font-size:120%;}#yiv1907295043
#yiv1907295043ygrp-vital ul li:last-child {border-right:none
!important;}#yiv1907295043
