Jumat, 30 Januari 2015

[belajar-excel] Digest Number 3283

14 Messages

Digest #3283
1b
Re: VBA Cetak Vlookup nomor 1 sampai dengan.... by "hendrik karnadi" hendrikkarnadi
2a
cara input by "muhamad rizki akbar" rizkiakbar_muhamad
2b
Re: cara input [1 Attachment] by "hendrik karnadi" hendrikkarnadi
3a
Re: Input data untuk protectsheet by "Toby kenzo" tobykenzo
5
Ambil Header dari data by "Agus Erwanto" erwanto_agus
6a
Re: Barcode - Key "Tab" by "sofyan mujit"
8a
Membuat grafik dengan cepat by "Azwar Rhosyied" azwar_rhosyied
8b
Membuat grafik dengan cepat by "Azwar Rhosyied" azwar_rhosyied
9a
10a
pesan warning saat file dibuka by "Contract Mining Assistant, Asam-Asam"
10b
Re: pesan warning saat file dibuka [1 Attachment] by "hendrik karnadi" hendrikkarnadi

Messages

Thu Jan 29, 2015 10:49 pm (PST) . Posted by:

mbah_t

Permisi.. saya coba buat VBA Cetak Vlookup nomor 1 sampai dengan....

Ketentuannya..
Jika Nilai AY < 10.000.000 Maka Yang dicetak sheet 3, 4, dan 5
Jika Nilai AY >= 10.000.000 maka yang dicetak hanya sheet 8
Dari script yang sya bikin ada eror "compile Error Next Without For"
Mohon koreksinya.. :)

Berikut scriptnya


Sub CtkOto()
Dim Mulai As Integer
Dim Sampai As Integer
Mulai = Range("AS34")
Sampai = Range("AS47")
Range("AS2") = Mulai
For i = Mulai To Sampai
If Range("AY26") > 10000000 Then
Sheet3.PrintOut
Sheet4.PrintOut
Sheet5.PrintOut
If Range("AY26") <= 10000000 Then
Sheet8.PrintOut

If Range("AS2") = Sampai Then Exit Sub
Range("AS2") = Range("AS2") + 1
Next
End Sub



Fri Jan 30, 2015 12:20 am (PST) . Posted by:

"hendrik karnadi" hendrikkarnadi

Coba bagian ini: If Range("AY26") <= 10000000 Then
 Sheet8.PrintOut

diganti menjadi:
ElseIf Range("AY26") <= 10000000 Then
        Sheet8.PrintOut
End If
Semoga dapat berjalan dengan baik.
Salam,HK

From: "mbah_t@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
Sent: Wednesday, 28 January 2015, 15:41
Subject: [belajar-excel] VBA Cetak Vlookup nomor 1 sampai dengan....

  Permisi.. saya coba buat VBA Cetak Vlookup nomor 1 sampai dengan....

Ketentuannya..
Jika Nilai AY < 10.000.000 Maka Yang dicetak sheet 3, 4, dan 5
Jika Nilai AY >= 10.000.000 maka yang dicetak hanya sheet 8
Dari script yang sya bikin ada eror "compile Error Next Without For"
Mohon koreksinya.. :)

Berikut scriptnya

Sub CtkOto()
    Dim Mulai As Integer
    Dim Sampai As Integer
    Mulai = Range("AS34")
    Sampai = Range("AS47")
    Range("AS2") = Mulai
    For i = Mulai To Sampai
        If Range("AY26") > 10000000 Then
        Sheet3.PrintOut
        Sheet4.PrintOut
        Sheet5.PrintOut
        If Range("AY26") <= 10000000 Then
        Sheet8.PrintOut
       
        If Range("AS2") = Sampai Then Exit Sub
        Range("AS2") = Range("AS2") + 1
    Next
End Sub

#yiv1852430675 #yiv1852430675 -- #yiv1852430675ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1852430675 #yiv1852430675ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1852430675 #yiv1852430675ygrp-mkp #yiv1852430675hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1852430675 #yiv1852430675ygrp-mkp #yiv1852430675ads {margin-bottom:10px;}#yiv1852430675 #yiv1852430675ygrp-mkp .yiv1852430675ad {padding:0 0;}#yiv1852430675 #yiv1852430675ygrp-mkp .yiv1852430675ad p {margin:0;}#yiv1852430675 #yiv1852430675ygrp-mkp .yiv1852430675ad a {color:#0000ff;text-decoration:none;}#yiv1852430675 #yiv1852430675ygrp-sponsor #yiv1852430675ygrp-lc {font-family:Arial;}#yiv1852430675 #yiv1852430675ygrp-sponsor #yiv1852430675ygrp-lc #yiv1852430675hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1852430675 #yiv1852430675ygrp-sponsor #yiv1852430675ygrp-lc .yiv1852430675ad {margin-bottom:10px;padding:0 0;}#yiv1852430675 #yiv1852430675actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1852430675 #yiv1852430675activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1852430675 #yiv1852430675activity span {font-weight:700;}#yiv1852430675 #yiv1852430675activity span:first-child {text-transform:uppercase;}#yiv1852430675 #yiv1852430675activity span a {color:#5085b6;text-decoration:none;}#yiv1852430675 #yiv1852430675activity span span {color:#ff7900;}#yiv1852430675 #yiv1852430675activity span .yiv1852430675underline {text-decoration:underline;}#yiv1852430675 .yiv1852430675attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1852430675 .yiv1852430675attach div a {text-decoration:none;}#yiv1852430675 .yiv1852430675attach img {border:none;padding-right:5px;}#yiv1852430675 .yiv1852430675attach label {display:block;margin-bottom:5px;}#yiv1852430675 .yiv1852430675attach label a {text-decoration:none;}#yiv1852430675 blockquote {margin:0 0 0 4px;}#yiv1852430675 .yiv1852430675bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1852430675 .yiv1852430675bold a {text-decoration:none;}#yiv1852430675 dd.yiv1852430675last p a {font-family:Verdana;font-weight:700;}#yiv1852430675 dd.yiv1852430675last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1852430675 dd.yiv1852430675last p span.yiv1852430675yshortcuts {margin-right:0;}#yiv1852430675 div.yiv1852430675attach-table div div a {text-decoration:none;}#yiv1852430675 div.yiv1852430675attach-table {width:400px;}#yiv1852430675 div.yiv1852430675file-title a, #yiv1852430675 div.yiv1852430675file-title a:active, #yiv1852430675 div.yiv1852430675file-title a:hover, #yiv1852430675 div.yiv1852430675file-title a:visited {text-decoration:none;}#yiv1852430675 div.yiv1852430675photo-title a, #yiv1852430675 div.yiv1852430675photo-title a:active, #yiv1852430675 div.yiv1852430675photo-title a:hover, #yiv1852430675 div.yiv1852430675photo-title a:visited {text-decoration:none;}#yiv1852430675 div#yiv1852430675ygrp-mlmsg #yiv1852430675ygrp-msg p a span.yiv1852430675yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1852430675 .yiv1852430675green {color:#628c2a;}#yiv1852430675 .yiv1852430675MsoNormal {margin:0 0 0 0;}#yiv1852430675 o {font-size:0;}#yiv1852430675 #yiv1852430675photos div {float:left;width:72px;}#yiv1852430675 #yiv1852430675photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv1852430675 #yiv1852430675photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1852430675 #yiv1852430675reco-category {font-size:77%;}#yiv1852430675 #yiv1852430675reco-desc {font-size:77%;}#yiv1852430675 .yiv1852430675replbq {margin:4px;}#yiv1852430675 #yiv1852430675ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1852430675 #yiv1852430675ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1852430675 #yiv1852430675ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1852430675 #yiv1852430675ygrp-mlmsg select, #yiv1852430675 input, #yiv1852430675 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1852430675 #yiv1852430675ygrp-mlmsg pre, #yiv1852430675 code {font:115% monospace;}#yiv1852430675 #yiv1852430675ygrp-mlmsg * {line-height:1.22em;}#yiv1852430675 #yiv1852430675ygrp-mlmsg #yiv1852430675logo {padding-bottom:10px;}#yiv1852430675 #yiv1852430675ygrp-msg p a {font-family:Verdana;}#yiv1852430675 #yiv1852430675ygrp-msg p#yiv1852430675attach-count span {color:#1E66AE;font-weight:700;}#yiv1852430675 #yiv1852430675ygrp-reco #yiv1852430675reco-head {color:#ff7900;font-weight:700;}#yiv1852430675 #yiv1852430675ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1852430675 #yiv1852430675ygrp-sponsor #yiv1852430675ov li a {font-size:130%;text-decoration:none;}#yiv1852430675 #yiv1852430675ygrp-sponsor #yiv1852430675ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1852430675 #yiv1852430675ygrp-sponsor #yiv1852430675ov ul {margin:0;padding:0 0 0 8px;}#yiv1852430675 #yiv1852430675ygrp-text {font-family:Georgia;}#yiv1852430675 #yiv1852430675ygrp-text p {margin:0 0 1em 0;}#yiv1852430675 #yiv1852430675ygrp-text tt {font-size:120%;}#yiv1852430675 #yiv1852430675ygrp-vital ul li:last-child {border-right:none !important;}#yiv1852430675

Thu Jan 29, 2015 10:49 pm (PST) . Posted by:

"muhamad rizki akbar" rizkiakbar_muhamad

dear all,
mohon bantu yah ane newbi nih, data terlampir
terima kasih & best regard

Thu Jan 29, 2015 11:45 pm (PST) . Posted by:

"hendrik karnadi" hendrikkarnadi

Coba pakai formula =CountA(rangenya) untuk menghitung "Migrasi" dan CountBlank(rangenya) untuk Menghitung "Belum Migrasi".
Untuk Totalnya : =CountA(rangenya) + CountBlank(rangenya).
Salam,HK

From: "muhamad rizki akbar rizkiakbar_muhamad@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
Sent: Wednesday, 28 January 2015, 17:57
Subject: [belajar-excel] cara input [1 Attachment]

  dear all,
mohon bantu yah ane newbi nih, data terlampir
terima kasih & best regard

#yiv4285256994 #yiv4285256994 -- #yiv4285256994ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4285256994 #yiv4285256994ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4285256994 #yiv4285256994ygrp-mkp #yiv4285256994hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv4285256994 #yiv4285256994ygrp-mkp #yiv4285256994ads {margin-bottom:10px;}#yiv4285256994 #yiv4285256994ygrp-mkp .yiv4285256994ad {padding:0 0;}#yiv4285256994 #yiv4285256994ygrp-mkp .yiv4285256994ad p {margin:0;}#yiv4285256994 #yiv4285256994ygrp-mkp .yiv4285256994ad a {color:#0000ff;text-decoration:none;}#yiv4285256994 #yiv4285256994ygrp-sponsor #yiv4285256994ygrp-lc {font-family:Arial;}#yiv4285256994 #yiv4285256994ygrp-sponsor #yiv4285256994ygrp-lc #yiv4285256994hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4285256994 #yiv4285256994ygrp-sponsor #yiv4285256994ygrp-lc .yiv4285256994ad {margin-bottom:10px;padding:0 0;}#yiv4285256994 #yiv4285256994actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4285256994 #yiv4285256994activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4285256994 #yiv4285256994activity span {font-weight:700;}#yiv4285256994 #yiv4285256994activity span:first-child {text-transform:uppercase;}#yiv4285256994 #yiv4285256994activity span a {color:#5085b6;text-decoration:none;}#yiv4285256994 #yiv4285256994activity span span {color:#ff7900;}#yiv4285256994 #yiv4285256994activity span .yiv4285256994underline {text-decoration:underline;}#yiv4285256994 .yiv4285256994attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv4285256994 .yiv4285256994attach div a {text-decoration:none;}#yiv4285256994 .yiv4285256994attach img {border:none;padding-right:5px;}#yiv4285256994 .yiv4285256994attach label {display:block;margin-bottom:5px;}#yiv4285256994 .yiv4285256994attach label a {text-decoration:none;}#yiv4285256994 blockquote {margin:0 0 0 4px;}#yiv4285256994 .yiv4285256994bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv4285256994 .yiv4285256994bold a {text-decoration:none;}#yiv4285256994 dd.yiv4285256994last p a {font-family:Verdana;font-weight:700;}#yiv4285256994 dd.yiv4285256994last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4285256994 dd.yiv4285256994last p span.yiv4285256994yshortcuts {margin-right:0;}#yiv4285256994 div.yiv4285256994attach-table div div a {text-decoration:none;}#yiv4285256994 div.yiv4285256994attach-table {width:400px;}#yiv4285256994 div.yiv4285256994file-title a, #yiv4285256994 div.yiv4285256994file-title a:active, #yiv4285256994 div.yiv4285256994file-title a:hover, #yiv4285256994 div.yiv4285256994file-title a:visited {text-decoration:none;}#yiv4285256994 div.yiv4285256994photo-title a, #yiv4285256994 div.yiv4285256994photo-title a:active, #yiv4285256994 div.yiv4285256994photo-title a:hover, #yiv4285256994 div.yiv4285256994photo-title a:visited {text-decoration:none;}#yiv4285256994 div#yiv4285256994ygrp-mlmsg #yiv4285256994ygrp-msg p a span.yiv4285256994yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4285256994 .yiv4285256994green {color:#628c2a;}#yiv4285256994 .yiv4285256994MsoNormal {margin:0 0 0 0;}#yiv4285256994 o {font-size:0;}#yiv4285256994 #yiv4285256994photos div {float:left;width:72px;}#yiv4285256994 #yiv4285256994photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv4285256994 #yiv4285256994photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4285256994 #yiv4285256994reco-category {font-size:77%;}#yiv4285256994 #yiv4285256994reco-desc {font-size:77%;}#yiv4285256994 .yiv4285256994replbq {margin:4px;}#yiv4285256994 #yiv4285256994ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv4285256994 #yiv4285256994ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4285256994 #yiv4285256994ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4285256994 #yiv4285256994ygrp-mlmsg select, #yiv4285256994 input, #yiv4285256994 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv4285256994 #yiv4285256994ygrp-mlmsg pre, #yiv4285256994 code {font:115% monospace;}#yiv4285256994 #yiv4285256994ygrp-mlmsg * {line-height:1.22em;}#yiv4285256994 #yiv4285256994ygrp-mlmsg #yiv4285256994logo {padding-bottom:10px;}#yiv4285256994 #yiv4285256994ygrp-msg p a {font-family:Verdana;}#yiv4285256994 #yiv4285256994ygrp-msg p#yiv4285256994attach-count span {color:#1E66AE;font-weight:700;}#yiv4285256994 #yiv4285256994ygrp-reco #yiv4285256994reco-head {color:#ff7900;font-weight:700;}#yiv4285256994 #yiv4285256994ygrp-reco {margin-bottom:20px;padding:0px;}#yiv4285256994 #yiv4285256994ygrp-sponsor #yiv4285256994ov li a {font-size:130%;text-decoration:none;}#yiv4285256994 #yiv4285256994ygrp-sponsor #yiv4285256994ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv4285256994 #yiv4285256994ygrp-sponsor #yiv4285256994ov ul {margin:0;padding:0 0 0 8px;}#yiv4285256994 #yiv4285256994ygrp-text {font-family:Georgia;}#yiv4285256994 #yiv4285256994ygrp-text p {margin:0 0 1em 0;}#yiv4285256994 #yiv4285256994ygrp-text tt {font-size:120%;}#yiv4285256994 #yiv4285256994ygrp-vital ul li:last-child {border-right:none !important;}#yiv4285256994

Thu Jan 29, 2015 10:50 pm (PST) . Posted by:

"Toby kenzo" tobykenzo

Dear Jack,

Untuk memasukan data pada sheet yg terprotect, anda bisa menambahkan pada formula unprotect di awal program vba tombol input dan di akhiri dengan formula protect.
Jika sheet yg akan si masukan data di protect menggunakan password, kamu tambahkan pada formula unprotect dan protect de, misal:

Private sub input_Click()

Sheet1.Activate.  (Lokasi data yg ingin di inputkan)
Sheet1.Unprotect ("your password").
">>>>>your program

Sheet1.Protect ("your password"

Semoga membantu.

TerimaKasih.

Sent from Yahoo Mail on Android

Thu Jan 29, 2015 10:50 pm (PST) . Posted by:

"Jack Carmelo"


Mas Hendrik
Makasih atas masukkannya. Tapi disitulah letak masalahnya. Mungkin ada code yang memaksa excel menginput data pada protect sheet. Dengan tetap membiarkan status sheetnya di protect. Ceritanya inputan data dari useform.
Karena saya ingin supaya user hanya boleh menginput data. Yang ditakutkan tiba2 ada edit yang diluar kehendak pada sheet excel.
Makasih atas bantuannya.
--
Salam Sehat Selalu Dari Papua Timika
Jack
Rabu, 28 Januari 2015, 10:08PM +0900 dari "hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com>:
 
Maaf, kurang teliti menterjemahkan pertanyaan,
jika yang diinginkan sheet hanya bisa dilihat tapi tidak bisa diapa2kan maka :
- Sheet harus diproteksi dengan password
-  Sebelum data diinput ke sheet tersebut, buka proteksi sheet dengan macro
-  bla bla bla --> perintah untuk menginput data ke sheet tersebut
-  Setelah  data diinput ke sheet tersebut, protek kembali sheet dengan macro
- macronya diprotek dengan password agar tidak bisa diapa2kan, kecuali user memang ahli bongkar password.
Salam,
HK
----- Forwarded Message -----
From: "hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
Sent: Wednesday, 28 January 2015, 16:09
Subject: Re: [belajar-excel] Input data untuk protectsheet
 
Untuk menampilkan Sheet penampungnya kembali..
2-xlsheetVeryHidden   diganti dengan  -1-xlsheetVisible .
Salam,
HK
----------------------------------------------------------
From: "hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
Sent: Wednesday, 28 January 2015, 15:57
Subject: Re: [belajar-excel] Input data untuk protectsheet [1 Attachment]
 
Hai Jack,
Bisa!
Caranya:
- Tekan Alt F11 secara bersamaan.
- Pilih Sheet penampung yang mau diprotect pada VBE Sheet (bagian sebelah kiri).
- Klik View, Properties Window.
- Cari "Visible" pada bagian bawah.
-  pilih 2-xlsheetVeryHidden .
- Protect VBE Sheetnya.
- Simpan File, kemudian coba buka lagi... Sheet dan VBE Sheetnya sdh terprotect.
Untuk uji coba, terlampir file modifikasi dari Kopdar2 (pwd VBE Sheet: "hk")
Selamat mencoba.
Salam,
HK
----------------------------------------------------------
From: "Jack Carmelo jackcarmelo@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
Sent: Wednesday, 28 January 2015, 12:52
Subject: [belajar-excel] Input data untuk protectsheet
 
Mat sore para master dan pecinta excel smua..
Sekedar tanya saja, klo jawabannya ya langsung mohon solusinya.. Hahaha
Saya punya data yang diinput dari userform.  Sheet yang terinput saya protect dengan tujuan agar hanya bisa dilihat tapi tidak diapa2kan .
Tapi sayangnya setelah di protect malah tidak bisa diinput karena debug di userform. Dengan alasan protect tadi.
Pertanyaannya adakah cara input atau perintah yang memaksa excel menginput data meskipun diprotect?
Sebelumnya makasih atas jawabannya.
--
Salam Sehat Selalu Dari Papua Timika
Jack

Thu Jan 29, 2015 10:50 pm (PST) . Posted by:

"Agus Erwanto" erwanto_agus

Dear Team Excel,

selamat siang,
mohon bantuannya untuk kasus saya dibawah ini.

saya ingin mengandengkan header header yang ada datanya, tidak masalah itu data berisi anggak 1, 2, dll
jadi kalau ada data langsung diambil headernya.

utnuk keterangan lebih lanjut saya sertakan attachmentnya.

regards,

terimakasih

agus erw

Thu Jan 29, 2015 10:50 pm (PST) . Posted by:

"sofyan mujit"

Utk saat ini scanner sepertinya hanya bisa capture data barcode saja.
Memang Biasanya qty di input manual. Utk konsep nang agus bisa di terapkan
utk sku(stok keeping unit) internal barcode. Kemungkinan tdk bisa Utk
universal kode (kode pabrikan).
Pada 29 Jan 2015 12:51, "Nang Agus nangagus@gmail.com [belajar-excel]" <
belajar-excel@yahoogroups.com> menulis:

>
>
> Untuk Mas Heri
>
> Walaikumsalam warahmatullah,
>
> Bagaimana kalau barcodenya dipisah jadi dua misalnya 8 digit dimuka untuk
> nomor id dan 4 digit di belakang jadi Quantity.
> Jika barcode di scan dapat langsung masuk di satu cell excel.
> pada dua cell berikutnya dapat dipecah dari cell yang pertama menjadi ID
> dan Quantity.
> saya pikir dengan barcode EAN128 dapat menampung sampai 20 digit.
> bergantung scan yang dipakai.
> selamat mencoba
>
> wasalam
> NangAgus
>
> 2015-01-29 10:43 GMT+07:00 'Heri Pamungkas ( Gmail )'
> ghery.dharma@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>>

>>
>>
>> --
>> السَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
>> Semangat pagi,.
>>
>> Dear Rekan-rekan.
>>
>> Mohon maaf kalau agak OOT.
>>
>> Saya punya problem pengaplikasian Barcode,
>> Dimana "Pak Bos" mintanya sekali barcode Sudah Entry ID dan Quantity.
>>
>> Dilemanya, system kami Itu ID dan Quantity ada di kolom yang berbeda
>> sehingga membutuhkan Key TAB untuk menggeser ke kolom yang dimakhsud.
>>
>> Mungkin ada saran atau pengalaman yang bisa di share.
>>
>> Hiks Hiks....
>>
>> وَالسَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
>>
>> Warm Regards,
>>
>>
>
>

Thu Jan 29, 2015 10:50 pm (PST) . Posted by:

"Marvell Christian"

Tks Mr. Kid atas bantuannya.


Pada Senin, 26 Januari 2015 10:04, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Hai MCS,

File terlampir dengan kolom bantu pemberi nilai status sebagai item yang mewakili record yang sama.

Wassalam,
Kid.

2015-01-26 12:41 GMT+11:00 Marvell Christian marvellchristian@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  Dear BeExceller,Mohon bantuannya untuk menghitung rata-rata di excel dengan averageifs dimana data yang sama hanya dihitung satu untuk dirata-ratakan (contoh terlampir). Terima kasih sebelumnya atas bantuannya.
Rgds,MCS


#yiv4064072251 #yiv4064072251 -- #yiv4064072251ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4064072251 #yiv4064072251ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4064072251 #yiv4064072251ygrp-mkp #yiv4064072251hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv4064072251 #yiv4064072251ygrp-mkp #yiv4064072251ads {margin-bottom:10px;}#yiv4064072251 #yiv4064072251ygrp-mkp .yiv4064072251ad {padding:0 0;}#yiv4064072251 #yiv4064072251ygrp-mkp .yiv4064072251ad p {margin:0;}#yiv4064072251 #yiv4064072251ygrp-mkp .yiv4064072251ad a {color:#0000ff;text-decoration:none;}#yiv4064072251 #yiv4064072251ygrp-sponsor #yiv4064072251ygrp-lc {font-family:Arial;}#yiv4064072251 #yiv4064072251ygrp-sponsor #yiv4064072251ygrp-lc #yiv4064072251hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4064072251 #yiv4064072251ygrp-sponsor #yiv4064072251ygrp-lc .yiv4064072251ad {margin-bottom:10px;padding:0 0;}#yiv4064072251 #yiv4064072251actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4064072251 #yiv4064072251activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4064072251 #yiv4064072251activity span {font-weight:700;}#yiv4064072251 #yiv4064072251activity span:first-child {text-transform:uppercase;}#yiv4064072251 #yiv4064072251activity span a {color:#5085b6;text-decoration:none;}#yiv4064072251 #yiv4064072251activity span span {color:#ff7900;}#yiv4064072251 #yiv4064072251activity span .yiv4064072251underline {text-decoration:underline;}#yiv4064072251 .yiv4064072251attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv4064072251 .yiv4064072251attach div a {text-decoration:none;}#yiv4064072251 .yiv4064072251attach img {border:none;padding-right:5px;}#yiv4064072251 .yiv4064072251attach label {display:block;margin-bottom:5px;}#yiv4064072251 .yiv4064072251attach label a {text-decoration:none;}#yiv4064072251 blockquote {margin:0 0 0 4px;}#yiv4064072251 .yiv4064072251bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv4064072251 .yiv4064072251bold a {text-decoration:none;}#yiv4064072251 dd.yiv4064072251last p a {font-family:Verdana;font-weight:700;}#yiv4064072251 dd.yiv4064072251last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4064072251 dd.yiv4064072251last p span.yiv4064072251yshortcuts {margin-right:0;}#yiv4064072251 div.yiv4064072251attach-table div div a {text-decoration:none;}#yiv4064072251 div.yiv4064072251attach-table {width:400px;}#yiv4064072251 div.yiv4064072251file-title a, #yiv4064072251 div.yiv4064072251file-title a:active, #yiv4064072251 div.yiv4064072251file-title a:hover, #yiv4064072251 div.yiv4064072251file-title a:visited {text-decoration:none;}#yiv4064072251 div.yiv4064072251photo-title a, #yiv4064072251 div.yiv4064072251photo-title a:active, #yiv4064072251 div.yiv4064072251photo-title a:hover, #yiv4064072251 div.yiv4064072251photo-title a:visited {text-decoration:none;}#yiv4064072251 div#yiv4064072251ygrp-mlmsg #yiv4064072251ygrp-msg p a span.yiv4064072251yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4064072251 .yiv4064072251green {color:#628c2a;}#yiv4064072251 .yiv4064072251MsoNormal {margin:0 0 0 0;}#yiv4064072251 o {font-size:0;}#yiv4064072251 #yiv4064072251photos div {float:left;width:72px;}#yiv4064072251 #yiv4064072251photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv4064072251 #yiv4064072251photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4064072251 #yiv4064072251reco-category {font-size:77%;}#yiv4064072251 #yiv4064072251reco-desc {font-size:77%;}#yiv4064072251 .yiv4064072251replbq {margin:4px;}#yiv4064072251 #yiv4064072251ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv4064072251 #yiv4064072251ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4064072251 #yiv4064072251ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4064072251 #yiv4064072251ygrp-mlmsg select, #yiv4064072251 input, #yiv4064072251 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv4064072251 #yiv4064072251ygrp-mlmsg pre, #yiv4064072251 code {font:115% monospace;}#yiv4064072251 #yiv4064072251ygrp-mlmsg * {line-height:1.22em;}#yiv4064072251 #yiv4064072251ygrp-mlmsg #yiv4064072251logo {padding-bottom:10px;}#yiv4064072251 #yiv4064072251ygrp-msg p a {font-family:Verdana;}#yiv4064072251 #yiv4064072251ygrp-msg p#yiv4064072251attach-count span {color:#1E66AE;font-weight:700;}#yiv4064072251 #yiv4064072251ygrp-reco #yiv4064072251reco-head {color:#ff7900;font-weight:700;}#yiv4064072251 #yiv4064072251ygrp-reco {margin-bottom:20px;padding:0px;}#yiv4064072251 #yiv4064072251ygrp-sponsor #yiv4064072251ov li a {font-size:130%;text-decoration:none;}#yiv4064072251 #yiv4064072251ygrp-sponsor #yiv4064072251ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv4064072251 #yiv4064072251ygrp-sponsor #yiv4064072251ov ul {margin:0;padding:0 0 0 8px;}#yiv4064072251 #yiv4064072251ygrp-text {font-family:Georgia;}#yiv4064072251 #yiv4064072251ygrp-text p {margin:0 0 1em 0;}#yiv4064072251 #yiv4064072251ygrp-text tt {font-size:120%;}#yiv4064072251 #yiv4064072251ygrp-vital ul li:last-child {border-right:none !important;}#yiv4064072251

Thu Jan 29, 2015 10:51 pm (PST) . Posted by:

"Azwar Rhosyied" azwar_rhosyied

Dear Master,
Mohon share ilmunya tentang membuat grafik excel dengan cepat. Contoh : Saya punya data di workbook berbeda dengan nama Nilai 1, Nilai 2 dan Nilai 3. Masing2 memiliki data yang berbeda NAMUN dengan susunan kolom dan baris yang sama. 
Bagaimana caranya membuat grafik dengan cara tidak perlu membuat satu2 untuk masing2 workbook tersebut?
Terima kasih.Salam
Rhosyied

Thu Jan 29, 2015 10:56 pm (PST) . Posted by:

"Azwar Rhosyied" azwar_rhosyied

Dear Master,
Mohon share ilmunya tentang membuat grafik excel dengan cepat. Contoh : Saya punya data di workbook berbeda dengan nama Nilai 1, Nilai 2 dan Nilai 3. Masing2 memiliki data yang berbeda NAMUN dengan susunan kolom dan baris yang sama. 
Bagaimana caranya membuat grafik dengan cara tidak perlu membuat satu2 untuk masing2 workbook tersebut?
Terima kasih.Salam
Rhosyied

Thu Jan 29, 2015 10:57 pm (PST) . Posted by:

"Febrian Yoshi"

Dear Mr. Kid,

Terima kasih banyak ya..

Tapi maaf setelah sy ganti formulanya kok malah error ya?

Terlampir sy emailkan format tabel yang sebenarnya.

Mohon bantuannya kembali ya..

Rgds,

Fefe

Pada 26 Januari 2015 09.50, 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <
belajar-excel@yahoogroups.com> menulis:

>
>
> ops formula cell K4 kurang +1 :
> =IFERROR(VLOOKUP(H4,$B$39:$K$
>
> 42,MATCH(E4,$B$37:$K$37,0),0),VLOOKUP(H4,$B$39:$I$42,(COLUMN()-COLUMN($K$3))*2+2
> +1+(E4=$D$38),0))
>
> Bagian :
> (COLUMN()-COLUMN($K$3))*2 -> nomor 1 (satu) kolom sebelum blok bulan
> terkait (satu kolom sebelum blok bulan oct, nov, dst)
> +2 -> jumlah kolom sejak kolom pertama array lookup sampai kolom pertama
> blok per bulan
> +1 -> untuk berpindah ke kolom Counter (selalu)
> +(E4=$D$38) -> untuk berpindah ke kolom setelah Counter jika terpenuhi
>
> Bagian :
> (COLUMN()-COLUMN($K$3))*2+2+1+(E4=$D$38)
> bisa diganti dengan :
> ( column(a1)-1 ) * 2 + 3 + (E4=$D$38)
>
> Wassalam,
> Kid.
>
>
>
> On Sun, Jan 25, 2015 at 7:27 AM, Mr. Kid <mr.nmkid@gmail.com> wrote:
>
>> Hai Fefe,
>>
>> cell K4 :
>>
>> =IFERROR(VLOOKUP(H4,$B$39:$K$42,MATCH(E4,$B$37:$K$37,0),0),VLOOKUP(H4,$B$39:$I$42,(COLUMN()-COLUMN($K$3))*2+2+(E4=$D$38),0))
>>
>> cell N4 :
>> =IFERROR(VLOOKUP($D4,$B$47:$F$50,MATCH(K4,$D$46:$F$46,0)+2,0),0)
>>
>> Wassalam,
>> Kid.
>>
>>
>>
>>
>> 2015-01-24 14:55 GMT+11:00 Febrian Yoshi febrixl@gmail.com
>> [belajar-excel] <belajar-excel@yahoogroups.com>:
>>
>>>
>>>
>>> Dear All Master,
>>> Mohon bantuannya untuk formula menghitung insentif seperti file
>>> terlampir.
>>> Terima kasih atas bantuannya ya..
>>>
>>> Rgds,
>>> Fefe
>>>
>>>
>>
>
>

Thu Jan 29, 2015 11:11 pm (PST) . Posted by:

"Contract Mining Assistant, Asam-Asam"

Dear Milis Belajar Excel,
Saya mempunyai masalah yaitu pada saat membuka salah satu file excel. Ada message box warning yang tampil.
Mohon bagi yang mengerti bisa menjelaskan maksud dari pesan tsb serta penanganannya.

Terima kasih sebelumnya.

Salam, Rasyid
Disclaimer - This message including any attachments and information contained
herein ("Message"; ) may contain privileged information or otherwise is protected
from disclosure. Any unauthorized use of this Message by any person may lead to
legal consequences. If you receive this Message in error, or if the recipient
of this Message is not the intended recipient or the employee or agent authorized
for delivering this Message to the intended recipient you are hereby notified that
any disclosure, dissemination, distribution or copying of this Message or let or
cause this Message to be disclosed,disseminated, distributed, or copied is strictly
prohibited, and please notify the sender by return message and immediately delete
this Message from your system. Incoming and outgoing communications using this
electronic mail may be monitored by PT Arutmin Indonesia,as permitted by applicable
law and regulations. Unless it is made by the authorized person,any views expressed
in this Message are those of the individual sender and may not necessarily reflect
the views of PT Arutmin Indonesia.
You should check attachments for viruses and defects before opening or using them.
We are not liable to recover any injuries caused by virused or defected attachments.

Thu Jan 29, 2015 11:30 pm (PST) . Posted by:

"hendrik karnadi" hendrikkarnadi

Hai Rasyid,Peringatan tersebut terjadi karena adanya formula yang "tumpang tindih".
Sebagai contoh:Ketik A1 dengan sembarang angka.Ketik B1 dengan sembarang angka.Ketik formula pada C1, C1 = A1 + B1
Kemudian ganti angka pada sel B1 dengan formula, B1 = C1 - A1
Maka antara B1 dan C1 terjadi formula yang "tumpang tindih".
Untuk memperbaikinya klik Formula, Show Formulas.
Salam,HK

From: "'Contract Mining Assistant, Asam-Asam' apassistant@arutmin.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
Cc: "Contract Mining Assistant, Asam-Asam" <apassistant@arutmin.com>
Sent: Friday, 30 January 2015, 13:41
Subject: [belajar-excel] pesan warning saat file dibuka [1 Attachment]

  Dear Milis Belajar Excel, Saya mempunyai masalah yaitu pada saat membuka salah satu file excel. Ada message box warning yang tampil. Mohon bagi yang mengerti bisa menjelaskan maksud dari pesan tsb serta penanganannya.   Terima kasih sebelumnya.   Salam, Rasyid Disclaimer  -  This  message  including any attachments and information contained
herein  ("Message"; ) may contain  privileged information  or otherwise is protected
from  disclosure. Any  unauthorized use of this  Message  by any person may lead to
legal consequences.  If  you  receive this  Message  in error, or if the recipient
of this  Message  is not the intended recipient or the employee or agent authorized
for delivering this  Message to the intended recipient you are hereby notified that
any  disclosure,  dissemination, distribution  or copying of this Message or let or
cause this Message to be disclosed,disseminated, distributed, or copied is strictly
prohibited, and please notify the sender by  return message  and immediately delete
this  Message  from your system.  Incoming and outgoing  communications using this
electronic mail may be monitored by PT Arutmin Indonesia,as permitted by applicable
law and regulations. Unless it is made by the authorized person,any views expressed
in this Message  are those of the individual sender and may not necessarily reflect
the views of PT Arutmin Indonesia.
You should check attachments for viruses and defects before opening or using them.
We are not liable to recover any injuries caused by virused or defected attachments. #yiv2396340059 #yiv2396340059 -- #yiv2396340059ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2396340059 #yiv2396340059ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2396340059 #yiv2396340059ygrp-mkp #yiv2396340059hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv2396340059 #yiv2396340059ygrp-mkp #yiv2396340059ads {margin-bottom:10px;}#yiv2396340059 #yiv2396340059ygrp-mkp .yiv2396340059ad {padding:0 0;}#yiv2396340059 #yiv2396340059ygrp-mkp .yiv2396340059ad p {margin:0;}#yiv2396340059 #yiv2396340059ygrp-mkp .yiv2396340059ad a {color:#0000ff;text-decoration:none;}#yiv2396340059 #yiv2396340059ygrp-sponsor #yiv2396340059ygrp-lc {font-family:Arial;}#yiv2396340059 #yiv2396340059ygrp-sponsor #yiv2396340059ygrp-lc #yiv2396340059hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2396340059 #yiv2396340059ygrp-sponsor #yiv2396340059ygrp-lc .yiv2396340059ad {margin-bottom:10px;padding:0 0;}#yiv2396340059 #yiv2396340059actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2396340059 #yiv2396340059activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2396340059 #yiv2396340059activity span {font-weight:700;}#yiv2396340059 #yiv2396340059activity span:first-child {text-transform:uppercase;}#yiv2396340059 #yiv2396340059activity span a {color:#5085b6;text-decoration:none;}#yiv2396340059 #yiv2396340059activity span span {color:#ff7900;}#yiv2396340059 #yiv2396340059activity span .yiv2396340059underline {text-decoration:underline;}#yiv2396340059 .yiv2396340059attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv2396340059 .yiv2396340059attach div a {text-decoration:none;}#yiv2396340059 .yiv2396340059attach img {border:none;padding-right:5px;}#yiv2396340059 .yiv2396340059attach label {display:block;margin-bottom:5px;}#yiv2396340059 .yiv2396340059attach label a {text-decoration:none;}#yiv2396340059 blockquote {margin:0 0 0 4px;}#yiv2396340059 .yiv2396340059bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv2396340059 .yiv2396340059bold a {text-decoration:none;}#yiv2396340059 dd.yiv2396340059last p a {font-family:Verdana;font-weight:700;}#yiv2396340059 dd.yiv2396340059last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2396340059 dd.yiv2396340059last p span.yiv2396340059yshortcuts {margin-right:0;}#yiv2396340059 div.yiv2396340059attach-table div div a {text-decoration:none;}#yiv2396340059 div.yiv2396340059attach-table {width:400px;}#yiv2396340059 div.yiv2396340059file-title a, #yiv2396340059 div.yiv2396340059file-title a:active, #yiv2396340059 div.yiv2396340059file-title a:hover, #yiv2396340059 div.yiv2396340059file-title a:visited {text-decoration:none;}#yiv2396340059 div.yiv2396340059photo-title a, #yiv2396340059 div.yiv2396340059photo-title a:active, #yiv2396340059 div.yiv2396340059photo-title a:hover, #yiv2396340059 div.yiv2396340059photo-title a:visited {text-decoration:none;}#yiv2396340059 div#yiv2396340059ygrp-mlmsg #yiv2396340059ygrp-msg p a span.yiv2396340059yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2396340059 .yiv2396340059green {color:#628c2a;}#yiv2396340059 .yiv2396340059MsoNormal {margin:0 0 0 0;}#yiv2396340059 o {font-size:0;}#yiv2396340059 #yiv2396340059photos div {float:left;width:72px;}#yiv2396340059 #yiv2396340059photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv2396340059 #yiv2396340059photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2396340059 #yiv2396340059reco-category {font-size:77%;}#yiv2396340059 #yiv2396340059reco-desc {font-size:77%;}#yiv2396340059 .yiv2396340059replbq {margin:4px;}#yiv2396340059 #yiv2396340059ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv2396340059 #yiv2396340059ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2396340059 #yiv2396340059ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2396340059 #yiv2396340059ygrp-mlmsg select, #yiv2396340059 input, #yiv2396340059 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv2396340059 #yiv2396340059ygrp-mlmsg pre, #yiv2396340059 code {font:115% monospace;}#yiv2396340059 #yiv2396340059ygrp-mlmsg * {line-height:1.22em;}#yiv2396340059 #yiv2396340059ygrp-mlmsg #yiv2396340059logo {padding-bottom:10px;}#yiv2396340059 #yiv2396340059ygrp-msg p a {font-family:Verdana;}#yiv2396340059 #yiv2396340059ygrp-msg p#yiv2396340059attach-count span {color:#1E66AE;font-weight:700;}#yiv2396340059 #yiv2396340059ygrp-reco #yiv2396340059reco-head {color:#ff7900;font-weight:700;}#yiv2396340059 #yiv2396340059ygrp-reco {margin-bottom:20px;padding:0px;}#yiv2396340059 #yiv2396340059ygrp-sponsor #yiv2396340059ov li a {font-size:130%;text-decoration:none;}#yiv2396340059 #yiv2396340059ygrp-sponsor #yiv2396340059ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv2396340059 #yiv2396340059ygrp-sponsor #yiv2396340059ov ul {margin:0;padding:0 0 0 8px;}#yiv2396340059 #yiv2396340059ygrp-text {font-family:Georgia;}#yiv2396340059 #yiv2396340059ygrp-text p {margin:0 0 1em 0;}#yiv2396340059 #yiv2396340059ygrp-text tt {font-size:120%;}#yiv2396340059 #yiv2396340059ygrp-vital ul li:last-child {border-right:none !important;}#yiv2396340059

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