Rabu, 28 Oktober 2015

[belajar-excel] Digest Number 3616

15 Messages

Digest #3616

Messages

Wed Oct 28, 2015 2:56 am (PDT) . Posted by:

"Doner santoso" donersantoso

dear pak adong, sangat membantu sekali
terimkasih


Pada Rabu, 28 Oktober 2015 14:55, "odong nando odongnando@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  yoo...DS,
semoga file terlampir dapat menjadi solusi
 br,

odong nando


Pada Rabu, 28 Oktober 2015 12:12, "Doner santoso donersantoso@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


 
dear all para senior

mohon bantuan untuk memunculkan jam telat  karyawan kemudian di rekap jumlahnya
detail terlampir

atas kebaikan para senior saya ucapkan terimakasih
DS


#yiv5331759421 #yiv5331759421 -- #yiv5331759421ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5331759421 #yiv5331759421ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5331759421 #yiv5331759421ygrp-mkp #yiv5331759421hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5331759421 #yiv5331759421ygrp-mkp #yiv5331759421ads {margin-bottom:10px;}#yiv5331759421 #yiv5331759421ygrp-mkp .yiv5331759421ad {padding:0 0;}#yiv5331759421 #yiv5331759421ygrp-mkp .yiv5331759421ad p {margin:0;}#yiv5331759421 #yiv5331759421ygrp-mkp .yiv5331759421ad a {color:#0000ff;text-decoration:none;}#yiv5331759421 #yiv5331759421ygrp-sponsor #yiv5331759421ygrp-lc {font-family:Arial;}#yiv5331759421 #yiv5331759421ygrp-sponsor #yiv5331759421ygrp-lc #yiv5331759421hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5331759421 #yiv5331759421ygrp-sponsor #yiv5331759421ygrp-lc .yiv5331759421ad {margin-bottom:10px;padding:0 0;}#yiv5331759421 #yiv5331759421actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5331759421 #yiv5331759421activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5331759421 #yiv5331759421activity span {font-weight:700;}#yiv5331759421 #yiv5331759421activity span:first-child {text-transform:uppercase;}#yiv5331759421 #yiv5331759421activity span a {color:#5085b6;text-decoration:none;}#yiv5331759421 #yiv5331759421activity span span {color:#ff7900;}#yiv5331759421 #yiv5331759421activity span .yiv5331759421underline {text-decoration:underline;}#yiv5331759421 .yiv5331759421attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv5331759421 .yiv5331759421attach div a {text-decoration:none;}#yiv5331759421 .yiv5331759421attach img {border:none;padding-right:5px;}#yiv5331759421 .yiv5331759421attach label {display:block;margin-bottom:5px;}#yiv5331759421 .yiv5331759421attach label a {text-decoration:none;}#yiv5331759421 blockquote {margin:0 0 0 4px;}#yiv5331759421 .yiv5331759421bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv5331759421 .yiv5331759421bold a {text-decoration:none;}#yiv5331759421 dd.yiv5331759421last p a {font-family:Verdana;font-weight:700;}#yiv5331759421 dd.yiv5331759421last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5331759421 dd.yiv5331759421last p span.yiv5331759421yshortcuts {margin-right:0;}#yiv5331759421 div.yiv5331759421attach-table div div a {text-decoration:none;}#yiv5331759421 div.yiv5331759421attach-table {width:400px;}#yiv5331759421 div.yiv5331759421file-title a, #yiv5331759421 div.yiv5331759421file-title a:active, #yiv5331759421 div.yiv5331759421file-title a:hover, #yiv5331759421 div.yiv5331759421file-title a:visited {text-decoration:none;}#yiv5331759421 div.yiv5331759421photo-title a, #yiv5331759421 div.yiv5331759421photo-title a:active, #yiv5331759421 div.yiv5331759421photo-title a:hover, #yiv5331759421 div.yiv5331759421photo-title a:visited {text-decoration:none;}#yiv5331759421 div#yiv5331759421ygrp-mlmsg #yiv5331759421ygrp-msg p a span.yiv5331759421yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5331759421 .yiv5331759421green {color:#628c2a;}#yiv5331759421 .yiv5331759421MsoNormal {margin:0 0 0 0;}#yiv5331759421 o {font-size:0;}#yiv5331759421 #yiv5331759421photos div {float:left;width:72px;}#yiv5331759421 #yiv5331759421photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv5331759421 #yiv5331759421photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5331759421 #yiv5331759421reco-category {font-size:77%;}#yiv5331759421 #yiv5331759421reco-desc {font-size:77%;}#yiv5331759421 .yiv5331759421replbq {margin:4px;}#yiv5331759421 #yiv5331759421ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv5331759421 #yiv5331759421ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5331759421 #yiv5331759421ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5331759421 #yiv5331759421ygrp-mlmsg select, #yiv5331759421 input, #yiv5331759421 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv5331759421 #yiv5331759421ygrp-mlmsg pre, #yiv5331759421 code {font:115% monospace;}#yiv5331759421 #yiv5331759421ygrp-mlmsg * {line-height:1.22em;}#yiv5331759421 #yiv5331759421ygrp-mlmsg #yiv5331759421logo {padding-bottom:10px;}#yiv5331759421 #yiv5331759421ygrp-msg p a {font-family:Verdana;}#yiv5331759421 #yiv5331759421ygrp-msg p#yiv5331759421attach-count span {color:#1E66AE;font-weight:700;}#yiv5331759421 #yiv5331759421ygrp-reco #yiv5331759421reco-head {color:#ff7900;font-weight:700;}#yiv5331759421 #yiv5331759421ygrp-reco {margin-bottom:20px;padding:0px;}#yiv5331759421 #yiv5331759421ygrp-sponsor #yiv5331759421ov li a {font-size:130%;text-decoration:none;}#yiv5331759421 #yiv5331759421ygrp-sponsor #yiv5331759421ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv5331759421 #yiv5331759421ygrp-sponsor #yiv5331759421ov ul {margin:0;padding:0 0 0 8px;}#yiv5331759421 #yiv5331759421ygrp-text {font-family:Georgia;}#yiv5331759421 #yiv5331759421ygrp-text p {margin:0 0 1em 0;}#yiv5331759421 #yiv5331759421ygrp-text tt {font-size:120%;}#yiv5331759421 #yiv5331759421ygrp-vital ul li:last-child {border-right:none !important;}#yiv5331759421

Wed Oct 28, 2015 3:37 am (PDT) . Posted by:

"Doner santoso" donersantoso

pak adong , maaf lupa saya kasih tau, yang dikatakan terlambat adalah jika lewat dari 08.45 WIB

bisa minta tolong di bantu lagi pak.. saya gak bisa modifikasinya  . sudah saya coba malah hancur
Tks
DS

Pada Rabu, 28 Oktober 2015 16:38, Doner santoso <donersantoso@yahoo.com> menulis:


dear pak adong, sangat membantu sekali
terimkasih


Pada Rabu, 28 Oktober 2015 14:55, "odong nando odongnando@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  yoo...DS,
semoga file terlampir dapat menjadi solusi
 br,

odong nando


Pada Rabu, 28 Oktober 2015 12:12, "Doner santoso donersantoso@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


 
dear all para senior

mohon bantuan untuk memunculkan jam telat  karyawan kemudian di rekap jumlahnya
detail terlampir

atas kebaikan para senior saya ucapkan terimakasih
DS


#yiv9787752268 -- #yiv9787752268ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9787752268 #yiv9787752268ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9787752268 #yiv9787752268ygrp-mkp #yiv9787752268hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9787752268 #yiv9787752268ygrp-mkp #yiv9787752268ads {margin-bottom:10px;}#yiv9787752268 #yiv9787752268ygrp-mkp .yiv9787752268ad {padding:0 0;}#yiv9787752268 #yiv9787752268ygrp-mkp .yiv9787752268ad p {margin:0;}#yiv9787752268 #yiv9787752268ygrp-mkp .yiv9787752268ad a {color:#0000ff;text-decoration:none;}#yiv9787752268 #yiv9787752268ygrp-sponsor #yiv9787752268ygrp-lc {font-family:Arial;}#yiv9787752268 #yiv9787752268ygrp-sponsor #yiv9787752268ygrp-lc #yiv9787752268hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9787752268 #yiv9787752268ygrp-sponsor #yiv9787752268ygrp-lc .yiv9787752268ad {margin-bottom:10px;padding:0 0;}#yiv9787752268 #yiv9787752268actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9787752268 #yiv9787752268activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9787752268 #yiv9787752268activity span {font-weight:700;}#yiv9787752268 #yiv9787752268activity span:first-child {text-transform:uppercase;}#yiv9787752268 #yiv9787752268activity span a {color:#5085b6;text-decoration:none;}#yiv9787752268 #yiv9787752268activity span span {color:#ff7900;}#yiv9787752268 #yiv9787752268activity span .yiv9787752268underline {text-decoration:underline;}#yiv9787752268 .yiv9787752268attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9787752268 .yiv9787752268attach div a {text-decoration:none;}#yiv9787752268 .yiv9787752268attach img {border:none;padding-right:5px;}#yiv9787752268 .yiv9787752268attach label {display:block;margin-bottom:5px;}#yiv9787752268 .yiv9787752268attach label a {text-decoration:none;}#yiv9787752268 blockquote {margin:0 0 0 4px;}#yiv9787752268 .yiv9787752268bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9787752268 .yiv9787752268bold a {text-decoration:none;}#yiv9787752268 dd.yiv9787752268last p a {font-family:Verdana;font-weight:700;}#yiv9787752268 dd.yiv9787752268last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9787752268 dd.yiv9787752268last p span.yiv9787752268yshortcuts {margin-right:0;}#yiv9787752268 div.yiv9787752268attach-table div div a {text-decoration:none;}#yiv9787752268 div.yiv9787752268attach-table {width:400px;}#yiv9787752268 div.yiv9787752268file-title a, #yiv9787752268 div.yiv9787752268file-title a:active, #yiv9787752268 div.yiv9787752268file-title a:hover, #yiv9787752268 div.yiv9787752268file-title a:visited {text-decoration:none;}#yiv9787752268 div.yiv9787752268photo-title a, #yiv9787752268 div.yiv9787752268photo-title a:active, #yiv9787752268 div.yiv9787752268photo-title a:hover, #yiv9787752268 div.yiv9787752268photo-title a:visited {text-decoration:none;}#yiv9787752268 div#yiv9787752268ygrp-mlmsg #yiv9787752268ygrp-msg p a span.yiv9787752268yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9787752268 .yiv9787752268green {color:#628c2a;}#yiv9787752268 .yiv9787752268MsoNormal {margin:0 0 0 0;}#yiv9787752268 o {font-size:0;}#yiv9787752268 #yiv9787752268photos div {float:left;width:72px;}#yiv9787752268 #yiv9787752268photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv9787752268 #yiv9787752268photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9787752268 #yiv9787752268reco-category {font-size:77%;}#yiv9787752268 #yiv9787752268reco-desc {font-size:77%;}#yiv9787752268 .yiv9787752268replbq {margin:4px;}#yiv9787752268 #yiv9787752268ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9787752268 #yiv9787752268ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9787752268 #yiv9787752268ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9787752268 #yiv9787752268ygrp-mlmsg select, #yiv9787752268 input, #yiv9787752268 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9787752268 #yiv9787752268ygrp-mlmsg pre, #yiv9787752268 code {font:115% monospace;}#yiv9787752268 #yiv9787752268ygrp-mlmsg * {line-height:1.22em;}#yiv9787752268 #yiv9787752268ygrp-mlmsg #yiv9787752268logo {padding-bottom:10px;}#yiv9787752268 #yiv9787752268ygrp-msg p a {font-family:Verdana;}#yiv9787752268 #yiv9787752268ygrp-msg p#yiv9787752268attach-count span {color:#1E66AE;font-weight:700;}#yiv9787752268 #yiv9787752268ygrp-reco #yiv9787752268reco-head {color:#ff7900;font-weight:700;}#yiv9787752268 #yiv9787752268ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9787752268 #yiv9787752268ygrp-sponsor #yiv9787752268ov li a {font-size:130%;text-decoration:none;}#yiv9787752268 #yiv9787752268ygrp-sponsor #yiv9787752268ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9787752268 #yiv9787752268ygrp-sponsor #yiv9787752268ov ul {margin:0;padding:0 0 0 8px;}#yiv9787752268 #yiv9787752268ygrp-text {font-family:Georgia;}#yiv9787752268 #yiv9787752268ygrp-text p {margin:0 0 1em 0;}#yiv9787752268 #yiv9787752268ygrp-text tt {font-size:120%;}#yiv9787752268 #yiv9787752268ygrp-vital ul li:last-child {border-right:none !important;}#yiv9787752268

Wed Oct 28, 2015 4:13 am (PDT) . Posted by:

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

Hai DS,

File terlampir.

Wassalam,
Kid

On Wed, Oct 28, 2015 at 5:26 PM, Doner santoso donersantoso@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:

>
>
> pak adong , maaf lupa saya kasih tau, yang dikatakan terlambat adalah jika
> lewat dari 08.45 WIB
>
> bisa minta tolong di bantu lagi pak.. saya gak bisa modifikasinya . sudah
> saya coba malah hancur
>
> Tks
>
> DS
>
>
> Pada Rabu, 28 Oktober 2015 16:38, Doner santoso <donersantoso@yahoo.com>
> menulis:
>
>
> dear pak adong, sangat membantu sekali
>
> terimkasih
>
>
>
> Pada Rabu, 28 Oktober 2015 14:55, "odong nando odongnando@yahoo.com
> [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
>
>
>
> yoo...DS,
>
> semoga file terlampir dapat menjadi solusi
>
> br,
>
> odong nando
>
>
>
> Pada Rabu, 28 Oktober 2015 12:12, "Doner santoso donersantoso@yahoo.com
> [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
>
>
>
>
> dear all para senior
>
>
> mohon bantuan untuk memunculkan jam telat karyawan kemudian di rekap
> jumlahnya
>
> detail terlampir
>
>
> atas kebaikan para senior saya ucapkan terimakasih
> DS
>
>
>
>
>
>
>
>

Wed Oct 28, 2015 4:51 am (PDT) . Posted by:

"odong nando"

ok, ganti formula di cell D6 dengan, dan copy kan ke cell yang membutuhkan
=IFERROR(IF(TIME(8;45;0)<INDEX('ori  absen '!$M$6:$M$54;MATCH('Table Keterlambatan'!$C6&'Table Keterlambatan'!D$5;INDEX('ori  absen '!$B$6:$B$54&'ori  absen '!$J$6:$J$54;0);0);0);INDEX('ori  absen '!$M$6:$M$54;MATCH('Table Keterlambatan'!$C6&'Table Keterlambatan'!D$5;INDEX('ori  absen '!$B$6:$B$54&'ori  absen '!$J$6:$J$54;0);0));"");"")
jika regional setting komputer adalah english maka ganti titik koma ";" dengan koma ",".
 br,

odong nando


Pada Rabu, 28 Oktober 2015 17:37, "Doner santoso donersantoso@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  pak adong , maaf lupa saya kasih tau, yang dikatakan terlambat adalah jika lewat dari 08.45 WIB

bisa minta tolong di bantu lagi pak.. saya gak bisa modifikasinya  . sudah saya coba malah hancur
Tks
DS

Pada Rabu, 28 Oktober 2015 16:38, Doner santoso <donersantoso@yahoo.com> menulis:


dear pak adong, sangat membantu sekali
terimkasih


Pada Rabu, 28 Oktober 2015 14:55, "odong nando odongnando@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  yoo...DS,
semoga file terlampir dapat menjadi solusi
 br,

odong nando


Pada Rabu, 28 Oktober 2015 12:12, "Doner santoso donersantoso@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


 
dear all para senior

mohon bantuan untuk memunculkan jam telat  karyawan kemudian di rekap jumlahnya
detail terlampir

atas kebaikan para senior saya ucapkan terimakasih
DS


#yiv5534311580 #yiv5534311580 -- #yiv5534311580ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5534311580 #yiv5534311580ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5534311580 #yiv5534311580ygrp-mkp #yiv5534311580hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5534311580 #yiv5534311580ygrp-mkp #yiv5534311580ads {margin-bottom:10px;}#yiv5534311580 #yiv5534311580ygrp-mkp .yiv5534311580ad {padding:0 0;}#yiv5534311580 #yiv5534311580ygrp-mkp .yiv5534311580ad p {margin:0;}#yiv5534311580 #yiv5534311580ygrp-mkp .yiv5534311580ad a {color:#0000ff;text-decoration:none;}#yiv5534311580 #yiv5534311580ygrp-sponsor #yiv5534311580ygrp-lc {font-family:Arial;}#yiv5534311580 #yiv5534311580ygrp-sponsor #yiv5534311580ygrp-lc #yiv5534311580hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5534311580 #yiv5534311580ygrp-sponsor #yiv5534311580ygrp-lc .yiv5534311580ad {margin-bottom:10px;padding:0 0;}#yiv5534311580 #yiv5534311580actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5534311580 #yiv5534311580activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5534311580 #yiv5534311580activity span {font-weight:700;}#yiv5534311580 #yiv5534311580activity span:first-child {text-transform:uppercase;}#yiv5534311580 #yiv5534311580activity span a {color:#5085b6;text-decoration:none;}#yiv5534311580 #yiv5534311580activity span span {color:#ff7900;}#yiv5534311580 #yiv5534311580activity span .yiv5534311580underline {text-decoration:underline;}#yiv5534311580 .yiv5534311580attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv5534311580 .yiv5534311580attach div a {text-decoration:none;}#yiv5534311580 .yiv5534311580attach img {border:none;padding-right:5px;}#yiv5534311580 .yiv5534311580attach label {display:block;margin-bottom:5px;}#yiv5534311580 .yiv5534311580attach label a {text-decoration:none;}#yiv5534311580 blockquote {margin:0 0 0 4px;}#yiv5534311580 .yiv5534311580bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv5534311580 .yiv5534311580bold a {text-decoration:none;}#yiv5534311580 dd.yiv5534311580last p a {font-family:Verdana;font-weight:700;}#yiv5534311580 dd.yiv5534311580last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5534311580 dd.yiv5534311580last p span.yiv5534311580yshortcuts {margin-right:0;}#yiv5534311580 div.yiv5534311580attach-table div div a {text-decoration:none;}#yiv5534311580 div.yiv5534311580attach-table {width:400px;}#yiv5534311580 div.yiv5534311580file-title a, #yiv5534311580 div.yiv5534311580file-title a:active, #yiv5534311580 div.yiv5534311580file-title a:hover, #yiv5534311580 div.yiv5534311580file-title a:visited {text-decoration:none;}#yiv5534311580 div.yiv5534311580photo-title a, #yiv5534311580 div.yiv5534311580photo-title a:active, #yiv5534311580 div.yiv5534311580photo-title a:hover, #yiv5534311580 div.yiv5534311580photo-title a:visited {text-decoration:none;}#yiv5534311580 div#yiv5534311580ygrp-mlmsg #yiv5534311580ygrp-msg p a span.yiv5534311580yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5534311580 .yiv5534311580green {color:#628c2a;}#yiv5534311580 .yiv5534311580MsoNormal {margin:0 0 0 0;}#yiv5534311580 o {font-size:0;}#yiv5534311580 #yiv5534311580photos div {float:left;width:72px;}#yiv5534311580 #yiv5534311580photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv5534311580 #yiv5534311580photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5534311580 #yiv5534311580reco-category {font-size:77%;}#yiv5534311580 #yiv5534311580reco-desc {font-size:77%;}#yiv5534311580 .yiv5534311580replbq {margin:4px;}#yiv5534311580 #yiv5534311580ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv5534311580 #yiv5534311580ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5534311580 #yiv5534311580ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5534311580 #yiv5534311580ygrp-mlmsg select, #yiv5534311580 input, #yiv5534311580 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv5534311580 #yiv5534311580ygrp-mlmsg pre, #yiv5534311580 code {font:115% monospace;}#yiv5534311580 #yiv5534311580ygrp-mlmsg * {line-height:1.22em;}#yiv5534311580 #yiv5534311580ygrp-mlmsg #yiv5534311580logo {padding-bottom:10px;}#yiv5534311580 #yiv5534311580ygrp-msg p a {font-family:Verdana;}#yiv5534311580 #yiv5534311580ygrp-msg p#yiv5534311580attach-count span {color:#1E66AE;font-weight:700;}#yiv5534311580 #yiv5534311580ygrp-reco #yiv5534311580reco-head {color:#ff7900;font-weight:700;}#yiv5534311580 #yiv5534311580ygrp-reco {margin-bottom:20px;padding:0px;}#yiv5534311580 #yiv5534311580ygrp-sponsor #yiv5534311580ov li a {font-size:130%;text-decoration:none;}#yiv5534311580 #yiv5534311580ygrp-sponsor #yiv5534311580ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv5534311580 #yiv5534311580ygrp-sponsor #yiv5534311580ov ul {margin:0;padding:0 0 0 8px;}#yiv5534311580 #yiv5534311580ygrp-text {font-family:Georgia;}#yiv5534311580 #yiv5534311580ygrp-text p {margin:0 0 1em 0;}#yiv5534311580 #yiv5534311580ygrp-text tt {font-size:120%;}#yiv5534311580 #yiv5534311580ygrp-vital ul li:last-child {border-right:none !important;}#yiv5534311580

Wed Oct 28, 2015 6:36 pm (PDT) . Posted by:

"Doner santoso" donersantoso

selamat pagi semua
Mr Kid..  terimakasih.. sedikit bertanya find last dan find first yang membedakan apa ya

TksDS


Pada Rabu, 28 Oktober 2015 18:13, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Hai DS,

File terlampir.

Wassalam,
Kid

On Wed, Oct 28, 2015 at 5:26 PM, Doner santoso donersantoso@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:

  pak adong , maaf lupa saya kasih tau, yang dikatakan terlambat adalah jika lewat dari 08.45 WIB

bisa minta tolong di bantu lagi pak.. saya gak bisa modifikasinya  . sudah saya coba malah hancur
Tks
DS

Pada Rabu, 28 Oktober 2015 16:38, Doner santoso <donersantoso@yahoo.com> menulis:


dear pak adong, sangat membantu sekali
terimkasih


Pada Rabu, 28 Oktober 2015 14:55, "odong nando odongnando@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  yoo...DS,
semoga file terlampir dapat menjadi solusi
 br,

odong nando


Pada Rabu, 28 Oktober 2015 12:12, "Doner santoso donersantoso@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


 
dear all para senior

mohon bantuan untuk memunculkan jam telat  karyawan kemudian di rekap jumlahnya
detail terlampir

atas kebaikan para senior saya ucapkan terimakasih
DS


#yiv2055606131 #yiv2055606131 -- #yiv2055606131ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2055606131 #yiv2055606131ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2055606131 #yiv2055606131ygrp-mkp #yiv2055606131hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv2055606131 #yiv2055606131ygrp-mkp #yiv2055606131ads {margin-bottom:10px;}#yiv2055606131 #yiv2055606131ygrp-mkp .yiv2055606131ad {padding:0 0;}#yiv2055606131 #yiv2055606131ygrp-mkp .yiv2055606131ad p {margin:0;}#yiv2055606131 #yiv2055606131ygrp-mkp .yiv2055606131ad a {color:#0000ff;text-decoration:none;}#yiv2055606131 #yiv2055606131ygrp-sponsor #yiv2055606131ygrp-lc {font-family:Arial;}#yiv2055606131 #yiv2055606131ygrp-sponsor #yiv2055606131ygrp-lc #yiv2055606131hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2055606131 #yiv2055606131ygrp-sponsor #yiv2055606131ygrp-lc .yiv2055606131ad {margin-bottom:10px;padding:0 0;}#yiv2055606131 #yiv2055606131actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2055606131 #yiv2055606131activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2055606131 #yiv2055606131activity span {font-weight:700;}#yiv2055606131 #yiv2055606131activity span:first-child {text-transform:uppercase;}#yiv2055606131 #yiv2055606131activity span a {color:#5085b6;text-decoration:none;}#yiv2055606131 #yiv2055606131activity span span {color:#ff7900;}#yiv2055606131 #yiv2055606131activity span .yiv2055606131underline {text-decoration:underline;}#yiv2055606131 .yiv2055606131attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv2055606131 .yiv2055606131attach div a {text-decoration:none;}#yiv2055606131 .yiv2055606131attach img {border:none;padding-right:5px;}#yiv2055606131 .yiv2055606131attach label {display:block;margin-bottom:5px;}#yiv2055606131 .yiv2055606131attach label a {text-decoration:none;}#yiv2055606131 blockquote {margin:0 0 0 4px;}#yiv2055606131 .yiv2055606131bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv2055606131 .yiv2055606131bold a {text-decoration:none;}#yiv2055606131 dd.yiv2055606131last p a {font-family:Verdana;font-weight:700;}#yiv2055606131 dd.yiv2055606131last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2055606131 dd.yiv2055606131last p span.yiv2055606131yshortcuts {margin-right:0;}#yiv2055606131 div.yiv2055606131attach-table div div a {text-decoration:none;}#yiv2055606131 div.yiv2055606131attach-table {width:400px;}#yiv2055606131 div.yiv2055606131file-title a, #yiv2055606131 div.yiv2055606131file-title a:active, #yiv2055606131 div.yiv2055606131file-title a:hover, #yiv2055606131 div.yiv2055606131file-title a:visited {text-decoration:none;}#yiv2055606131 div.yiv2055606131photo-title a, #yiv2055606131 div.yiv2055606131photo-title a:active, #yiv2055606131 div.yiv2055606131photo-title a:hover, #yiv2055606131 div.yiv2055606131photo-title a:visited {text-decoration:none;}#yiv2055606131 div#yiv2055606131ygrp-mlmsg #yiv2055606131ygrp-msg p a span.yiv2055606131yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2055606131 .yiv2055606131green {color:#628c2a;}#yiv2055606131 .yiv2055606131MsoNormal {margin:0 0 0 0;}#yiv2055606131 o {font-size:0;}#yiv2055606131 #yiv2055606131photos div {float:left;width:72px;}#yiv2055606131 #yiv2055606131photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv2055606131 #yiv2055606131photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2055606131 #yiv2055606131reco-category {font-size:77%;}#yiv2055606131 #yiv2055606131reco-desc {font-size:77%;}#yiv2055606131 .yiv2055606131replbq {margin:4px;}#yiv2055606131 #yiv2055606131ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv2055606131 #yiv2055606131ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2055606131 #yiv2055606131ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2055606131 #yiv2055606131ygrp-mlmsg select, #yiv2055606131 input, #yiv2055606131 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv2055606131 #yiv2055606131ygrp-mlmsg pre, #yiv2055606131 code {font:115% monospace;}#yiv2055606131 #yiv2055606131ygrp-mlmsg * {line-height:1.22em;}#yiv2055606131 #yiv2055606131ygrp-mlmsg #yiv2055606131logo {padding-bottom:10px;}#yiv2055606131 #yiv2055606131ygrp-msg p a {font-family:Verdana;}#yiv2055606131 #yiv2055606131ygrp-msg p#yiv2055606131attach-count span {color:#1E66AE;font-weight:700;}#yiv2055606131 #yiv2055606131ygrp-reco #yiv2055606131reco-head {color:#ff7900;font-weight:700;}#yiv2055606131 #yiv2055606131ygrp-reco {margin-bottom:20px;padding:0px;}#yiv2055606131 #yiv2055606131ygrp-sponsor #yiv2055606131ov li a {font-size:130%;text-decoration:none;}#yiv2055606131 #yiv2055606131ygrp-sponsor #yiv2055606131ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv2055606131 #yiv2055606131ygrp-sponsor #yiv2055606131ov ul {margin:0;padding:0 0 0 8px;}#yiv2055606131 #yiv2055606131ygrp-text {font-family:Georgia;}#yiv2055606131 #yiv2055606131ygrp-text p {margin:0 0 1em 0;}#yiv2055606131 #yiv2055606131ygrp-text tt {font-size:120%;}#yiv2055606131 #yiv2055606131ygrp-vital ul li:last-child {border-right:none !important;}#yiv2055606131

Wed Oct 28, 2015 6:42 pm (PDT) . Posted by:

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

Hai DS,

Find last dan find first akan menghasilkan nilai yang berbeda jika ada 2
record duplicate berdasar keys nya (no dan tanggal).
Find first adalah mencari record pertama yang sesuai keys-nya. Fungsi yang
digunakan adalah vLookUp dengan exact match (paramter terakhir fungsi
vLookUp yang biasa diisi FALSE untuk exact match dan TRUE untuk approximate
match).
Find last adalah mencari record terkahir yang sesuai keys-nya. Fungsi yang
digunakan adalah LookUp dengan proses array didalamnya. Pada data yang
terurut berdasar keys, maka formula find last bisa berbunyi seperti find
first, tetapi dengan approximate match.

Demikianlah...

Wassalam,
Kid

On Thu, Oct 29, 2015 at 8:35 AM, Doner santoso donersantoso@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:

>
>
> selamat pagi semua
>
> Mr Kid.. terimakasih.. sedikit bertanya find last dan find first yang
> membedakan apa ya
>
>
> Tks
> DS
>
>
>
> Pada Rabu, 28 Oktober 2015 18:13, "'Mr. Kid' mr.nmkid@gmail.com
> [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
>
>
>
> Hai DS,
>
> File terlampir.
>
> Wassalam,
> Kid
>
>
>
>
> On Wed, Oct 28, 2015 at 5:26 PM, Doner santoso donersantoso@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
> pak adong , maaf lupa saya kasih tau, yang dikatakan terlambat adalah jika
> lewat dari 08.45 WIB
>
> bisa minta tolong di bantu lagi pak.. saya gak bisa modifikasinya . sudah
> saya coba malah hancur
>
> Tks
>
> DS
>
>
> Pada Rabu, 28 Oktober 2015 16:38, Doner santoso <donersantoso@yahoo.com>
> menulis:
>
>
> dear pak adong, sangat membantu sekali
>
> terimkasih
>
>
>
> Pada Rabu, 28 Oktober 2015 14:55, "odong nando odongnando@yahoo.com
> [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
>
>
>
> yoo...DS,
>
> semoga file terlampir dapat menjadi solusi
>
> br,
>
> odong nando
>
>
>
> Pada Rabu, 28 Oktober 2015 12:12, "Doner santoso donersantoso@yahoo.com
> [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
>
>
>
>
> dear all para senior
>
>
> mohon bantuan untuk memunculkan jam telat karyawan kemudian di rekap
> jumlahnya
>
> detail terlampir
>
>
> atas kebaikan para senior saya ucapkan terimakasih
> DS
>
>
>
>
>
>
>
>
>
>
>

Wed Oct 28, 2015 7:00 pm (PDT) . Posted by:

"Doner santoso" donersantoso

dear pak adong dan Mr Kid
matursuwun semua.. terimkasih atas pencerahannya
dan terimkasih to momod atas milist ini

TksDS


Pada Kamis, 29 Oktober 2015 8:42, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Hai DS,

Find last dan find first akan menghasilkan nilai yang berbeda jika ada 2 record duplicate berdasar keys nya (no dan tanggal).
Find first adalah mencari record pertama yang sesuai keys-nya. Fungsi yang digunakan adalah vLookUp dengan exact match (paramter terakhir fungsi vLookUp yang biasa diisi FALSE untuk exact match dan TRUE untuk approximate match).
Find last adalah mencari record terkahir yang sesuai keys-nya. Fungsi yang digunakan adalah LookUp dengan proses array didalamnya. Pada data yang terurut berdasar keys, maka formula find last bisa berbunyi seperti find first, tetapi dengan approximate match.

Demikianlah...

Wassalam,
Kid

On Thu, Oct 29, 2015 at 8:35 AM, Doner santoso donersantoso@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:

  selamat pagi semua
Mr Kid..  terimakasih.. sedikit bertanya find last dan find first yang membedakan apa ya

TksDS


Pada Rabu, 28 Oktober 2015 18:13, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Hai DS,

File terlampir.

Wassalam,
Kid

On Wed, Oct 28, 2015 at 5:26 PM, Doner santoso donersantoso@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:

  pak adong , maaf lupa saya kasih tau, yang dikatakan terlambat adalah jika lewat dari 08.45 WIB

bisa minta tolong di bantu lagi pak.. saya gak bisa modifikasinya  . sudah saya coba malah hancur
Tks
DS

Pada Rabu, 28 Oktober 2015 16:38, Doner santoso <donersantoso@yahoo.com> menulis:


dear pak adong, sangat membantu sekali
terimkasih


Pada Rabu, 28 Oktober 2015 14:55, "odong nando odongnando@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  yoo...DS,
semoga file terlampir dapat menjadi solusi
 br,

odong nando


Pada Rabu, 28 Oktober 2015 12:12, "Doner santoso donersantoso@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


 
dear all para senior

mohon bantuan untuk memunculkan jam telat  karyawan kemudian di rekap jumlahnya
detail terlampir

atas kebaikan para senior saya ucapkan terimakasih
DS


#yiv8445226089 #yiv8445226089 -- #yiv8445226089ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8445226089 #yiv8445226089ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8445226089 #yiv8445226089ygrp-mkp #yiv8445226089hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv8445226089 #yiv8445226089ygrp-mkp #yiv8445226089ads {margin-bottom:10px;}#yiv8445226089 #yiv8445226089ygrp-mkp .yiv8445226089ad {padding:0 0;}#yiv8445226089 #yiv8445226089ygrp-mkp .yiv8445226089ad p {margin:0;}#yiv8445226089 #yiv8445226089ygrp-mkp .yiv8445226089ad a {color:#0000ff;text-decoration:none;}#yiv8445226089 #yiv8445226089ygrp-sponsor #yiv8445226089ygrp-lc {font-family:Arial;}#yiv8445226089 #yiv8445226089ygrp-sponsor #yiv8445226089ygrp-lc #yiv8445226089hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8445226089 #yiv8445226089ygrp-sponsor #yiv8445226089ygrp-lc .yiv8445226089ad {margin-bottom:10px;padding:0 0;}#yiv8445226089 #yiv8445226089actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8445226089 #yiv8445226089activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8445226089 #yiv8445226089activity span {font-weight:700;}#yiv8445226089 #yiv8445226089activity span:first-child {text-transform:uppercase;}#yiv8445226089 #yiv8445226089activity span a {color:#5085b6;text-decoration:none;}#yiv8445226089 #yiv8445226089activity span span {color:#ff7900;}#yiv8445226089 #yiv8445226089activity span .yiv8445226089underline {text-decoration:underline;}#yiv8445226089 .yiv8445226089attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv8445226089 .yiv8445226089attach div a {text-decoration:none;}#yiv8445226089 .yiv8445226089attach img {border:none;padding-right:5px;}#yiv8445226089 .yiv8445226089attach label {display:block;margin-bottom:5px;}#yiv8445226089 .yiv8445226089attach label a {text-decoration:none;}#yiv8445226089 blockquote {margin:0 0 0 4px;}#yiv8445226089 .yiv8445226089bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv8445226089 .yiv8445226089bold a {text-decoration:none;}#yiv8445226089 dd.yiv8445226089last p a {font-family:Verdana;font-weight:700;}#yiv8445226089 dd.yiv8445226089last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8445226089 dd.yiv8445226089last p span.yiv8445226089yshortcuts {margin-right:0;}#yiv8445226089 div.yiv8445226089attach-table div div a {text-decoration:none;}#yiv8445226089 div.yiv8445226089attach-table {width:400px;}#yiv8445226089 div.yiv8445226089file-title a, #yiv8445226089 div.yiv8445226089file-title a:active, #yiv8445226089 div.yiv8445226089file-title a:hover, #yiv8445226089 div.yiv8445226089file-title a:visited {text-decoration:none;}#yiv8445226089 div.yiv8445226089photo-title a, #yiv8445226089 div.yiv8445226089photo-title a:active, #yiv8445226089 div.yiv8445226089photo-title a:hover, #yiv8445226089 div.yiv8445226089photo-title a:visited {text-decoration:none;}#yiv8445226089 div#yiv8445226089ygrp-mlmsg #yiv8445226089ygrp-msg p a span.yiv8445226089yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8445226089 .yiv8445226089green {color:#628c2a;}#yiv8445226089 .yiv8445226089MsoNormal {margin:0 0 0 0;}#yiv8445226089 o {font-size:0;}#yiv8445226089 #yiv8445226089photos div {float:left;width:72px;}#yiv8445226089 #yiv8445226089photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv8445226089 #yiv8445226089photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8445226089 #yiv8445226089reco-category {font-size:77%;}#yiv8445226089 #yiv8445226089reco-desc {font-size:77%;}#yiv8445226089 .yiv8445226089replbq {margin:4px;}#yiv8445226089 #yiv8445226089ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv8445226089 #yiv8445226089ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8445226089 #yiv8445226089ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8445226089 #yiv8445226089ygrp-mlmsg select, #yiv8445226089 input, #yiv8445226089 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv8445226089 #yiv8445226089ygrp-mlmsg pre, #yiv8445226089 code {font:115% monospace;}#yiv8445226089 #yiv8445226089ygrp-mlmsg * {line-height:1.22em;}#yiv8445226089 #yiv8445226089ygrp-mlmsg #yiv8445226089logo {padding-bottom:10px;}#yiv8445226089 #yiv8445226089ygrp-msg p a {font-family:Verdana;}#yiv8445226089 #yiv8445226089ygrp-msg p#yiv8445226089attach-count span {color:#1E66AE;font-weight:700;}#yiv8445226089 #yiv8445226089ygrp-reco #yiv8445226089reco-head {color:#ff7900;font-weight:700;}#yiv8445226089 #yiv8445226089ygrp-reco {margin-bottom:20px;padding:0px;}#yiv8445226089 #yiv8445226089ygrp-sponsor #yiv8445226089ov li a {font-size:130%;text-decoration:none;}#yiv8445226089 #yiv8445226089ygrp-sponsor #yiv8445226089ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv8445226089 #yiv8445226089ygrp-sponsor #yiv8445226089ov ul {margin:0;padding:0 0 0 8px;}#yiv8445226089 #yiv8445226089ygrp-text {font-family:Georgia;}#yiv8445226089 #yiv8445226089ygrp-text p {margin:0 0 1em 0;}#yiv8445226089 #yiv8445226089ygrp-text tt {font-size:120%;}#yiv8445226089 #yiv8445226089ygrp-vital ul li:last-child {border-right:none !important;}#yiv8445226089

Wed Oct 28, 2015 3:56 am (PDT) . Posted by:

"odong nando"

 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_mj76@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 #yiv7606490892 -- #yiv7606490892ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7606490892 #yiv7606490892ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7606490892 #yiv7606490892ygrp-mkp #yiv7606490892hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv7606490892 #yiv7606490892ygrp-mkp #yiv7606490892ads {margin-bottom:10px;}#yiv7606490892 #yiv7606490892ygrp-mkp .yiv7606490892ad {padding:0 0;}#yiv7606490892 #yiv7606490892ygrp-mkp .yiv7606490892ad p {margin:0;}#yiv7606490892 #yiv7606490892ygrp-mkp .yiv7606490892ad a {color:#0000ff;text-decoration:none;}#yiv7606490892 #yiv7606490892ygrp-sponsor #yiv7606490892ygrp-lc {font-family:Arial;}#yiv7606490892 #yiv7606490892ygrp-sponsor #yiv7606490892ygrp-lc #yiv7606490892hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7606490892 #yiv7606490892ygrp-sponsor #yiv7606490892ygrp-lc .yiv7606490892ad {margin-bottom:10px;padding:0 0;}#yiv7606490892 #yiv7606490892actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7606490892 #yiv7606490892activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7606490892 #yiv7606490892activity span {font-weight:700;}#yiv7606490892 #yiv7606490892activity span:first-child {text-transform:uppercase;}#yiv7606490892 #yiv7606490892activity span a {color:#5085b6;text-decoration:none;}#yiv7606490892 #yiv7606490892activity span span {color:#ff7900;}#yiv7606490892 #yiv7606490892activity span .yiv7606490892underline {text-decoration:underline;}#yiv7606490892 .yiv7606490892attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv7606490892 .yiv7606490892attach div a {text-decoration:none;}#yiv7606490892 .yiv7606490892attach img {border:none;padding-right:5px;}#yiv7606490892 .yiv7606490892attach label {display:block;margin-bottom:5px;}#yiv7606490892 .yiv7606490892attach label a {text-decoration:none;}#yiv7606490892 blockquote {margin:0 0 0 4px;}#yiv7606490892 .yiv7606490892bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv7606490892 .yiv7606490892bold a {text-decoration:none;}#yiv7606490892 dd.yiv7606490892last p a {font-family:Verdana;font-weight:700;}#yiv7606490892 dd.yiv7606490892last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7606490892 dd.yiv7606490892last p span.yiv7606490892yshortcuts {margin-right:0;}#yiv7606490892 div.yiv7606490892attach-table div div a {text-decoration:none;}#yiv7606490892 div.yiv7606490892attach-table {width:400px;}#yiv7606490892 div.yiv7606490892file-title a, #yiv7606490892 div.yiv7606490892file-title a:active, #yiv7606490892 div.yiv7606490892file-title a:hover, #yiv7606490892 div.yiv7606490892file-title a:visited {text-decoration:none;}#yiv7606490892 div.yiv7606490892photo-title a, #yiv7606490892 div.yiv7606490892photo-title a:active, #yiv7606490892 div.yiv7606490892photo-title a:hover, #yiv7606490892 div.yiv7606490892photo-title a:visited {text-decoration:none;}#yiv7606490892 div#yiv7606490892ygrp-mlmsg #yiv7606490892ygrp-msg p a span.yiv7606490892yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7606490892 .yiv7606490892green {color:#628c2a;}#yiv7606490892 .yiv7606490892MsoNormal {margin:0 0 0 0;}#yiv7606490892 o {font-size:0;}#yiv7606490892 #yiv7606490892photos div {float:left;width:72px;}#yiv7606490892 #yiv7606490892photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv7606490892 #yiv7606490892photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7606490892 #yiv7606490892reco-category {font-size:77%;}#yiv7606490892 #yiv7606490892reco-desc {font-size:77%;}#yiv7606490892 .yiv7606490892replbq {margin:4px;}#yiv7606490892 #yiv7606490892ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv7606490892 #yiv7606490892ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7606490892 #yiv7606490892ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7606490892 #yiv7606490892ygrp-mlmsg select, #yiv7606490892 input, #yiv7606490892 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv7606490892 #yiv7606490892ygrp-mlmsg pre, #yiv7606490892 code {font:115% monospace;}#yiv7606490892 #yiv7606490892ygrp-mlmsg * {line-height:1.22em;}#yiv7606490892 #yiv7606490892ygrp-mlmsg #yiv7606490892logo {padding-bottom:10px;}#yiv7606490892 #yiv7606490892ygrp-msg p a {font-family:Verdana;}#yiv7606490892 #yiv7606490892ygrp-msg p#yiv7606490892attach-count span {color:#1E66AE;font-weight:700;}#yiv7606490892 #yiv7606490892ygrp-reco #yiv7606490892reco-head {color:#ff7900;font-weight:700;}#yiv7606490892 #yiv7606490892ygrp-reco {margin-bottom:20px;padding:0px;}#yiv7606490892 #yiv7606490892ygrp-sponsor #yiv7606490892ov li a {font-size:130%;text-decoration:none;}#yiv7606490892 #yiv7606490892ygrp-sponsor #yiv7606490892ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv7606490892 #yiv7606490892ygrp-sponsor #yiv7606490892ov ul {margin:0;padding:0 0 0 8px;}#yiv7606490892 #yiv7606490892ygrp-text {font-family:Georgia;}#yiv7606490892 #yiv7606490892ygrp-text p {margin:0 0 1em 0;}#yiv7606490892 #yiv7606490892ygrp-text tt {font-size:120%;}#yiv7606490892 #yiv7606490892ygrp-vital ul li:last-child {border-right:none !important;}#yiv7606490892

Wed Oct 28, 2015 3:59 am (PDT) . Posted by:

"odong nando"

oiya, ada yang ketinggalan syarat formula dapat 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 17:56, "odong nando odongnando@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_mj76@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

#yiv5313552085 #yiv5313552085 -- #yiv5313552085ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5313552085 #yiv5313552085ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5313552085 #yiv5313552085ygrp-mkp #yiv5313552085hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5313552085 #yiv5313552085ygrp-mkp #yiv5313552085ads {margin-bottom:10px;}#yiv5313552085 #yiv5313552085ygrp-mkp .yiv5313552085ad {padding:0 0;}#yiv5313552085 #yiv5313552085ygrp-mkp .yiv5313552085ad p {margin:0;}#yiv5313552085 #yiv5313552085ygrp-mkp .yiv5313552085ad a {color:#0000ff;text-decoration:none;}#yiv5313552085 #yiv5313552085ygrp-sponsor #yiv5313552085ygrp-lc {font-family:Arial;}#yiv5313552085 #yiv5313552085ygrp-sponsor #yiv5313552085ygrp-lc #yiv5313552085hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5313552085 #yiv5313552085ygrp-sponsor #yiv5313552085ygrp-lc .yiv5313552085ad {margin-bottom:10px;padding:0 0;}#yiv5313552085 #yiv5313552085actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5313552085 #yiv5313552085activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5313552085 #yiv5313552085activity span {font-weight:700;}#yiv5313552085 #yiv5313552085activity span:first-child {text-transform:uppercase;}#yiv5313552085 #yiv5313552085activity span a {color:#5085b6;text-decoration:none;}#yiv5313552085 #yiv5313552085activity span span {color:#ff7900;}#yiv5313552085 #yiv5313552085activity span .yiv5313552085underline {text-decoration:underline;}#yiv5313552085 .yiv5313552085attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv5313552085 .yiv5313552085attach div a {text-decoration:none;}#yiv5313552085 .yiv5313552085attach img {border:none;padding-right:5px;}#yiv5313552085 .yiv5313552085attach label {display:block;margin-bottom:5px;}#yiv5313552085 .yiv5313552085attach label a {text-decoration:none;}#yiv5313552085 blockquote {margin:0 0 0 4px;}#yiv5313552085 .yiv5313552085bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv5313552085 .yiv5313552085bold a {text-decoration:none;}#yiv5313552085 dd.yiv5313552085last p a {font-family:Verdana;font-weight:700;}#yiv5313552085 dd.yiv5313552085last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5313552085 dd.yiv5313552085last p span.yiv5313552085yshortcuts {margin-right:0;}#yiv5313552085 div.yiv5313552085attach-table div div a {text-decoration:none;}#yiv5313552085 div.yiv5313552085attach-table {width:400px;}#yiv5313552085 div.yiv5313552085file-title a, #yiv5313552085 div.yiv5313552085file-title a:active, #yiv5313552085 div.yiv5313552085file-title a:hover, #yiv5313552085 div.yiv5313552085file-title a:visited {text-decoration:none;}#yiv5313552085 div.yiv5313552085photo-title a, #yiv5313552085 div.yiv5313552085photo-title a:active, #yiv5313552085 div.yiv5313552085photo-title a:hover, #yiv5313552085 div.yiv5313552085photo-title a:visited {text-decoration:none;}#yiv5313552085 div#yiv5313552085ygrp-mlmsg #yiv5313552085ygrp-msg p a span.yiv5313552085yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5313552085 .yiv5313552085green {color:#628c2a;}#yiv5313552085 .yiv5313552085MsoNormal {margin:0 0 0 0;}#yiv5313552085 o {font-size:0;}#yiv5313552085 #yiv5313552085photos div {float:left;width:72px;}#yiv5313552085 #yiv5313552085photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv5313552085 #yiv5313552085photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5313552085 #yiv5313552085reco-category {font-size:77%;}#yiv5313552085 #yiv5313552085reco-desc {font-size:77%;}#yiv5313552085 .yiv5313552085replbq {margin:4px;}#yiv5313552085 #yiv5313552085ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv5313552085 #yiv5313552085ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5313552085 #yiv5313552085ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5313552085 #yiv5313552085ygrp-mlmsg select, #yiv5313552085 input, #yiv5313552085 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv5313552085 #yiv5313552085ygrp-mlmsg pre, #yiv5313552085 code {font:115% monospace;}#yiv5313552085 #yiv5313552085ygrp-mlmsg * {line-height:1.22em;}#yiv5313552085 #yiv5313552085ygrp-mlmsg #yiv5313552085logo {padding-bottom:10px;}#yiv5313552085 #yiv5313552085ygrp-msg p a {font-family:Verdana;}#yiv5313552085 #yiv5313552085ygrp-msg p#yiv5313552085attach-count span {color:#1E66AE;font-weight:700;}#yiv5313552085 #yiv5313552085ygrp-reco #yiv5313552085reco-head {color:#ff7900;font-weight:700;}#yiv5313552085 #yiv5313552085ygrp-reco {margin-bottom:20px;padding:0px;}#yiv5313552085 #yiv5313552085ygrp-sponsor #yiv5313552085ov li a {font-size:130%;text-decoration:none;}#yiv5313552085 #yiv5313552085ygrp-sponsor #yiv5313552085ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv5313552085 #yiv5313552085ygrp-sponsor #yiv5313552085ov ul {margin:0;padding:0 0 0 8px;}#yiv5313552085 #yiv5313552085ygrp-text {font-family:Georgia;}#yiv5313552085 #yiv5313552085ygrp-text p {margin:0 0 1em 0;}#yiv5313552085 #yiv5313552085ygrp-text tt {font-size:120%;}#yiv5313552085 #yiv5313552085ygrp-vital ul li:last-child {border-right:none !important;}#yiv5313552085

Wed Oct 28, 2015 4:24 am (PDT) . Posted by:

"HAIRUL ALAM"

Terimakasih Banyak Pak Odong

Pada Rabu, 28 Oktober 2015 17:55, "odong nando odongnando@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_mj76@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

Wed Oct 28, 2015 5:29 am (PDT) . Posted by:

"HAIRUL ALAM"

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 ha_mj76@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Terimakasih Banyak Pak Odong

Pada Rabu, 28 Oktober 2015 17:55, "odong nando odongnando@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_mj76@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

#yiv5901624624 #yiv5901624624 -- #yiv5901624624ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5901624624 #yiv5901624624ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5901624624 #yiv5901624624ygrp-mkp #yiv5901624624hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5901624624 #yiv5901624624ygrp-mkp #yiv5901624624ads {margin-bottom:10px;}#yiv5901624624 #yiv5901624624ygrp-mkp .yiv5901624624ad {padding:0 0;}#yiv5901624624 #yiv5901624624ygrp-mkp .yiv5901624624ad p {margin:0;}#yiv5901624624 #yiv5901624624ygrp-mkp .yiv5901624624ad a {color:#0000ff;text-decoration:none;}#yiv5901624624 #yiv5901624624ygrp-sponsor #yiv5901624624ygrp-lc {font-family:Arial;}#yiv5901624624 #yiv5901624624ygrp-sponsor #yiv5901624624ygrp-lc #yiv5901624624hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5901624624 #yiv5901624624ygrp-sponsor #yiv5901624624ygrp-lc .yiv5901624624ad {margin-bottom:10px;padding:0 0;}#yiv5901624624 #yiv5901624624actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5901624624 #yiv5901624624activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5901624624 #yiv5901624624activity span {font-weight:700;}#yiv5901624624 #yiv5901624624activity span:first-child {text-transform:uppercase;}#yiv5901624624 #yiv5901624624activity span a {color:#5085b6;text-decoration:none;}#yiv5901624624 #yiv5901624624activity span span {color:#ff7900;}#yiv5901624624 #yiv5901624624activity span .yiv5901624624underline {text-decoration:underline;}#yiv5901624624 .yiv5901624624attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv5901624624 .yiv5901624624attach div a {text-decoration:none;}#yiv5901624624 .yiv5901624624attach img {border:none;padding-right:5px;}#yiv5901624624 .yiv5901624624attach label {display:block;margin-bottom:5px;}#yiv5901624624 .yiv5901624624attach label a {text-decoration:none;}#yiv5901624624 blockquote {margin:0 0 0 4px;}#yiv5901624624 .yiv5901624624bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv5901624624 .yiv5901624624bold a {text-decoration:none;}#yiv5901624624 dd.yiv5901624624last p a {font-family:Verdana;font-weight:700;}#yiv5901624624 dd.yiv5901624624last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5901624624 dd.yiv5901624624last p span.yiv5901624624yshortcuts {margin-right:0;}#yiv5901624624 div.yiv5901624624attach-table div div a {text-decoration:none;}#yiv5901624624 div.yiv5901624624attach-table {width:400px;}#yiv5901624624 div.yiv5901624624file-title a, #yiv5901624624 div.yiv5901624624file-title a:active, #yiv5901624624 div.yiv5901624624file-title a:hover, #yiv5901624624 div.yiv5901624624file-title a:visited {text-decoration:none;}#yiv5901624624 div.yiv5901624624photo-title a, #yiv5901624624 div.yiv5901624624photo-title a:active, #yiv5901624624 div.yiv5901624624photo-title a:hover, #yiv5901624624 div.yiv5901624624photo-title a:visited {text-decoration:none;}#yiv5901624624 div#yiv5901624624ygrp-mlmsg #yiv5901624624ygrp-msg p a span.yiv5901624624yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5901624624 .yiv5901624624green {color:#628c2a;}#yiv5901624624 .yiv5901624624MsoNormal {margin:0 0 0 0;}#yiv5901624624 o {font-size:0;}#yiv5901624624 #yiv5901624624photos div {float:left;width:72px;}#yiv5901624624 #yiv5901624624photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv5901624624 #yiv5901624624photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5901624624 #yiv5901624624reco-category {font-size:77%;}#yiv5901624624 #yiv5901624624reco-desc {font-size:77%;}#yiv5901624624 .yiv5901624624replbq {margin:4px;}#yiv5901624624 #yiv5901624624ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv5901624624 #yiv5901624624ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5901624624 #yiv5901624624ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5901624624 #yiv5901624624ygrp-mlmsg select, #yiv5901624624 input, #yiv5901624624 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv5901624624 #yiv5901624624ygrp-mlmsg pre, #yiv5901624624 code {font:115% monospace;}#yiv5901624624 #yiv5901624624ygrp-mlmsg * {line-height:1.22em;}#yiv5901624624 #yiv5901624624ygrp-mlmsg #yiv5901624624logo {padding-bottom:10px;}#yiv5901624624 #yiv5901624624ygrp-msg p a {font-family:Verdana;}#yiv5901624624 #yiv5901624624ygrp-msg p#yiv5901624624attach-count span {color:#1E66AE;font-weight:700;}#yiv5901624624 #yiv5901624624ygrp-reco #yiv5901624624reco-head {color:#ff7900;font-weight:700;}#yiv5901624624 #yiv5901624624ygrp-reco {margin-bottom:20px;padding:0px;}#yiv5901624624 #yiv5901624624ygrp-sponsor #yiv5901624624ov li a {font-size:130%;text-decoration:none;}#yiv5901624624 #yiv5901624624ygrp-sponsor #yiv5901624624ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv5901624624 #yiv5901624624ygrp-sponsor #yiv5901624624ov ul {margin:0;padding:0 0 0 8px;}#yiv5901624624 #yiv5901624624ygrp-text {font-family:Georgia;}#yiv5901624624 #yiv5901624624ygrp-text p {margin:0 0 1em 0;}#yiv5901624624 #yiv5901624624ygrp-text tt {font-size:120%;}#yiv5901624624 #yiv5901624624ygrp-vital ul li:last-child {border-right:none !important;}#yiv5901624624

Wed Oct 28, 2015 9:57 am (PDT) . Posted by:

"odong nando"

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 ha_mj76@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> 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 ha_mj76@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Terimakasih Banyak Pak Odong

Pada Rabu, 28 Oktober 2015 17:55, "odong nando odongnando@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_mj76@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

#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

Wed Oct 28, 2015 4:22 pm (PDT) . Posted by:

"HAIRUL ALAM"

Maaf Pak Odong
Maksudnya yang di delet row yang kosong begitu, Terimakasih

Pada Rabu, 28 Oktober 2015 23:57, "odong nando odongnando@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  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 ha_mj76@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> 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 ha_mj76@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Terimakasih Banyak Pak Odong

Pada Rabu, 28 Oktober 2015 17:55, "odong nando odongnando@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_mj76@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

#yiv1913237671 #yiv1913237671 -- #yiv1913237671ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1913237671 #yiv1913237671ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1913237671 #yiv1913237671ygrp-mkp #yiv1913237671hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1913237671 #yiv1913237671ygrp-mkp #yiv1913237671ads {margin-bottom:10px;}#yiv1913237671 #yiv1913237671ygrp-mkp .yiv1913237671ad {padding:0 0;}#yiv1913237671 #yiv1913237671ygrp-mkp .yiv1913237671ad p {margin:0;}#yiv1913237671 #yiv1913237671ygrp-mkp .yiv1913237671ad a {color:#0000ff;text-decoration:none;}#yiv1913237671 #yiv1913237671ygrp-sponsor #yiv1913237671ygrp-lc {font-family:Arial;}#yiv1913237671 #yiv1913237671ygrp-sponsor #yiv1913237671ygrp-lc #yiv1913237671hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1913237671 #yiv1913237671ygrp-sponsor #yiv1913237671ygrp-lc .yiv1913237671ad {margin-bottom:10px;padding:0 0;}#yiv1913237671 #yiv1913237671actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1913237671 #yiv1913237671activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1913237671 #yiv1913237671activity span {font-weight:700;}#yiv1913237671 #yiv1913237671activity span:first-child {text-transform:uppercase;}#yiv1913237671 #yiv1913237671activity span a {color:#5085b6;text-decoration:none;}#yiv1913237671 #yiv1913237671activity span span {color:#ff7900;}#yiv1913237671 #yiv1913237671activity span .yiv1913237671underline {text-decoration:underline;}#yiv1913237671 .yiv1913237671attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1913237671 .yiv1913237671attach div a {text-decoration:none;}#yiv1913237671 .yiv1913237671attach img {border:none;padding-right:5px;}#yiv1913237671 .yiv1913237671attach label {display:block;margin-bottom:5px;}#yiv1913237671 .yiv1913237671attach label a {text-decoration:none;}#yiv1913237671 blockquote {margin:0 0 0 4px;}#yiv1913237671 .yiv1913237671bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1913237671 .yiv1913237671bold a {text-decoration:none;}#yiv1913237671 dd.yiv1913237671last p a {font-family:Verdana;font-weight:700;}#yiv1913237671 dd.yiv1913237671last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1913237671 dd.yiv1913237671last p span.yiv1913237671yshortcuts {margin-right:0;}#yiv1913237671 div.yiv1913237671attach-table div div a {text-decoration:none;}#yiv1913237671 div.yiv1913237671attach-table {width:400px;}#yiv1913237671 div.yiv1913237671file-title a, #yiv1913237671 div.yiv1913237671file-title a:active, #yiv1913237671 div.yiv1913237671file-title a:hover, #yiv1913237671 div.yiv1913237671file-title a:visited {text-decoration:none;}#yiv1913237671 div.yiv1913237671photo-title a, #yiv1913237671 div.yiv1913237671photo-title a:active, #yiv1913237671 div.yiv1913237671photo-title a:hover, #yiv1913237671 div.yiv1913237671photo-title a:visited {text-decoration:none;}#yiv1913237671 div#yiv1913237671ygrp-mlmsg #yiv1913237671ygrp-msg p a span.yiv1913237671yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1913237671 .yiv1913237671green {color:#628c2a;}#yiv1913237671 .yiv1913237671MsoNormal {margin:0 0 0 0;}#yiv1913237671 o {font-size:0;}#yiv1913237671 #yiv1913237671photos div {float:left;width:72px;}#yiv1913237671 #yiv1913237671photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv1913237671 #yiv1913237671photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1913237671 #yiv1913237671reco-category {font-size:77%;}#yiv1913237671 #yiv1913237671reco-desc {font-size:77%;}#yiv1913237671 .yiv1913237671replbq {margin:4px;}#yiv1913237671 #yiv1913237671ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1913237671 #yiv1913237671ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1913237671 #yiv1913237671ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1913237671 #yiv1913237671ygrp-mlmsg select, #yiv1913237671 input, #yiv1913237671 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1913237671 #yiv1913237671ygrp-mlmsg pre, #yiv1913237671 code {font:115% monospace;}#yiv1913237671 #yiv1913237671ygrp-mlmsg * {line-height:1.22em;}#yiv1913237671 #yiv1913237671ygrp-mlmsg #yiv1913237671logo {padding-bottom:10px;}#yiv1913237671 #yiv1913237671ygrp-msg p a {font-family:Verdana;}#yiv1913237671 #yiv1913237671ygrp-msg p#yiv1913237671attach-count span {color:#1E66AE;font-weight:700;}#yiv1913237671 #yiv1913237671ygrp-reco #yiv1913237671reco-head {color:#ff7900;font-weight:700;}#yiv1913237671 #yiv1913237671ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1913237671 #yiv1913237671ygrp-sponsor #yiv1913237671ov li a {font-size:130%;text-decoration:none;}#yiv1913237671 #yiv1913237671ygrp-sponsor #yiv1913237671ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1913237671 #yiv1913237671ygrp-sponsor #yiv1913237671ov ul {margin:0;padding:0 0 0 8px;}#yiv1913237671 #yiv1913237671ygrp-text {font-family:Georgia;}#yiv1913237671 #yiv1913237671ygrp-text p {margin:0 0 1em 0;}#yiv1913237671 #yiv1913237671ygrp-text tt {font-size:120%;}#yiv1913237671 #yiv1913237671ygrp-vital ul li:last-child {border-right:none !important;}#yiv1913237671

Wed Oct 28, 2015 3:15 pm (PDT) . Posted by:

"windawati ku"

Alhamdulillah terimakasih master atas UDFnya.Wassalam.

Pada Rabu, 28 Oktober 2015 12:16, "Nang Agus nangagus@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Coba buka messege lama di:
https://groups.yahoo.com/neo/groups/belajar-excel/conversations/topics/21237

atau di:

http://excelfeed.blogspot.co.id/2012/12/belajar-excel-digest-number-1964.html#6b

sedangkan filenya ada di sini:
https://app.box.com/s/0o2m3b6xqyrfucpxdlgn

salam

On 10/28/15, windawati ku windawatiku@yahoo.co.id [belajar-excel]
<belajar-excel@yahoogroups.com> wrote:
> Assalamu'alaikum, selamat pagi suhu semuanya, saya anggota baru saja gabung
> di group ini, langsung saja saya mau tanyakan sekaligus mintol udf terbilang
> yang otomatis bisa baca nilai-nilai raport seperti ini:6.00 (Terbilang: Enam
> koma nol nol)1180 (Seribu seratus delapan puluh)81.01 (Delapan puluh satu
> koma nol satu) dstnya....saya sudah temukan Terbilangnilai dari ctv (semoga
> ilmunya yang bermanfaat terus mengalir buat beliau), tapi hanya bisa membaca
> angka satuan dan desimal, keinginan saya supaya ketiga model nilai tsb bisa
> dibaca oleh satu bentuk udf, mohon bantuannya...terimakasih.
#yiv4349251343 #yiv4349251343 -- #yiv4349251343ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4349251343 #yiv4349251343ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4349251343 #yiv4349251343ygrp-mkp #yiv4349251343hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv4349251343 #yiv4349251343ygrp-mkp #yiv4349251343ads {margin-bottom:10px;}#yiv4349251343 #yiv4349251343ygrp-mkp .yiv4349251343ad {padding:0 0;}#yiv4349251343 #yiv4349251343ygrp-mkp .yiv4349251343ad p {margin:0;}#yiv4349251343 #yiv4349251343ygrp-mkp .yiv4349251343ad a {color:#0000ff;text-decoration:none;}#yiv4349251343 #yiv4349251343ygrp-sponsor #yiv4349251343ygrp-lc {font-family:Arial;}#yiv4349251343 #yiv4349251343ygrp-sponsor #yiv4349251343ygrp-lc #yiv4349251343hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4349251343 #yiv4349251343ygrp-sponsor #yiv4349251343ygrp-lc .yiv4349251343ad {margin-bottom:10px;padding:0 0;}#yiv4349251343 #yiv4349251343actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4349251343 #yiv4349251343activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4349251343 #yiv4349251343activity span {font-weight:700;}#yiv4349251343 #yiv4349251343activity span:first-child {text-transform:uppercase;}#yiv4349251343 #yiv4349251343activity span a {color:#5085b6;text-decoration:none;}#yiv4349251343 #yiv4349251343activity span span {color:#ff7900;}#yiv4349251343 #yiv4349251343activity span .yiv4349251343underline {text-decoration:underline;}#yiv4349251343 .yiv4349251343attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv4349251343 .yiv4349251343attach div a {text-decoration:none;}#yiv4349251343 .yiv4349251343attach img {border:none;padding-right:5px;}#yiv4349251343 .yiv4349251343attach label {display:block;margin-bottom:5px;}#yiv4349251343 .yiv4349251343attach label a {text-decoration:none;}#yiv4349251343 blockquote {margin:0 0 0 4px;}#yiv4349251343 .yiv4349251343bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv4349251343 .yiv4349251343bold a {text-decoration:none;}#yiv4349251343 dd.yiv4349251343last p a {font-family:Verdana;font-weight:700;}#yiv4349251343 dd.yiv4349251343last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4349251343 dd.yiv4349251343last p span.yiv4349251343yshortcuts {margin-right:0;}#yiv4349251343 div.yiv4349251343attach-table div div a {text-decoration:none;}#yiv4349251343 div.yiv4349251343attach-table {width:400px;}#yiv4349251343 div.yiv4349251343file-title a, #yiv4349251343 div.yiv4349251343file-title a:active, #yiv4349251343 div.yiv4349251343file-title a:hover, #yiv4349251343 div.yiv4349251343file-title a:visited {text-decoration:none;}#yiv4349251343 div.yiv4349251343photo-title a, #yiv4349251343 div.yiv4349251343photo-title a:active, #yiv4349251343 div.yiv4349251343photo-title a:hover, #yiv4349251343 div.yiv4349251343photo-title a:visited {text-decoration:none;}#yiv4349251343 div#yiv4349251343ygrp-mlmsg #yiv4349251343ygrp-msg p a span.yiv4349251343yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4349251343 .yiv4349251343green {color:#628c2a;}#yiv4349251343 .yiv4349251343MsoNormal {margin:0 0 0 0;}#yiv4349251343 o {font-size:0;}#yiv4349251343 #yiv4349251343photos div {float:left;width:72px;}#yiv4349251343 #yiv4349251343photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv4349251343 #yiv4349251343photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4349251343 #yiv4349251343reco-category {font-size:77%;}#yiv4349251343 #yiv4349251343reco-desc {font-size:77%;}#yiv4349251343 .yiv4349251343replbq {margin:4px;}#yiv4349251343 #yiv4349251343ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv4349251343 #yiv4349251343ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4349251343 #yiv4349251343ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4349251343 #yiv4349251343ygrp-mlmsg select, #yiv4349251343 input, #yiv4349251343 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv4349251343 #yiv4349251343ygrp-mlmsg pre, #yiv4349251343 code {font:115% monospace;}#yiv4349251343 #yiv4349251343ygrp-mlmsg * {line-height:1.22em;}#yiv4349251343 #yiv4349251343ygrp-mlmsg #yiv4349251343logo {padding-bottom:10px;}#yiv4349251343 #yiv4349251343ygrp-msg p a {font-family:Verdana;}#yiv4349251343 #yiv4349251343ygrp-msg p#yiv4349251343attach-count span {color:#1E66AE;font-weight:700;}#yiv4349251343 #yiv4349251343ygrp-reco #yiv4349251343reco-head {color:#ff7900;font-weight:700;}#yiv4349251343 #yiv4349251343ygrp-reco {margin-bottom:20px;padding:0px;}#yiv4349251343 #yiv4349251343ygrp-sponsor #yiv4349251343ov li a {font-size:130%;text-decoration:none;}#yiv4349251343 #yiv4349251343ygrp-sponsor #yiv4349251343ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv4349251343 #yiv4349251343ygrp-sponsor #yiv4349251343ov ul {margin:0;padding:0 0 0 8px;}#yiv4349251343 #yiv4349251343ygrp-text {font-family:Georgia;}#yiv4349251343 #yiv4349251343ygrp-text p {margin:0 0 1em 0;}#yiv4349251343 #yiv4349251343ygrp-text tt {font-size:120%;}#yiv4349251343 #yiv4349251343ygrp-vital ul li:last-child {border-right:none !important;}#yiv4349251343

Wed Oct 28, 2015 8:18 pm (PDT) . Posted by:

"Doner santoso" donersantoso

dear pak adongdear Mr KidDear rekan semua
waduh mohon maklum ya.. sepertinya sangat mudah  tapi susah buat saya.. hehe
berdasarkan petunjuk yang telah di berikan saya coba menggunkan file Mr Kid ( rumus pendek menurut hemat saya yang awam excel ).
namun ada kendala setelah saya inplementasikan hanya terekap mentok di tanggal 10 aja ( file revisi 04 yang saya buat terlampir)

kenapa ya Mr Kid, mohon pencerahan

Tks


Pada Rabu, 28 Oktober 2015 11:15, Doner santoso <donersantoso@yahoo.com> menulis:


dear all para senior

mohon bantuan untuk memunculkan jam telat  karyawan kemudian di rekap jumlahnya
detail terlampir

atas kebaikan para senior saya ucapkan terimakasih
DS

============================================================
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