Dear Mr Kid
It's work
Masalah berikutnya adalah, jika kasbon pasir itu ternyata settlement nya adalah
material yg lain, misalnya kerikil, maka status kasbon menjadi "UNSETTLED".
Padahal, kasbon yang awalnya untuk pasir itu sbnrnya sudah SETTLED, hanya
berganti menjadi kerikil.
Mohon pencerahannya lagi ya..
On Friday, December 29, 2017 5:14 AM, "'Mr. Kid' mr.nm...@gmail.com
[belajar-excel]" <belajar-excel@yahoogroups.com> wrote:
Hai Ki,
Begini :
=IF(COUNTIFS([No
Kasbon],B2,[Keterangan],D2,[Transaksi],"Set*"),"","UN")&"SETTLED"
Regards,
Kid
2017-12-28 14:20 GMT+07:00 kiki tanjung kiek...@yahoo.com [belajar-excel]
<belajar-excel@yahoogroups.com>:
Dear rekan rekan
Mohon bantuan untuk otomatisasi input data berikut :
Kasbon No : 18000001 Total Kasbon : 15.000.000 Terdiri atas 2 inputan (row) ;1.
Semen : 10.000.000 -----> status transaksi : "Kasbon"2. Pasir : 5.000.000
------> status transaksi : "Kasbon"
Pertanyaan :
Ketika laporan pertanggungjawaban kasbon untuk item Semen diterima, maka
diinput :
1. Semen : 5.000.000 -----> status transaksi "SETTLED" -----> bagaimana caranya
agar status "SETTLED" otomatis terisiLalu bagaimana caranya agar status
transaksi "Kasbon" pada inputan Semen : 10.000.000 juga otomatis terisi
"SETTLED"
2. Bagaimana caranya inputan Pasir : 5.000.000 terisi "UNSETTLED" ( karena
belum ada inputan settlement nya )
Saya sudah coba menggunakan rumus berikut :
=IF(MAX(COUNTIF([No Kasbon],[No Kasbon]))>1,"SETTLED"," UNSETTLED")
Tapi dengan rumus diatas, item inputan "Pasir" juga menjadi "SETTLED", padahal
belum ada inputan settlement nya.
Terlampir contoh kasus. Terima kasih banyak atas bantuan rekan rekan.
#yiv8482919575 #yiv8482919575 -- #yiv8482919575ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8482919575
#yiv8482919575ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8482919575
#yiv8482919575ygrp-mkp #yiv8482919575hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv8482919575 #yiv8482919575ygrp-mkp #yiv8482919575ads
{margin-bottom:10px;}#yiv8482919575 #yiv8482919575ygrp-mkp .yiv8482919575ad
{padding:0 0;}#yiv8482919575 #yiv8482919575ygrp-mkp .yiv8482919575ad p
{margin:0;}#yiv8482919575 #yiv8482919575ygrp-mkp .yiv8482919575ad a
{color:#0000ff;text-decoration:none;}#yiv8482919575 #yiv8482919575ygrp-sponsor
#yiv8482919575ygrp-lc {font-family:Arial;}#yiv8482919575
#yiv8482919575ygrp-sponsor #yiv8482919575ygrp-lc #yiv8482919575hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8482919575
#yiv8482919575ygrp-sponsor #yiv8482919575ygrp-lc .yiv8482919575ad
{margin-bottom:10px;padding:0 0;}#yiv8482919575 #yiv8482919575actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8482919575
#yiv8482919575activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8482919575
#yiv8482919575activity span {font-weight:700;}#yiv8482919575
#yiv8482919575activity span:first-child
{text-transform:uppercase;}#yiv8482919575 #yiv8482919575activity span a
{color:#5085b6;text-decoration:none;}#yiv8482919575 #yiv8482919575activity span
span {color:#ff7900;}#yiv8482919575 #yiv8482919575activity span
.yiv8482919575underline {text-decoration:underline;}#yiv8482919575
.yiv8482919575attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv8482919575 .yiv8482919575attach div a
{text-decoration:none;}#yiv8482919575 .yiv8482919575attach img
{border:none;padding-right:5px;}#yiv8482919575 .yiv8482919575attach label
{display:block;margin-bottom:5px;}#yiv8482919575 .yiv8482919575attach label a
{text-decoration:none;}#yiv8482919575 blockquote {margin:0 0 0
4px;}#yiv8482919575 .yiv8482919575bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv8482919575
.yiv8482919575bold a {text-decoration:none;}#yiv8482919575
dd...yiv8482919575last p a {font-family:Verdana;font-weight:700;}#yiv8482919575
dd.yiv8482919575last p span
{margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8482919575
dd.yiv8482919575last p span.yiv8482919575yshortcuts
{margin-right:0;}#yiv8482919575 div.yiv8482919575attach-table div div a
{text-decoration:none;}#yiv8482919575 div.yiv8482919575attach-table
{width:400px;}#yiv8482919575 div.yiv8482919575file-title a, #yiv8482919575
div.yiv8482919575file-title a:active, #yiv8482919575
div.yiv8482919575file-title a:hover, #yiv8482919575 div.yiv8482919575file-title
a:visited {text-decoration:none;}#yiv8482919575 div.yiv8482919575photo-title a,
#yiv8482919575 div.yiv8482919575photo-title a:active, #yiv8482919575
div.yiv8482919575photo-title a:hover, #yiv8482919575
div.yiv8482919575photo-title a:visited {text-decoration:none;}#yiv8482919575
div#yiv8482919575ygrp-mlmsg #yiv8482919575ygrp-msg p a
span.yiv8482919575yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8482919575
.yiv8482919575green {color:#628c2a;}#yiv8482919575 .yiv8482919575MsoNormal
{margin:0 0 0 0;}#yiv8482919575 o {font-size:0;}#yiv8482919575
#yiv8482919575photos div {float:left;width:72px;}#yiv8482919575
#yiv8482919575photos div div {border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv8482919575
#yiv8482919575photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8482919575
#yiv8482919575reco-category {font-size:77%;}#yiv8482919575
#yiv8482919575reco-desc {font-size:77%;}#yiv8482919575 .yiv8482919575replbq
{margin:4px;}#yiv8482919575 #yiv8482919575ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv8482919575 #yiv8482919575ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8482919575
#yiv8482919575ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8482919575
#yiv8482919575ygrp-mlmsg select, #yiv8482919575 input, #yiv8482919575 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv8482919575
#yiv8482919575ygrp-mlmsg pre, #yiv8482919575 code {font:115%
monospace;}#yiv8482919575 #yiv8482919575ygrp-mlmsg *
{line-height:1...22em;}#yiv8482919575 #yiv8482919575ygrp-mlmsg
#yiv8482919575logo {padding-bottom:10px;}#yiv8482919575 #yiv8482919575ygrp-msg
p a {font-family:Verdana;}#yiv8482919575 #yiv8482919575ygrp-msg
p#yiv8482919575attach-count span {color:#1E66AE;font-weight:700;}#yiv8482919575
#yiv8482919575ygrp-reco #yiv8482919575reco-head
{color:#ff7900;font-weight:700;}#yiv8482919575 #yiv8482919575ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv8482919575 #yiv8482919575ygrp-sponsor
#yiv8482919575ov li a {font-size:130%;text-decoration:none;}#yiv8482919575
#yiv8482919575ygrp-sponsor #yiv8482919575ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv8482919575
#yiv8482919575ygrp-sponsor #yiv8482919575ov ul {margin:0;padding:0 0 0
8px;}#yiv8482919575 #yiv8482919575ygrp-text
{font-family:Georgia;}#yiv8482919575 #yiv8482919575ygrp-text p {margin:0 0 1em
0;}#yiv8482919575 #yiv8482919575ygrp-text tt {font-size:120%;}#yiv8482919575
#yiv8482919575ygrp-vital ul li:last-child {border-right:none
!important;}#yiv8482919575
