Validasi nilai Saldo Dana

  1. tahun lalu

    Salam para suhu,
    Mohon bisa dibantu bagaimana membuat validasi untuk transaksi yg di input terutama pada kolom KELUAR.
    Agar user diberi peringatan jika saldo transaksi yg bersesuaian dengan (VIA-KATEGORI-SUB KATEGORI) lebih kecil dari angka yang dimasukan User.

    Saya menggunakan pivot table untuk melakukan perekapan saldo perdana, bisa di cek di sheet query01

    Contoh di transaksi no 1708-0010, seharusnya user tidak bisa melakukan input nominal sebesar 700.000, karena sebelum angka 700rb itu dimasukan saldo untuk KAS-ZAKAT-MAAL hanya sisa 600rb

    Apakah kasus ini bisa diselesaikan tanpa macro ?

    File terlampir

  2. saya coba pake validasi, apakah seperti itu mas@mumtaz ?

  3. Caton

    11 Agu 2017 Terverifikasi Indonesia + 12.574 Poin

    Maaf mas @Zolydck... ikutan nimbrung ya ;)

    Konsepnya hampir serupa dengan mas @Zolydck, yakni dengan fitur Data Validation. Namun disini, menurut saya, salah satu cara validasi yang bisa dilakukan tanpa menggunakan macro adalah dengan kolom bantu yang digunakan untuk memeriksa apakah nilai Keluar lebih besar dari nilai Masuk pada Kategori dan Sub Kategori yang sama, dengan syarat range acuannya harus bersifat dinamis.

    Dalam hal ini, saya menggunakan fungsi SUMIFS untuk menjumlahkan nilai Masuk untuk Kategori dan Sub Kategori yang sama pada baris yang sedang diinput. Apabila nilai penjumlahan kolom Masuk lebih kecil dari nilai Keluar yang sedang diinput, maka kolom bantu tersebut (kolom IS_OVER) akan terkalkulasi terlebih dahulu dan akan bernilai TRUE. Akibatnya, validasi terpicu. Keuntungan lainnya, jika ada input data baru dengan Kategori dan Sub Kategori berbeda dari yang telah ada sebelumnya di dalam tabel, dimana nilai yang diinput adalah pada kolom Keluar, otomatis validasi juga akan terpicu, karena belum ada nilai pemasukan (nilai Masuk) terhadap Kategori dan Sub Kategori baru tersebut. Sedangkan untuk membuat range penjumlahannya dinamis, saya menggunakan fungsi INDIRECT, dengan konsep baris range yang dijumlahkan dengan fungsi SUMIFS adalah total seluruh baris yang sudah terisi dikurangi 1 baris (yakni baris terakhir yang sedang diinput). Konsepnya ada pada file terlampir... :)

    Mengapa saya tidak menggunakan data pada Pivot Table (misalnya dengan fungsi GETPIVOTDATA)? Karena jika mengandalkan nilai Pivot Table, repotnya setiap kali ada masukan data baru pada tabel Transaksi, kita harus refresh Pivot Table-nya dahulu agar data up-to-date.

    Bagi rekan-rekan BeORG yang punya solusi lain, mohon dibagi-bagi dong... ;)

  4. mantap mas@Caton , jadi lebih paham..hehe
    tadi sempat bingung juga karena saldonya ga update/ statis.. kirain cuma fungsi validasi aja..hehe
    hasilnya mas@Caton mantap, dibuatin saldo akhirnya juga jadi update.., dan sepertinya saya pernah butuh solusi tersebut sebelumnya..haha

  5. @Zolydck saya coba pake validasi, apakah seperti itu mas@mumtaz ?

    Terimakasih sebelumnya mas @Zolydck , sudah saya coba tapi ketika ditambah transaksi lain dengan jenis VIA-KATEGORI-SUB KATEGORI yang berbeda ternyata tidak berjalan fungsi validasinya

  6. @Caton Maaf mas @Zolydck... ikutan nimbrung ya ;)

    Konsepnya hampir serupa dengan mas @Zolydck, yakni dengan fitur Data Validation. Namun disini, menurut saya, salah satu cara validasi yang bisa dilakukan tanpa menggunakan macro adalah dengan kolom bantu yang digunakan untuk memeriksa apakah nilai Keluar lebih besar dari nilai Masuk pada Kategori dan Sub Kategori yang sama, dengan syarat range acuannya harus bersifat dinamis.

    Dalam hal ini, saya menggunakan fungsi SUMIFS untuk menjumlahkan nilai Masuk untuk Kategori dan Sub Kategori yang sama pada baris yang sedang diinput. Apabila nilai penjumlahan kolom Masuk lebih kecil dari nilai Keluar yang sedang diinput, maka kolom bantu tersebut (kolom IS_OVER) akan terkalkulasi terlebih dahulu dan akan bernilai TRUE. Akibatnya, validasi terpicu. Keuntungan lainnya, jika ada input data baru dengan Kategori dan Sub Kategori berbeda dari yang telah ada sebelumnya di dalam tabel, dimana nilai yang diinput adalah pada kolom Keluar, otomatis validasi juga akan terpicu, karena belum ada nilai pemasukan (nilai Masuk) terhadap Kategori dan Sub Kategori baru tersebut. Sedangkan untuk membuat range penjumlahannya dinamis, saya menggunakan fungsi INDIRECT, dengan konsep baris range yang dijumlahkan dengan fungsi SUMIFS adalah total seluruh baris yang sudah terisi dikurangi 1 baris (yakni baris terakhir yang sedang diinput). Konsepnya ada pada file terlampir... :)

    Mengapa saya tidak menggunakan data pada Pivot Table (misalnya dengan fungsi GETPIVOTDATA)? Karena jika mengandalkan nilai Pivot Table, repotnya setiap kali ada masukan data baru pada tabel Transaksi, kita harus refresh Pivot Table-nya dahulu agar data up-to-date.

    Bagi rekan-rekan BeORG yang punya solusi lain, mohon dibagi-bagi dong... ;)

    Terimakasih juga kepada mas @Caton yang sudah bersusah payah mencarikan solusinya
    namun ketika dicoba dgn transaksi yang lain dengan VIA-KATEGORI-SUB KATEGORI yang berbeda walaupun memiliki nilai yg lebih besar dari saldo dananya VALIDASI tidak berjalan
    contohnya KAS-ZAKAT-PROFESI saya masukan nilai 71.000.000 (posisi saldo di pivot data 70jt)

  7. Caton

    13 Agu 2017 Terverifikasi Indonesia + 12.574 Poin

    Berarti ada yang lupa saya masukkan pada formulanya... ^^; Saya hanya memasukkan jumlah pada kolom Masuk saja, tanpa menguranginya dengan jumlah dari kolom Keluar... ;) Coba modifikasi ulang (atau ubah) formula di kolom IS_OVER dengan formula berikut (dimulai dari sel M8):

    =IF([@KELUAR]<>"";J8>(SUMIFS($I$4:INDIRECT("I"&ROW()-1);$E$4:INDIRECT("E"&ROW()-1);E8;$F$4:INDIRECT("F"&ROW()-1);F8)-SUMIFS($J$4:INDIRECT("J"&ROW()-1);$E$4:INDIRECT("E"&ROW()-1);E8;$F$4:INDIRECT("F"&ROW()-1);F8));"")

    InsyaAllah akan tervalidasi. Jika memang masih ada masalah, silahkan didiskusikan kembali ya mas @Mumtaz... ;)

  8. @Caton Berarti ada yang lupa saya masukkan pada formulanya... ^^; Saya hanya memasukkan jumlah pada kolom Masuk saja, tanpa menguranginya dengan jumlah dari kolom Keluar... ;) Coba modifikasi ulang (atau ubah) formula di kolom IS_OVER dengan formula berikut (dimulai dari sel M8):

    =IF([@KELUAR]<>"";J8>(SUMIFS($I$4:INDIRECT("I"&ROW()-1);$E$4:INDIRECT("E"&ROW()-1);E8;$F$4:INDIRECT("F"&ROW()-1);F8)-SUMIFS($J$4:INDIRECT("J"&ROW()-1);$E$4:INDIRECT("E"&ROW()-1);E8;$F$4:INDIRECT("F"&ROW()-1);F8));"")

    InsyaAllah akan tervalidasi. Jika memang masih ada masalah, silahkan didiskusikan kembali ya mas @Mumtaz... ;)

    Iya mas, tadi saya lihat memang di rumusnya tidak memasukan pengurangnya,
    okeh, sekarang sudah berjalan dgn baik..

 

atau Mendaftar untuk ikut berdiskusi!