Selasa, 24 November 2015

[belajar-excel] Digest Number 3655

15 Messages

Digest #3655
1b
Re: protect sheet tapi borders tetap aktif by "Mr. Kid" nmkid.family@ymail.com
2a
Re: Conditional Formatting [1 Attachment] by "Mr. Kid" nmkid.family@ymail.com
2b
Re: Conditional Formatting by "Fendik Anton Wijaya"
3a
3c
Re: COPY PASTE VALUE OTOMATIS ANTAR SHEET by "Mr. Kid" nmkid.family@ymail.com
4a
Re: filter data by "Mr. Kid" nmkid.family@ymail.com
4b
Re: filter data by fathianm
4c
Re: filter data [1 Attachment] by "Mr. Kid" nmkid.family@ymail.com
5a
Re: Pengaturan dengn LIST Button [2 Attachments] by "Mr. Kid" nmkid.family@ymail.com
6a
Re: konsversi satuan [1 Attachment] by "Mr. Kid" nmkid.family@ymail.com

Messages

Tue Nov 24, 2015 5:01 am (PST) . Posted by:

"marone oke" maroneoke940

tengkyu mr. kid
ada lagi nih...saya mau protect sheet...tapi freeze panes aktif...gimana ya..
tks


Pada Selasa, 24 November 2015 6:36, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Hai Marone,

Manualnya begini :
1. ribbon Review -> group Changes -> Protect Sheet -> muncul dialog window protect sheet
2. isi password (bila perlu) -> centang yang diperbolehkan -> tekan OK
     untuk bisa border, berarti format cells yang di centang. Tapi, segala macam format cells bisa dilakukan.

Jika ingin tahu kode VBA nya, lakukan cara manual tersebut saat proses record macro dijalankan, alias lakukan macro recording

Regards,
Kid

On Mon, Nov 23, 2015 at 6:12 PM, marone oke maroneoke940@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:

  dear master,,
tolong code macro, protect sheet tapi borders tetap aktif.....
tks bantuannya


#yiv5606556196 #yiv5606556196 -- #yiv5606556196ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5606556196 #yiv5606556196ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5606556196 #yiv5606556196ygrp-mkp #yiv5606556196hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5606556196 #yiv5606556196ygrp-mkp #yiv5606556196ads {margin-bottom:10px;}#yiv5606556196 #yiv5606556196ygrp-mkp .yiv5606556196ad {padding:0 0;}#yiv5606556196 #yiv5606556196ygrp-mkp .yiv5606556196ad p {margin:0;}#yiv5606556196 #yiv5606556196ygrp-mkp .yiv5606556196ad a {color:#0000ff;text-decoration:none;}#yiv5606556196 #yiv5606556196ygrp-sponsor #yiv5606556196ygrp-lc {font-family:Arial;}#yiv5606556196 #yiv5606556196ygrp-sponsor #yiv5606556196ygrp-lc #yiv5606556196hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5606556196 #yiv5606556196ygrp-sponsor #yiv5606556196ygrp-lc .yiv5606556196ad {margin-bottom:10px;padding:0 0;}#yiv5606556196 #yiv5606556196actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5606556196 #yiv5606556196activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5606556196 #yiv5606556196activity span {font-weight:700;}#yiv5606556196 #yiv5606556196activity span:first-child {text-transform:uppercase;}#yiv5606556196 #yiv5606556196activity span a {color:#5085b6;text-decoration:none;}#yiv5606556196 #yiv5606556196activity span span {color:#ff7900;}#yiv5606556196 #yiv5606556196activity span .yiv5606556196underline {text-decoration:underline;}#yiv5606556196 .yiv5606556196attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv5606556196 .yiv5606556196attach div a {text-decoration:none;}#yiv5606556196 .yiv5606556196attach img {border:none;padding-right:5px;}#yiv5606556196 .yiv5606556196attach label {display:block;margin-bottom:5px;}#yiv5606556196 .yiv5606556196attach label a {text-decoration:none;}#yiv5606556196 blockquote {margin:0 0 0 4px;}#yiv5606556196 .yiv5606556196bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv5606556196 .yiv5606556196bold a {text-decoration:none;}#yiv5606556196 dd.yiv5606556196last p a {font-family:Verdana;font-weight:700;}#yiv5606556196 dd.yiv5606556196last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5606556196 dd.yiv5606556196last p span.yiv5606556196yshortcuts {margin-right:0;}#yiv5606556196 div.yiv5606556196attach-table div div a {text-decoration:none;}#yiv5606556196 div.yiv5606556196attach-table {width:400px;}#yiv5606556196 div.yiv5606556196file-title a, #yiv5606556196 div.yiv5606556196file-title a:active, #yiv5606556196 div.yiv5606556196file-title a:hover, #yiv5606556196 div.yiv5606556196file-title a:visited {text-decoration:none;}#yiv5606556196 div.yiv5606556196photo-title a, #yiv5606556196 div.yiv5606556196photo-title a:active, #yiv5606556196 div.yiv5606556196photo-title a:hover, #yiv5606556196 div.yiv5606556196photo-title a:visited {text-decoration:none;}#yiv5606556196 div#yiv5606556196ygrp-mlmsg #yiv5606556196ygrp-msg p a span.yiv5606556196yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5606556196 .yiv5606556196green {color:#628c2a;}#yiv5606556196 .yiv5606556196MsoNormal {margin:0 0 0 0;}#yiv5606556196 o {font-size:0;}#yiv5606556196 #yiv5606556196photos div {float:left;width:72px;}#yiv5606556196 #yiv5606556196photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv5606556196 #yiv5606556196photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5606556196 #yiv5606556196reco-category {font-size:77%;}#yiv5606556196 #yiv5606556196reco-desc {font-size:77%;}#yiv5606556196 .yiv5606556196replbq {margin:4px;}#yiv5606556196 #yiv5606556196ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv5606556196 #yiv5606556196ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5606556196 #yiv5606556196ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5606556196 #yiv5606556196ygrp-mlmsg select, #yiv5606556196 input, #yiv5606556196 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv5606556196 #yiv5606556196ygrp-mlmsg pre, #yiv5606556196 code {font:115% monospace;}#yiv5606556196 #yiv5606556196ygrp-mlmsg * {line-height:1.22em;}#yiv5606556196 #yiv5606556196ygrp-mlmsg #yiv5606556196logo {padding-bottom:10px;}#yiv5606556196 #yiv5606556196ygrp-msg p a {font-family:Verdana;}#yiv5606556196 #yiv5606556196ygrp-msg p#yiv5606556196attach-count span {color:#1E66AE;font-weight:700;}#yiv5606556196 #yiv5606556196ygrp-reco #yiv5606556196reco-head {color:#ff7900;font-weight:700;}#yiv5606556196 #yiv5606556196ygrp-reco {margin-bottom:20px;padding:0px;}#yiv5606556196 #yiv5606556196ygrp-sponsor #yiv5606556196ov li a {font-size:130%;text-decoration:none;}#yiv5606556196 #yiv5606556196ygrp-sponsor #yiv5606556196ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv5606556196 #yiv5606556196ygrp-sponsor #yiv5606556196ov ul {margin:0;padding:0 0 0 8px;}#yiv5606556196 #yiv5606556196ygrp-text {font-family:Georgia;}#yiv5606556196 #yiv5606556196ygrp-text p {margin:0 0 1em 0;}#yiv5606556196 #yiv5606556196ygrp-text tt {font-size:120%;}#yiv5606556196 #yiv5606556196ygrp-vital ul li:last-child {border-right:none !important;}#yiv5606556196

Tue Nov 24, 2015 5:03 am (PST) . Posted by:

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

sebelum di protect, pasang freeze panes yang diinginkan. Setelah itu
barulah sheet di protect.

Regards,
Kid

On Tue, Nov 24, 2015 at 4:43 PM, marone oke maroneoke940@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:

>
>
> tengkyu mr. kid
>
> ada lagi nih...
> saya mau protect sheet...tapi freeze panes aktif...gimana ya..
>
> tks
>
>
>
> Pada Selasa, 24 November 2015 6:36, "'Mr. Kid' mr.nmkid@gmail.com
> [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
>
>
>
> Hai Marone,
>
> Manualnya begini :
> 1. ribbon Review -> group Changes -> Protect Sheet -> muncul dialog window
> protect sheet
> 2. isi password (bila perlu) -> centang yang diperbolehkan -> tekan OK
> untuk bisa border, berarti format cells yang di centang. Tapi, segala
> macam format cells bisa dilakukan.
>
> Jika ingin tahu kode VBA nya, lakukan cara manual tersebut saat proses
> record macro dijalankan, alias lakukan macro recording
>
> Regards,
> Kid
>
>
>
>
>
> On Mon, Nov 23, 2015 at 6:12 PM, marone oke maroneoke940@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
> dear master,,
>
> tolong code macro, protect sheet tapi borders tetap aktif.....
>
> tks bantuannya
>
>
>
>
>
>

Tue Nov 24, 2015 5:06 am (PST) . Posted by:

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

Hai Fendik,

Blok area yang akan diberi Conditional Formatting (CF) mulai dari record
pertamanya (misal data di kolom C). Kemudian buat CF dengan opsi is formula
(xl2007 ke atas) atau formula is (xl2003 ke bawah).
Tulis formula kondisinya, seperti : (untuk baris 5)
=$F5>0
kemudian atur formatnya dan OK sampai kembali ke worksheet

Regards,
Kid

On Tue, Nov 24, 2015 at 8:29 AM, 'Fendik Anton Wijaya'
fendik.anton@tafinance.com [belajar-excel] <belajar-excel@yahoogroups.com>
wrote:

>
>
> Semangat Pagi para master/guru2,
>
> Mohon bantuannya apa ya rumusnya conditional formatting agar hanya kolom
> tertentu yg berwarna merah jika nilai kolom lain mempunyai angka > 0..
> Misal kolom C5:C12, jadi hanya kolom tertentu yg berwarna merah jika ada
> kolom F5:F12 yg mempunyai nilai > 0...
>
> Berikut saya lampirkan contoh excelnya...
>
> Mohon bantuannya & pencerahannya para master & guru2...
>
> Terima kasih banyak atas bantuannya...
>
> Best regards,
>
> Fendik Anton Wijaya
> PT. Toyota Astra Financial Services
> Gedung GARDA OTO
> Jl. A. Yani KM. 3.5 No. 66
> Banjarmasin 70369
> VOIP : 1929 EXT. 118
> Telpon : 0511.3269136
> Handphone : 081351356550
>
> ------------------------------
> The information transmitted is intended only for the person or the entity
> to which it is addressed and may contain confidential and/or privileged
> material. If you have received it by mistake please notify the sender by
> return e-mail and delete this message including any of its attachments from
> your system. Any use, review, reliance or dissemination of this
> message/information in whole or in part is strictly prohibited. Please note
> that e-mails are susceptible to change. The views expressed/presented in
> this email are solely those of the author and do not necessarily represent
> those of PT Toyota Astra Financial Services and should not be construed as
> the views, offers or acceptances of PT Toyota Astra Financial Services. The
> recipient should check this email/message and any attachments for the
> presence of viruses. PT Toyota Astra Financial Services accepts no
> liability for any damage caused by any viruses transmitted by this
> email/message.
>
> ý *Towards a Sustainable Future - **Please consider the environment
> before printing this email.*
>
>
>

Tue Nov 24, 2015 4:25 pm (PST) . Posted by:

"Fendik Anton Wijaya"

Dear Mr. Kid,

Waaao berhasil Pak...

Terima kasih banyak Mr. Kid...

Best regards,

Fendik Anton Wijaya
PT. Toyota Astra Financial Services
Gedung GARDA OTO
Jl. A. Yani KM. 3.5 No. 66
Banjarmasin 70369
VOIP : 1929 EXT. 118

Telpon : 0511.3269136
Handphone : 081351356550

From: mailto:belajar-excel@yahoogroups.com
Sent: Tuesday, November 24, 2015 9:06 PM
To: BeExcel
Subject: Re: [belajar-excel] Conditional Formatting

Hai Fendik,

Blok area yang akan diberi Conditional Formatting (CF) mulai dari record pertamanya (misal data di kolom C). Kemudian buat CF dengan opsi is formula (xl2007 ke atas) atau formula is (xl2003 ke bawah).

Tulis formula kondisinya, seperti : (untuk baris 5)

=$F5>0

kemudian atur formatnya dan OK sampai kembali ke worksheet

Regards,

Kid

On Tue, Nov 24, 2015 at 8:29 AM, 'Fendik Anton Wijaya' fendik.anton@tafinance.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:

Semangat Pagi para master/guru2,

Mohon bantuannya apa ya rumusnya conditional formatting agar hanya kolom tertentu yg berwarna merah jika nilai kolom lain mempunyai angka > 0..
Misal kolom C5:C12, jadi hanya kolom tertentu yg berwarna merah jika ada kolom F5:F12 yg mempunyai nilai > 0...

Berikut saya lampirkan contoh excelnya...

Mohon bantuannya & pencerahannya para master & guru2...

Terima kasih banyak atas bantuannya...

Best regards,

Fendik Anton Wijaya
PT. Toyota Astra Financial Services
Gedung GARDA OTO
Jl. A. Yani KM. 3.5 No. 66
Banjarmasin 70369
VOIP : 1929 EXT. 118
Telpon : 0511.3269136
Handphone : 081351356550

----------------------------------------------------------
The information transmitted is intended only for the person or the entity to which it is addressed and may contain confidential and/or privileged material. If you have received it by mistake please notify the sender by return e-mail and delete this message including any of its attachments from your system. Any use, review, reliance or dissemination of this message/information in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. The views expressed/presented in this email are solely those of the author and do not necessarily represent those of PT Toyota Astra Financial Services and should not be construed as the views, offers or acceptances of PT Toyota Astra Financial Services. The recipient should check this email/message and any attachments for the presence of viruses. PT Toyota Astra Financial Services accepts no liability for any damage caused by any viruses transmitted by this email/message.

ý Towards a Sustainable Future - Please consider the environment before printing this email.

-------------------

The information transmitted is intended only for the person or the entity to which it is addressed and may contain confidential and/or privileged material. If you have received it by mistake please notify the sender by return e-mail and delete this message including any of its attachments from your system. Any use, review, reliance or dissemination of this message/information in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. The views expressed/presented in this email are solely those of the author and do not necessarily represent those of PT Toyota Astra Financial Services and should not be construed as the views, offers or acceptances of PT Toyota Astra Financial Services. The recipient should check this email/message and any attachments for the presence of viruses. PT Toyota Astra Financial Services accepts no liability for any damage caused by any viruses transmitted by this email/message.

Please consider the environment before printing this email.

Tue Nov 24, 2015 5:13 am (PST) . Posted by:

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

File terlampir

Regards,
Kid

2015-11-23 12:08 GMT+07:00 fathianm@yahoo.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Mohon bantuan lagi , file terlampir
>
>

Tue Nov 24, 2015 6:08 pm (PST) . Posted by:

fathianm

SUPEERRRRR sekali Mr. Kid. klo bleh nanya kolom K di sheet PD fungsinya untuk apa? sama sheet KONTAK di kolom E ketika formatnya sy ubah jadi DATE kok ga bisa ya..... FILE TERLAMPIR

Tue Nov 24, 2015 6:16 pm (PST) . Posted by:

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

Hai Fathian,

Bukannya di formula hasil ada yang merujuk ke kolom K di sheet PD ?
Garis besarnya, supaya formula hasil tidak pakai array formula. Bagaimana
kok bisa begitu hayo ?
Kalau mau tahu jawabnya, coba buat output seperti yang Anda inginkan di
sheet lain, tetapi menggunakan array formula filtering. Coretan tentang
array formula filtering ada disini
<http://excel-mr-kid.blogspot.co.id/2011/04/formula-filter-data-filtering-formula.html>
.

Kemudian, tentang format date, coba disempatkan untuk merenungkan sejenak
tentang datatype di dalam cells. Kalau tidak punya bahan untuk renungan
tersebut, coba mampir disini
<http://excel-mr-kid.blogspot.co.id/2015/03/datatype-dalam-sebuah-cells.html>
(hati-hati, nanti terhipnotis loh, jadi, jangan sambil melamun atau
kebangeten nggumunnya ya).

Setelah itu, barulah mencoba konversi datatype dengan proses perkalian
dengan 1 atau penjumlahan dengan 0.

;)

Wassalam,
Kid

2015-11-25 8:53 GMT+07:00 fathianm@yahoo.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> SUPEERRRRR sekali Mr. Kid. klo bleh nanya kolom K di sheet PD fungsinya
> untuk apa? sama sheet KONTAK di kolom E ketika formatnya sy ubah jadi DATE
> kok ga bisa ya..... FILE TERLAMPIR
>
>

Tue Nov 24, 2015 5:24 am (PST) . Posted by:

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

Maaf, gagal paham nih...
Apanya yang akan di-copy ke kanan ya ?

Regards,
Kid

2015-11-22 9:26 GMT+07:00 fathianm@yahoo.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Trimakasih atas solusinya Mr. Kid udah sy coba dan sukses tp ketika
> rumusnya sy copy kesamping kok ga bisa ya hasilnya sama seperti cel
> dikananya kalo kebawah bisa.
>
>

Tue Nov 24, 2015 5:42 pm (PST) . Posted by:

fathianm

Hee...kalimate mbulet ya Mr Kid..... ini filenya yg dari Mr. Kid sy hapus lalu sy masukkan lagi formulanya hasilnya jadi sama semua

Tue Nov 24, 2015 5:47 pm (PST) . Posted by:

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

oh....
jadi baris keterangan berbunyi :

> Mengambil banyak data yang terstruktur sekaligus dapat dilakukan dengan
> array formula yang dipasang dalam suatu blok array.
>
dan

> Array formula di baris output (misal baris 5) yang membentuk blok array
> C5:H5
>
belum dilaksanakan to...

Jadi, cara membuat blok array : (misal untuk C5:H5)
1. blok C5:H5 mulai dari C5
2. tulis formulanya tapi jangan tekan ENTER ataupun pindah cells :
=IF(ISNUMBER($A5),INDEX('[data
siswa.xlsx]PD'!$A$5:$G$24,$A5,{1,4,2,6,7,3}),"")
3. tekan CTRL SHIFT ENTER supaya jadi array formula

Karena array formula yang dibuat (poin 3) diletakkan di banyak cells (poin
1), maka akan membentuk blok array.

Wassalam,
Kid

2015-11-25 8:41 GMT+07:00 fathianm@yahoo.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Hee...kalimate mbulet ya Mr Kid..... ini filenya yg dari Mr. Kid sy hapus
> lalu sy masukkan lagi formulanya hasilnya jadi sama semua
>
>

Tue Nov 24, 2015 10:05 am (PST) . Posted by:

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

Hai Heru Purnomo,

Data validation list ya...
Coba jalan-jalan ke sini
<http://excel-mr-kid.blogspot.co.id/2015/11/data-validation-list-3-banyak-tingkat.html>.
Sebenarnya mulai dari sini
<http://excel-mr-kid.blogspot.co.id/2015/11/data-validation-list-1-pengantar.html>
.

Regards,
Kid

2015-11-19 8:01 GMT+07:00 heru Purnomo h3rupurnomo@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Dear Be Exceller
>
> Selamat siang
> Mohon bantuannya bagaimana membuat formula LIST Button
>
> Contoh
> Provinsi :
> ACEH, SUMATERA BARAT, SUMATERA UTARA
>
> AREA
> ACEH : Banda Aceh; Langsa; Lhoksumawe; Meulaboh; Subulussalam
> SUMATERA UTARA : Medan; Lubuk Pakam; Binjai; Pematang siantar; Sibolga;
> Padang Sidempuan; Nias
> Sumatera Barat : Padang; Bukit tinggi; Solok; Payak kumbuh
>
> Sub Area
> Banda Aceh : merduati; keudebieng; lambaro; jantho
> Langsa : Kuala simpang; pidie; peureulak; kota cane; blang kejeran
> ....
>
> yang diinginkan seperti pada gambar; data contoh terlampir
>
>
>
> atas bantuannya saya ucapkan terima Kasih
>
> heru purnomo
>
>
>

Tue Nov 24, 2015 10:58 am (PST) . Posted by:

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

Pak Samsudin,

Contoh formula konversi satuan seperti file yang ada disini
<https://app.box.com/s/yrifge66jwl4707vp8zx639oket6689n>.
Data validation list bertingkat bisa dilihat disini
<http://excel-mr-kid.blogspot.co.id/2015/11/data-validation-list-3-banyak-tingkat.html>,
tetapi sebaiknya dimulai dari sini
<http://excel-mr-kid.blogspot.co.id/2015/11/data-validation-list-1-pengantar.html>
.

Wassalam,
Kid

On Sat, Nov 21, 2015 at 2:51 PM, Samsudin samsudin@bayan.com.sg
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:

>
>
> Dear Master Excel,
>
>
>
> Saya punya masalah dengan formula untuk buat template satuan panjang,
> tekanan, dan energy (file terlampir). Bagiamana formulanya untuk bisa
> secara otomatis jika saya memilih pada cell f2, kemudian saya memilih lagi
> cell h2, kemudian saya menginput nilai pada cell g2, sehingga saya akan
> mendapatkan nilai otomatis muncul (berubah-ubah) sesuai pilihan pada cell
> h4, dimana acuannya ada pada cell b12 :d20.
>
>
>
> Sebelumnya saya mengucapkan banyak terima kasih dan sebagai informasi pada
> kasus ini saya sudah menggunakan formula dari Mba Siti
> (Alm)_dynamic_dropdownlist.
>
>
>
> *Terima kasih*
>
> *Samsudin*
>
>
>
> ----------------------------------------------------------
>
>
> This message (including any attachments) contains confidential information intended
> for a specific individual and purpose, and is protected by law. If you are not the
> intended recipient, you should delete this message and hereby notified that any
> disclosure, copying, or distribution of this message (or any part thereof), or the
> taking of any action based on it, is strictly prohibited. No liability or responsibility
> is accepted if information or data is, for whatever reason corrupted or does not reach
> its intended recipient. No warranty is given that this email is free of viruses. The
> views expressed in this email are, unless otherwise stated, those of the author and
> not those of the Company or its management. The Company reserves the right to monitor,
> intercept and block emails addressed to its users or take any other action in accordance
> with its email use policy.
>
>
>

Tue Nov 24, 2015 5:33 pm (PST) . Posted by:

"Walsoyo"

Muohon bantuannya bagaimana menghitung dengan sumifs dengan kriteria
bulan file terlampir

Tue Nov 24, 2015 6:00 pm (PST) . Posted by:

"odong nando"

yoo, walsuyo...
jika memaksakan harus dengan fungsi sumifs, sepertinya harus ada kolom bantu untuk mengkonfersi tanggal yang ada untuk menghasilkan nilai bulan yang anda mau, berikut adalah contuh fungsi sumifs dengan asumsi pada kolom D2:D5 merupakan kolom bantu untuk nilai bulan :
=SUMIFS ( C2:C5 ; D2 : D 5 ; F 1 ; B 2 : B 5 ; " b " )

ganti karakter titik koma " ; " menjadi koma " , " bila komputer setempat beregional english. br,

odong nando


Pada Rabu, 25 November 2015 8:33, "Walsoyo soyomakmur@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Muohon bantuannya bagaimana menghitung dengan sumifs dengan kriteria
bulan file terlampir
#yiv3529844552 #yiv3529844552 -- #yiv3529844552ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3529844552 #yiv3529844552ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3529844552 #yiv3529844552ygrp-mkp #yiv3529844552hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3529844552 #yiv3529844552ygrp-mkp #yiv3529844552ads {margin-bottom:10px;}#yiv3529844552 #yiv3529844552ygrp-mkp .yiv3529844552ad {padding:0 0;}#yiv3529844552 #yiv3529844552ygrp-mkp .yiv3529844552ad p {margin:0;}#yiv3529844552 #yiv3529844552ygrp-mkp .yiv3529844552ad a {color:#0000ff;text-decoration:none;}#yiv3529844552 #yiv3529844552ygrp-sponsor #yiv3529844552ygrp-lc {font-family:Arial;}#yiv3529844552 #yiv3529844552ygrp-sponsor #yiv3529844552ygrp-lc #yiv3529844552hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3529844552 #yiv3529844552ygrp-sponsor #yiv3529844552ygrp-lc .yiv3529844552ad {margin-bottom:10px;padding:0 0;}#yiv3529844552 #yiv3529844552actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3529844552 #yiv3529844552activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3529844552 #yiv3529844552activity span {font-weight:700;}#yiv3529844552 #yiv3529844552activity span:first-child {text-transform:uppercase;}#yiv3529844552 #yiv3529844552activity span a {color:#5085b6;text-decoration:none;}#yiv3529844552 #yiv3529844552activity span span {color:#ff7900;}#yiv3529844552 #yiv3529844552activity span .yiv3529844552underline {text-decoration:underline;}#yiv3529844552 .yiv3529844552attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3529844552 .yiv3529844552attach div a {text-decoration:none;}#yiv3529844552 .yiv3529844552attach img {border:none;padding-right:5px;}#yiv3529844552 .yiv3529844552attach label {display:block;margin-bottom:5px;}#yiv3529844552 .yiv3529844552attach label a {text-decoration:none;}#yiv3529844552 blockquote {margin:0 0 0 4px;}#yiv3529844552 .yiv3529844552bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3529844552 .yiv3529844552bold a {text-decoration:none;}#yiv3529844552 dd.yiv3529844552last p a {font-family:Verdana;font-weight:700;}#yiv3529844552 dd.yiv3529844552last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3529844552 dd.yiv3529844552last p span.yiv3529844552yshortcuts {margin-right:0;}#yiv3529844552 div.yiv3529844552attach-table div div a {text-decoration:none;}#yiv3529844552 div.yiv3529844552attach-table {width:400px;}#yiv3529844552 div.yiv3529844552file-title a, #yiv3529844552 div.yiv3529844552file-title a:active, #yiv3529844552 div.yiv3529844552file-title a:hover, #yiv3529844552 div.yiv3529844552file-title a:visited {text-decoration:none;}#yiv3529844552 div.yiv3529844552photo-title a, #yiv3529844552 div.yiv3529844552photo-title a:active, #yiv3529844552 div.yiv3529844552photo-title a:hover, #yiv3529844552 div.yiv3529844552photo-title a:visited {text-decoration:none;}#yiv3529844552 div#yiv3529844552ygrp-mlmsg #yiv3529844552ygrp-msg p a span.yiv3529844552yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3529844552 .yiv3529844552green {color:#628c2a;}#yiv3529844552 .yiv3529844552MsoNormal {margin:0 0 0 0;}#yiv3529844552 o {font-size:0;}#yiv3529844552 #yiv3529844552photos div {float:left;width:72px;}#yiv3529844552 #yiv3529844552photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv3529844552 #yiv3529844552photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3529844552 #yiv3529844552reco-category {font-size:77%;}#yiv3529844552 #yiv3529844552reco-desc {font-size:77%;}#yiv3529844552 .yiv3529844552replbq {margin:4px;}#yiv3529844552 #yiv3529844552ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3529844552 #yiv3529844552ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3529844552 #yiv3529844552ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3529844552 #yiv3529844552ygrp-mlmsg select, #yiv3529844552 input, #yiv3529844552 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3529844552 #yiv3529844552ygrp-mlmsg pre, #yiv3529844552 code {font:115% monospace;}#yiv3529844552 #yiv3529844552ygrp-mlmsg * {line-height:1.22em;}#yiv3529844552 #yiv3529844552ygrp-mlmsg #yiv3529844552logo {padding-bottom:10px;}#yiv3529844552 #yiv3529844552ygrp-msg p a {font-family:Verdana;}#yiv3529844552 #yiv3529844552ygrp-msg p#yiv3529844552attach-count span {color:#1E66AE;font-weight:700;}#yiv3529844552 #yiv3529844552ygrp-reco #yiv3529844552reco-head {color:#ff7900;font-weight:700;}#yiv3529844552 #yiv3529844552ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3529844552 #yiv3529844552ygrp-sponsor #yiv3529844552ov li a {font-size:130%;text-decoration:none;}#yiv3529844552 #yiv3529844552ygrp-sponsor #yiv3529844552ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3529844552 #yiv3529844552ygrp-sponsor #yiv3529844552ov ul {margin:0;padding:0 0 0 8px;}#yiv3529844552 #yiv3529844552ygrp-text {font-family:Georgia;}#yiv3529844552 #yiv3529844552ygrp-text p {margin:0 0 1em 0;}#yiv3529844552 #yiv3529844552ygrp-text tt {font-size:120%;}#yiv3529844552 #yiv3529844552ygrp-vital ul li:last-child {border-right:none !important;}#yiv3529844552

Tue Nov 24, 2015 5:33 pm (PST) . Posted by:

"ab readnow" abiez_readnow

Dear Suhu Excel,

Saya baru gabung milis ini karena cukup pusing harus menggunakan rumus apa untuk pengisian rak otomatis berdasarkan data yg tersedia. Ketentuannya sebagai berikut:- Satu rak berisi maksimal 4 item & 4 Qty
- Rak kosong tetap terlihat kosong (tanpa tulisan 0)- Pengisian lebih 1 item di rak sebelah Kanan adalah arah kiri ke kanan- Pengisian lebih 1 item di rak sebelah Kiri adalah arah kanan ke kiri
keterangan lebih lengkap ada di lampiran.

Terima kasihAbdul Ritno
============================================================
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
---------------------------------------------------------------------
Diposting oleh EXCEL FEED LEARNING di 18.16

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)