Yth...master Kid saya sudah coba mengikuti poin no 1...dan itu menghasilkan angka (warna merah) sesuai baris pada sheet REKAP AR, namun sy tidak paham dengan poin no 2 yaitu deklarasi variabel utk menyimpan cell J3 pada sheet ANUITAS, dan poin no 4 sheet rekap cells itu apa, apakah sy harus membuat sheet baru.. (sheet ada 2, antara lain sheet REKAP AR & ANUITAS).
demikian kendala saya, mohon maaf master sdh bikin repot....
wass
Pada Sabtu, 20 Agustus 2016 1:13, "'Mr. Kid' [email protected]
[belajar-excel]" <[email protected]> menulis:
Hai Marone,
Coba langkah berikut :
1. di sheet anuitas cells J3 diisi formula match : (nomor baris Excel di
lokasi penulisan data di sheet rekap)
=IFERROR(MATCH(C1,'Rekap AR'!B:B,0)*ISNUMBER(H5),0)
2. deklarasikan variabel untuk menyimpan nilai J3 (hasil formula 1 tadi)
3. ambil nilai J3 tadi
4. tulis di sheet rekap cells dengan nomor baris yang disimpan variabel nomor 2
tadi, pada kolom J (kolom ke-10 karena kolom A adalah kolom ke-1)
Selamat mencoba
Regards,
Kid
2016-08-19 16:24 GMT+07:00 marone oke [email protected] [belajar-excel]
<[email protected]>:
Dear Master Excel...
mohon solusinya, gimana cara memindahkan data dari kolom H5 (sheet anuitas) ke
kolom J (sheet rekap AR)
ke masing-masing NIK yang tersedia.. warna kuning...
misal : ketika dipilih nik : M0107010 => nama : ino sucipto, maka pada kolom H5
(sheet anuitas) sisa outstanding pokokakan pindah ke kolom J6 (sheet rekap AR)
warna kuning, begitu seterusnya...
kami mohon dengan sangat, karena datanya ada 2.000 anggota. (file terlampir)
| demikian, terima kasih bantuannya
|
#yiv2519666372 #yiv2519666372 -- #yiv2519666372ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2519666372
#yiv2519666372ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2519666372
#yiv2519666372ygrp-mkp #yiv2519666372hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv2519666372 #yiv2519666372ygrp-mkp #yiv2519666372ads
{margin-bottom:10px;}#yiv2519666372 #yiv2519666372ygrp-mkp .yiv2519666372ad
{padding:0 0;}#yiv2519666372 #yiv2519666372ygrp-mkp .yiv2519666372ad p
{margin:0;}#yiv2519666372 #yiv2519666372ygrp-mkp .yiv2519666372ad a
{color:#0000ff;text-decoration:none;}#yiv2519666372 #yiv2519666372ygrp-sponsor
#yiv2519666372ygrp-lc {font-family:Arial;}#yiv2519666372
#yiv2519666372ygrp-sponsor #yiv2519666372ygrp-lc #yiv2519666372hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2519666372
#yiv2519666372ygrp-sponsor #yiv2519666372ygrp-lc .yiv2519666372ad
{margin-bottom:10px;padding:0 0;}#yiv2519666372 #yiv2519666372actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2519666372
#yiv2519666372activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2519666372
#yiv2519666372activity span {font-weight:700;}#yiv2519666372
#yiv2519666372activity span:first-child
{text-transform:uppercase;}#yiv2519666372 #yiv2519666372activity span a
{color:#5085b6;text-decoration:none;}#yiv2519666372 #yiv2519666372activity span
span {color:#ff7900;}#yiv2519666372 #yiv2519666372activity span
.yiv2519666372underline {text-decoration:underline;}#yiv2519666372
.yiv2519666372attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv2519666372 .yiv2519666372attach div a
{text-decoration:none;}#yiv2519666372 .yiv2519666372attach img
{border:none;padding-right:5px;}#yiv2519666372 .yiv2519666372attach label
{display:block;margin-bottom:5px;}#yiv2519666372 .yiv2519666372attach label a
{text-decoration:none;}#yiv2519666372 blockquote {margin:0 0 0
4px;}#yiv2519666372 .yiv2519666372bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv2519666372
.yiv2519666372bold a {text-decoration:none;}#yiv2519666372 dd.yiv2519666372last
p a {font-family:Verdana;font-weight:700;}#yiv2519666372 dd.yiv2519666372last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2519666372
dd.yiv2519666372last p span.yiv2519666372yshortcuts
{margin-right:0;}#yiv2519666372 div.yiv2519666372attach-table div div a
{text-decoration:none;}#yiv2519666372 div.yiv2519666372attach-table
{width:400px;}#yiv2519666372 div.yiv2519666372file-title a, #yiv2519666372
div.yiv2519666372file-title a:active, #yiv2519666372
div.yiv2519666372file-title a:hover, #yiv2519666372 div.yiv2519666372file-title
a:visited {text-decoration:none;}#yiv2519666372 div.yiv2519666372photo-title a,
#yiv2519666372 div.yiv2519666372photo-title a:active, #yiv2519666372
div.yiv2519666372photo-title a:hover, #yiv2519666372
div.yiv2519666372photo-title a:visited {text-decoration:none;}#yiv2519666372
div#yiv2519666372ygrp-mlmsg #yiv2519666372ygrp-msg p a
span.yiv2519666372yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2519666372
.yiv2519666372green {color:#628c2a;}#yiv2519666372 .yiv2519666372MsoNormal
{margin:0 0 0 0;}#yiv2519666372 o {font-size:0;}#yiv2519666372
#yiv2519666372photos div {float:left;width:72px;}#yiv2519666372
#yiv2519666372photos div div {border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv2519666372
#yiv2519666372photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2519666372
#yiv2519666372reco-category {font-size:77%;}#yiv2519666372
#yiv2519666372reco-desc {font-size:77%;}#yiv2519666372 .yiv2519666372replbq
{margin:4px;}#yiv2519666372 #yiv2519666372ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv2519666372 #yiv2519666372ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2519666372
#yiv2519666372ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2519666372
#yiv2519666372ygrp-mlmsg select, #yiv2519666372 input, #yiv2519666372 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv2519666372
#yiv2519666372ygrp-mlmsg pre, #yiv2519666372 code {font:115%
monospace;}#yiv2519666372 #yiv2519666372ygrp-mlmsg *
{line-height:1.22em;}#yiv2519666372 #yiv2519666372ygrp-mlmsg #yiv2519666372logo
{padding-bottom:10px;}#yiv2519666372 #yiv2519666372ygrp-msg p a
{font-family:Verdana;}#yiv2519666372 #yiv2519666372ygrp-msg
p#yiv2519666372attach-count span {color:#1E66AE;font-weight:700;}#yiv2519666372
#yiv2519666372ygrp-reco #yiv2519666372reco-head
{color:#ff7900;font-weight:700;}#yiv2519666372 #yiv2519666372ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv2519666372 #yiv2519666372ygrp-sponsor
#yiv2519666372ov li a {font-size:130%;text-decoration:none;}#yiv2519666372
#yiv2519666372ygrp-sponsor #yiv2519666372ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv2519666372
#yiv2519666372ygrp-sponsor #yiv2519666372ov ul {margin:0;padding:0 0 0
8px;}#yiv2519666372 #yiv2519666372ygrp-text
{font-family:Georgia;}#yiv2519666372 #yiv2519666372ygrp-text p {margin:0 0 1em
0;}#yiv2519666372 #yiv2519666372ygrp-text tt {font-size:120%;}#yiv2519666372
#yiv2519666372ygrp-vital ul li:last-child {border-right:none
!important;}#yiv2519666372
HITUNG REKON BDS 2.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12
