mohon di perhatikan lagi pak hairul untuk "....formula berjalan bila LOCATION
PADA KOLOM B HARUS DI SHORT A to Z, DAN DI DELET SEMUA YANG KOSONG." br,
odong nando
Pada Rabu, 28 Oktober 2015 19:29, "HAIRUL ALAM [email protected]
[belajar-excel]" <[email protected]> menulis:
Yth. Pak Odong Nando
Terlampir masalah Urut & Status yang saya higligh kuning tidak berjumlah
otomatis, Maaf
Terimakasih
Pada Rabu, 28 Oktober 2015 18:24, "HAIRUL ALAM [email protected]
[belajar-excel]" <[email protected]> menulis:
Terimakasih Banyak Pak Odong
Pada Rabu, 28 Oktober 2015 17:55, "odong nando [email protected]
[belajar-excel]" <[email protected]> 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 [email protected]
[belajar-excel]" <[email protected]> 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
#yiv0899979539 #yiv0899979539 -- #yiv0899979539ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0899979539
#yiv0899979539ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0899979539
#yiv0899979539ygrp-mkp #yiv0899979539hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv0899979539 #yiv0899979539ygrp-mkp #yiv0899979539ads
{margin-bottom:10px;}#yiv0899979539 #yiv0899979539ygrp-mkp .yiv0899979539ad
{padding:0 0;}#yiv0899979539 #yiv0899979539ygrp-mkp .yiv0899979539ad p
{margin:0;}#yiv0899979539 #yiv0899979539ygrp-mkp .yiv0899979539ad a
{color:#0000ff;text-decoration:none;}#yiv0899979539 #yiv0899979539ygrp-sponsor
#yiv0899979539ygrp-lc {font-family:Arial;}#yiv0899979539
#yiv0899979539ygrp-sponsor #yiv0899979539ygrp-lc #yiv0899979539hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0899979539
#yiv0899979539ygrp-sponsor #yiv0899979539ygrp-lc .yiv0899979539ad
{margin-bottom:10px;padding:0 0;}#yiv0899979539 #yiv0899979539actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0899979539
#yiv0899979539activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0899979539
#yiv0899979539activity span {font-weight:700;}#yiv0899979539
#yiv0899979539activity span:first-child
{text-transform:uppercase;}#yiv0899979539 #yiv0899979539activity span a
{color:#5085b6;text-decoration:none;}#yiv0899979539 #yiv0899979539activity span
span {color:#ff7900;}#yiv0899979539 #yiv0899979539activity span
.yiv0899979539underline {text-decoration:underline;}#yiv0899979539
.yiv0899979539attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv0899979539 .yiv0899979539attach div a
{text-decoration:none;}#yiv0899979539 .yiv0899979539attach img
{border:none;padding-right:5px;}#yiv0899979539 .yiv0899979539attach label
{display:block;margin-bottom:5px;}#yiv0899979539 .yiv0899979539attach label a
{text-decoration:none;}#yiv0899979539 blockquote {margin:0 0 0
4px;}#yiv0899979539 .yiv0899979539bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv0899979539
.yiv0899979539bold a {text-decoration:none;}#yiv0899979539 dd.yiv0899979539last
p a {font-family:Verdana;font-weight:700;}#yiv0899979539 dd.yiv0899979539last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0899979539
dd.yiv0899979539last p span.yiv0899979539yshortcuts
{margin-right:0;}#yiv0899979539 div.yiv0899979539attach-table div div a
{text-decoration:none;}#yiv0899979539 div.yiv0899979539attach-table
{width:400px;}#yiv0899979539 div.yiv0899979539file-title a, #yiv0899979539
div.yiv0899979539file-title a:active, #yiv0899979539
div.yiv0899979539file-title a:hover, #yiv0899979539 div.yiv0899979539file-title
a:visited {text-decoration:none;}#yiv0899979539 div.yiv0899979539photo-title a,
#yiv0899979539 div.yiv0899979539photo-title a:active, #yiv0899979539
div.yiv0899979539photo-title a:hover, #yiv0899979539
div.yiv0899979539photo-title a:visited {text-decoration:none;}#yiv0899979539
div#yiv0899979539ygrp-mlmsg #yiv0899979539ygrp-msg p a
span.yiv0899979539yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0899979539
.yiv0899979539green {color:#628c2a;}#yiv0899979539 .yiv0899979539MsoNormal
{margin:0 0 0 0;}#yiv0899979539 o {font-size:0;}#yiv0899979539
#yiv0899979539photos div {float:left;width:72px;}#yiv0899979539
#yiv0899979539photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv0899979539
#yiv0899979539photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0899979539
#yiv0899979539reco-category {font-size:77%;}#yiv0899979539
#yiv0899979539reco-desc {font-size:77%;}#yiv0899979539 .yiv0899979539replbq
{margin:4px;}#yiv0899979539 #yiv0899979539ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv0899979539 #yiv0899979539ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0899979539
#yiv0899979539ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0899979539
#yiv0899979539ygrp-mlmsg select, #yiv0899979539 input, #yiv0899979539 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv0899979539
#yiv0899979539ygrp-mlmsg pre, #yiv0899979539 code {font:115%
monospace;}#yiv0899979539 #yiv0899979539ygrp-mlmsg *
{line-height:1.22em;}#yiv0899979539 #yiv0899979539ygrp-mlmsg #yiv0899979539logo
{padding-bottom:10px;}#yiv0899979539 #yiv0899979539ygrp-msg p a
{font-family:Verdana;}#yiv0899979539 #yiv0899979539ygrp-msg
p#yiv0899979539attach-count span {color:#1E66AE;font-weight:700;}#yiv0899979539
#yiv0899979539ygrp-reco #yiv0899979539reco-head
{color:#ff7900;font-weight:700;}#yiv0899979539 #yiv0899979539ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv0899979539 #yiv0899979539ygrp-sponsor
#yiv0899979539ov li a {font-size:130%;text-decoration:none;}#yiv0899979539
#yiv0899979539ygrp-sponsor #yiv0899979539ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv0899979539
#yiv0899979539ygrp-sponsor #yiv0899979539ov ul {margin:0;padding:0 0 0
8px;}#yiv0899979539 #yiv0899979539ygrp-text
{font-family:Georgia;}#yiv0899979539 #yiv0899979539ygrp-text p {margin:0 0 1em
0;}#yiv0899979539 #yiv0899979539ygrp-text tt {font-size:120%;}#yiv0899979539
#yiv0899979539ygrp-vital ul li:last-child {border-right:none
!important;}#yiv0899979539
