[belajar-excel] Digest Number 1210

Milis Belajar Microsoft Excel

Messages In This Digest (25 Messages)

1a.

Re: Summary berdasarkan dari Header From: Mr. Kid

1b.

Re: Summary berdasarkan dari Header From: summonery

1c.

Re: Summary berdasarkan dari Header From: Sudarsono Suhenk

2a.

Re: Tanya donk,,Tampilkan hasil membalikkan urutan data From: summonery

3a.

Tanya: merapikan tabel dengan makro From: Santi Rosaindah

3b.

Re: Tanya: merapikan tabel dengan makro From: Sudarsono Suhenk

3c.

Re: Tanya: merapikan tabel dengan makro From: Sudarsono Suhenk

3d.

Re: Tanya: merapikan tabel dengan makro From: Sudarsono Suhenk

3e.

Re: Tanya: merapikan tabel dengan makro From: Sudarsono Suhenk

4a.

Re: Fwd: ]] XL-mania [[ bahasakan Surat penyerahan barang From: Bachelor Ard

4b.

Re: Fwd: ]] XL-mania [[ bahasakan Surat penyerahan barang From: STDEV(i)

5a.

Mengelompokkan Sheet Berdasakan Salah Satu Kolom From: akbar arsyad

5b.

Function untuk Goal Seek From: Tauffik Effendi

5c.

Re: Function untuk Goal Seek From: zainul ulum [yahoo]

5d.

Re: Function untuk Goal Seek From: Tauffik Effendi

5e.

Re: Function untuk Goal Seek From: zainul ulum [yahoo]

5f.

Re: Function untuk Goal Seek From: Sudarsono Suhenk

5g.

Re: Function untuk Goal Seek From: Tauffik Effendi

5h.

Re: Function untuk Goal Seek From: zainul ulum [yahoo]

6a.

Masalah penomoran lagi ... From: Yulius Minsai

6b.

Re: Masalah penomoran lagi ... From: Sudarsono Suhenk

6c.

Re: Masalah penomoran lagi ... From: STDEV(i)

6d.

Re: Masalah penomoran lagi ... From: STDEV(i)

7a.

Re: Bon Toko From: Yvonne

7b.

Re: Bon Toko From: STDEV(i)

View All Topics | Create New Topic

Messages

1a.

Re: Summary berdasarkan dari Header

Posted by: "Mr. Kid" mr.nmkid@gmail.com   nmkid.family@ymail.com

Wed Jun 22, 2011 1:04 am (PDT)

Coba pada record pertama :
=MID(REPT(","&D$2,-INT(-D4))&REPT(","&E$2,-INT(-E4))&REPT(","&F$2,-INT(-F4))&REPT(","&G$2,-INT(-G4))&REPT(","&H$2,-INT(-H4))&REPT(","&I$2,-INT(-I4)),2,99)

copy ke bawah

Kid.

2011/6/22 Tony Bexcel <tonybexcel@yahoo.co.id>

> **
>
>
> Dear Pakar yang Budiman,
>
> Mohon maaf bila tulisan Judul atau pun maksud tulisan tidak dimengerti,
> karena saya kesulitan menyampaikan maksud nya.
> Inti tujuan nya terlampir pada File terlampir..
> Atas segala bantuan ilmu dan waktunya saya ucapkan banyak terimakasih ya
> Temanz..
>
> Salam,
> TonyBexcel
>
>
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)

1b.

Re: Summary berdasarkan dari Header

Posted by: "summonery" summonery@yahoo.com   summonery

Wed Jun 22, 2011 1:12 am (PDT)

coba
juga,=MID(REPT(","&D$2,D15<>0)&REPT(","&E$2,E15<>0)&REPT(","&F$2,F15<>0)\
&REPT(","&G$2,G15<>0)&REPT(","&H$2,H15<>0)&REPT(","&I$2,I15<>0),2,99)

>semoga bermanfaat

--- In belajar-excel@yahoogroups.com, "Mr. Kid" <mr.nmkid@...> wrote:
>
> Coba pada record pertama :
>
=MID(REPT(","&D$2,-INT(-D4))&REPT(","&E$2,-INT(-E4))&REPT(","&F$2,-INT(-\
F4))&REPT(","&G$2,-INT(-G4))&REPT(","&H$2,-INT(-H4))&REPT(","&I$2,-INT(-\
I4)),2,99)
>
> copy ke bawah
>
> Kid.
>
> 2011/6/22 Tony Bexcel tonybexcel@...
>
> > **
> >
> >
> > Dear Pakar yang Budiman,
> >
> > Mohon maaf bila tulisan Judul atau pun maksud tulisan tidak
dimengerti,
> > karena saya kesulitan menyampaikan maksud nya.
> > Inti tujuan nya terlampir pada File terlampir..
> > Atas segala bantuan ilmu dan waktunya saya ucapkan banyak
terimakasih ya
> > Temanz..
> >
> > Salam,
> > TonyBexcel
> >
> >
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)

1c.

Re: Summary berdasarkan dari Header

Posted by: "Sudarsono Suhenk" jkssbma@live.com   jkssbma

Wed Jun 22, 2011 7:51 pm (PDT)

saya membuat dengan CONCATENATE :=IF(LEFT( CONCATENATE(IF(D4>0,$D$2,""),IF(E4>0,"," & $E$2,""),IF(F4>0,"," & $F$2,""),IF(G4>0,"," & $G$2,""),IF(H4>0,"," & $H$2,""),IF(I4>0,"," & $I$2,"")) ,1)=",",MID(CONCATENATE(IF(D4>0,$D$2,""),IF(E4>0,"," & $E$2,""),IF(F4>0,"," & $F$2,""),IF(G4>0,"," & $G$2,""),IF(H4>0,"," & $H$2,""),IF(I4>0,"," & $I$2,"")),2,LEN( CONCATENATE(IF(D4>0,$D$2,""),IF(E4>0,"," & $E$2,""),IF(F4>0,"," & $F$2,""),IF(G4>0,"," & $G$2,""),IF(H4>0,"," & $H$2,""),IF(I4>0,"," & $I$2,"")) )),CONCATENATE(IF(D4>0,$D$2,""),IF(E4>0,"," & $E$2,""),IF(F4>0,"," & $F$2,""),IF(G4>0,"," & $G$2,""),IF(H4>0,"," & $H$2,""),IF(I4>0,"," & $I$2,"")) ) =copy ke baris L4:L15
To: belajar-excel@yahoogroups.com
From: summonery@yahoo.com
Date: Wed, 22 Jun 2011 08:12:26 +0000
Subject: [belajar-excel] Re: Summary berdasarkan dari Header

coba juga,=MID(REPT(","&D$2,D15<>0)&REPT(","&E$2,E15<>0)&REPT(","&F$2,F15<>0)&REPT(","&G$2,G15<>0)&REPT(","&H$2,H15<>0)&REPT(","&I$2,I15<>0),2,99)

>semoga bermanfaat

--- In belajar-excel@yahoogroups.com, "Mr. Kid" <mr.nmkid@...> wrote:
>
> Coba pada record pertama :
> =MID(REPT(","&D$2,-INT(-D4))&REPT(","&E$2,-INT(-E4))&REPT(","&F$2,-INT(-F4))&REPT(","&G$2,-INT(-G4))&REPT(","&H$2,-INT(-H4))&REPT(","&I$2,-INT(-I4)),2,99)
>
> copy ke bawah
>
> Kid.
>
> 2011/6/22 Tony Bexcel tonybexcel@...
>
> > **
> >
> >
> > Dear Pakar yang Budiman,
> >
> > Mohon maaf bila tulisan Judul atau pun maksud tulisan tidak dimengerti,
> > karena saya kesulitan menyampaikan maksud nya.
> > Inti tujuan nya terlampir pada File terlampir..
> > Atas segala bantuan ilmu dan waktunya saya ucapkan banyak terimakasih ya
> > Temanz..
> >
> > Salam,
> > TonyBexcel
> >
> >
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)

2a.

Re: Tanya donk,,Tampilkan hasil membalikkan urutan data

Posted by: "summonery" summonery@yahoo.com   summonery

Wed Jun 22, 2011 1:17 am (PDT)

+1 INDIRECT
=INDIRECT("A"&(ROW($A$10)+ROW($A$1)-ROW()))

>semoga bermanfaat

--- In belajar-excel@yahoogroups.com, "sudarsono" <jkssbma@...> wrote:
>
>
> jika =row(1:1) di jadikan sbg formula biasa, maka hasilnya sama dengan
> {=row(1:8)} ,bila di kopi ke baris berikutnya.
> ini hanya selera dalam hal penulisan dan debuging.
>
> Keuntungan no.1 ,kita dapat penggal rumus yang ada untuk melihat
apakah
> penggalan rumus tsb benar atau tidak
>
> jika pendefenisian formula ( misal data ) meliputi range A3:A117 ( 15
> baris ) , walaupun data 10 , itu tak masalah . Justru akan timbul
> masalah jika data yang di isi lebih dari 15 baris. maka data menjadi
> tidak valid.
>
> masing2 mempunyai alur pemikiran yang berbeda , solusi akhir mungkin
> sama.
> '==========
> --- In belajar-excel@yahoogroups.com, "Mr. Kid" mr.nmkid@ wrote:
> >
> > Apakah karena formula no 1 dan nomor 2 adalah array formula sehingga
> formula
> > pertama lebih mudah ditelusuri ?
> > Bukankah formula no 2 bisa sebagai formula biasa ?
> > Andai array formula pertama telah di assign sebagai array blok 10
> baris
> > karena data ada 10 baris, kemudian data bertambah 2 baris, akan ada
> > kelebihan cell berformula ?
> >
> > Just wondering...
> >
> > Kid.
> >
> > 2011/6/22 Sudarsono Suhenk jkssbma@
> >
> > > **
> > >
> > >
> > > 1. {=INDEX(Data,COUNTA(Data)-ROW(INDIRECT("1:"&COUNTA(Data)))+1)}
> > > 2. {=INDEX(Data,COUNTA(Data)-ROW(1:1)+1)}
> > > {=COUNTA(Data)-ROW(INDIRECT("1:"&COUNTA(Data)))+1} jika di
terapkan
> > > pada range , misal dari baris 1 sd 7 akan hasilkan
> > > {7-1+1,7-2+1,7-3+1,7-4+1,7-5+1,7-6+1,7-7+1}
> > > {=COUNTA(Data)-ROW(1:1)+1} jika di terapkan pada range , misal
dari
> > > baris 1 sd 7 akan hasilkan
> {7-1+1,7-1+1,7-1+1,7-1+1,7-1+1,7-1+1,7-1+1}
> > > walaupun jika di terapkan pada index(data, ..... ) akan
menghasilkan
> data
> > > baris 7 , baris 6 , baris 5 , baris 4 ,baris 3 , baris 2 , dan
baris
> 1
> > >
> > > dari segi kosistensi saya lebih memilih no. 1. karena dapat
telusuri
> jika
> > > hasil tidak sesuai.
> > >
> > >
> > > ------------------------------
> > > To: belajar-excel@yahoogroups.com
> > > From: hendrikkarnadi@
> > > Date: Tue, 21 Jun 2011 19:20:42 -0700
> > > Subject: Fw: Re: [belajar-excel] Tanya donk,,Tampilkan hasil
> membalikkan
> > > urutan data
> > >
> > >
> > > Untuk menentukan jumlah barisnya buat *flexible range name, *mis*
> *"*Data
> > > *" dengan menggunakan fungsi OFFSET (lihat contoh terlampir).
> > > di
> > > Salam,
> > > Hendrik Karnadi
> > >
> > >
> > > --- On *Tue, 21/6/11, yogassantika@ yogassantika@*wrote:
> > >
> > >
> > > From: yogassantika@ yogassantika@
> > > Subject: Re: [belajar-excel] Tanya donk,,Tampilkan hasil
membalikkan
> urutan
> > > data
> > > To: belajar-excel@yahoogroups.com
> > > Date: Tuesday, 21 June, 2011, 6:34 PM
> > >
> > >
> > > Makasih ya,,,tanya lagi donk berarti kita harus tw jumlah barisnya
> > > yua?seandainya kita gatau barisnya berapa gmna donk
> > > Sent from my BlackBerry® smartphone from Sinyal Bagus XL,
> Nyambung
> > > Teruuusss...!
> > > ------------------------------
> > > *From: *"STDEV(i)" setiyowati.devi@
> > > *Sender: *belajar-excel@yahoogroups.com
> > > *Date: *Tue, 21 Jun 2011 17:50:17 +0700
> > > *To: *belajar-excel@yahoogroups.com
> > > *ReplyTo: *belajar-excel@yahoogroups.com
> > > *Subject: *Re: [belajar-excel] Tanya donk,,Tampilkan hasil
> membalikkan
> > > urutan data
> > >
> > >
> > > kalau pak yoga saat bertanya sambil melampirkan data contoh
> > > otomatis para penangggap akan membuatkan solusinya di workbook
> lampiran itu
> > > lalu mengembalikan kepada bapak
> > >
> > > *bapak (yg menanyakan) tidak melampirkan contoh* tapi malah minta
> dikirimi
> > > contoh !!
> > > dari mana kami mendapatkannya ?
> > >
> > >
> > > he he he
> > >
> > >
> > >
> > > 2011/6/21
> yogassantika@<http://aa.mc1105.mail.yahoo.com/mc/compose?to=yogassant\
> ika@
> > > >
> > >
> > > **
> > > *Bisa berikan contoh *Ã`ǥαќ>:/ lewat lampiran biz sya
coba
> ko
> > > gbsa?.‎​Ħïî:D☀ Ħïî:D☀
> Ħïî:D☀ Ħïî
> > >
> > >
> > >
> > >
> > >
> > > *From: *"STDEV(i)"
> setiyowati.devi@<http://aa.mc1105.mail.yahoo.com/mc/compose?to=setiyo\
> wati.devi@
> > >
> > > *Date: *Tue, 21 Jun 2011 17:29:34 +0700
> > >
> > > =INDEX($A$1:$A$10,10-ROW(1:$10)+1)
> > > *ya SANGAT BISA*
> > > kalau anda menuliskan nama fungsi-nya secara salah
> > >
> > >
> > >
> > > 2011/6/21
> yogassantika@<http://aa.mc1105.mail.yahoo.com/mc/compose?to=yogassant\
> ika@
> > > >
> > >
> > > **
> > > *Ko gbisa ya*?muncul #name?
> > > *From: *"STDEV(i)"
> setiyowati.devi@<http://aa.mc1105.mail.yahoo.com/mc/compose?to=setiyo\
> wati.devi@
> > >
> > > *Date: *Tue, 21 Jun 2011 17:06:06 +0700
> > > *
> > > *
> > > Misal kita SUDAH TAHU datanya ada 10 cell, berada di A1:A10
> > > tulis di cell B1
> > >
> > > =INDEX($A$1:$A$10,10-ROW(1:$10)+1)
> > >
> > > Kemudian double-klik Fill-Handel-nya
> > > (Fill Handel adalah titik di pojok-kanan-bawah cell yg sedang
aktif)
> > > dengan didoble-kllik hasilny aseperti kita mengkopi cell B1
> > > ke B1 sampai B10 (sejajar dengan adanya data di kolom sebelahnya)
> > >
> > > 2011/6/21
> yogassantika@<http://aa.mc1105.mail.yahoo.com/mc/compose?to=yogassant\
> ika@
> > > >
> > > > Master" sya mw tanya nich,,,misal di kolom a itu ada nama hari
> > > senin,selasa,rabu,kamis,jum'at,,,,
> > > > nach ada Ã`ǥαќ>:/ rumus supaya di kolom b secara
otomatis
> datanya jadi
> > > sebaliknya
> > > > jadi jum'at,kamis,rabu,selasa,senin,makasih
> > >
> > >
> > >
> > >
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

3a.

Tanya: merapikan tabel dengan makro

Posted by: "Santi Rosaindah" santi.rosaindah@indexfurnishings.com   santi.rosaindah

Wed Jun 22, 2011 1:23 am (PDT)

Dear all,

Saya punya contoh data seperti terlampir.
kendalanya, kalau dibagian responsibility kosong, maka ketika makro
dijalankan, maka bordernya sampai akhir baris.
maunya terborder sampai baris yang sama dengan kolom sebelahnya

Mohon bantuan infonya untuk bahasa VBAnya seperti apa yaa?

Terima kasih

--
Sincerely,
Santi Rosaindah

*There will always be Sun-SHinE after the rain*

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

3b.

Re: Tanya: merapikan tabel dengan makro

Posted by: "Sudarsono Suhenk" jkssbma@live.com   jkssbma

Wed Jun 22, 2011 6:47 pm (PDT)

ADF

























Periode
From
5/1/2011











Periode
To
5/31/2011











Company
20
Toko A










Dept Name
HRD
























NO
KLS
DESCRIPTION
QTY
UNITPRICE
TOTAL
Kondisi
PROPOSED
CAUSED BY
APPROVED
RESPONSIBILITY OF


A1
K245
kursi
1
100,000
100,000
retak
repair
Handling di
store
repair di store



A2
K624
kursi
1
100,000
100,000
retak
repair
Handling
di store
repair
di store



A3
K921
meja
1
200,000
100,000
retak
Pengiriman
Handling
di store
repair
di store








300,000







macro utama :Sub FormatFormula()
Dim barisawal, barisakhir As Long
barisawal = 11
'===
Call formatjudul
'----
Range("A" & barisawal).Select ' awal sel tabel
Selection.End(xlDown).Select
barisakhir = Selection.Row
Range("A" & barisawal & ":K" & barisakhir).Select
' summary
Range("F" & barisakhir + 1).Select
Call formatFooter
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
' Responsibilty
Range("K" & barisakhir + 1).Select
Call formatFooter
' proposed , menentukan warna yang sesuai
Range("H" & barisawal & ":H" & barisakhir).Select
Call formatKondisional
End Sub

untuk sub rutin formatjudul , formatisi dan formatFooter , ambil saja koding anda . Karena hanya menentukan lebar kolom, tinggi baris dan border.
untuk kondisiona format gunakan sub rutin berikut :Sub formatKondisional()
Cells.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""pengiriman"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = True
End Sub
Semoga sesuaiTo: belajar-excel@yahoogroups.com
From: santi.rosaindah@indexfurnishings.com
Date: Wed, 22 Jun 2011 15:21:13 +0700
Subject: [belajar-excel] Tanya: merapikan tabel dengan makro

Dear all,

Saya punya contoh data seperti terlampir.

kendalanya, kalau dibagian responsibility kosong, maka ketika makro

dijalankan, maka bordernya sampai akhir baris.

maunya terborder sampai baris yang sama dengan kolom sebelahnya

Mohon bantuan infonya untuk bahasa VBAnya seperti apa yaa?

Terima kasih

--

Sincerely,

Santi Rosaindah

*There will always be Sun-SHinE after the rain*

--

This message has been scanned for viruses and

dangerous content by MailScanner, and is

believed to be clean.

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

3c.

Re: Tanya: merapikan tabel dengan makro

Posted by: "Sudarsono Suhenk" jkssbma@live.com   jkssbma

Wed Jun 22, 2011 6:54 pm (PDT)

sori , ada yang ketinggalan yaitu Call formatisi . Range("A" & barisawal & ":K" & barisakhir).Select Call formatisi
' summary
To: belajar-excel@yahoogroups.com
From: jkssbma@live.com
Date: Thu, 23 Jun 2011 08:47:20 +0700
Subject: RE: [belajar-excel] Tanya: merapikan tabel dengan makro

ADF

























Periode
From
5/1/2011











Periode
To
5/31/2011











Company
20
Toko A










Dept Name
HRD
























NO
KLS
DESCRIPTION
QTY
UNITPRICE
TOTAL
Kondisi
PROPOSED
CAUSED BY
APPROVED
RESPONSIBILITY OF


A1
K245
kursi
1
100,000
100,000
retak
repair
Handling di
store
repair di store



A2
K624
kursi
1
100,000
100,000
retak
repair
Handling
di store
repair
di store



A3
K921
meja
1
200,000
100,000
retak
Pengiriman
Handling
di store
repair
di store








300,000







macro utama :
Sub FormatFormula()
Dim barisawal, barisakhir As Long
barisawal = 11
'===
Call formatjudul
'----
Range("A" & barisawal).Select ' awal sel tabel
Selection.End(xlDown).Select
barisakhir = Selection.Row
Range("A" & barisawal & ":K" & barisakhir).Select Call formatisi

' summary
Range("F" & barisakhir + 1).Select
Call formatFooter
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
' Responsibilty
Range("K" & barisakhir + 1).Select
Call formatFooter
' proposed , menentukan warna yang sesuai
Range("H" & barisawal & ":H" & barisakhir).Select
Call formatKondisional
End Sub

untuk sub rutin formatjudul , formatisi dan formatFooter , ambil saja koding anda . Karena hanya menentukan lebar kolom, tinggi baris dan border.

untuk kondisiona format gunakan sub rutin berikut :
Sub formatKondisional()
Cells.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""pengiriman"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = True
End Sub

Semoga sesuai
To: belajar-excel@yahoogroups.com
From: santi.rosaindah@indexfurnishings.com
Date: Wed, 22 Jun 2011 15:21:13 +0700
Subject: [belajar-excel] Tanya: merapikan tabel dengan makro

Dear all,

Saya punya contoh data seperti terlampir.

kendalanya, kalau dibagian responsibility kosong, maka ketika makro

dijalankan, maka bordernya sampai akhir baris.

maunya terborder sampai baris yang sama dengan kolom sebelahnya

Mohon bantuan infonya untuk bahasa VBAnya seperti apa yaa?

Terima kasih

--

Sincerely,

Santi Rosaindah

*There will always be Sun-SHinE after the rain*

--

This message has been scanned for viruses and

dangerous content by MailScanner, and is

believed to be clean.

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

3d.

Re: Tanya: merapikan tabel dengan makro

Posted by: "Sudarsono Suhenk" jkssbma@live.com   jkssbma

Wed Jun 22, 2011 7:00 pm (PDT)

ada saja yang kurang nih, maklum terburu-buru. pagi2 baca milis be-excel, baru mau mulai kerja. sebab ini kesenangan saya ( vba ), daripada di dahuluin yang lain.... ( just kiding Hooi ). ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ganti menjadi:
ActiveCell.FormulaR1C1 = "=SUM(R[-" & barisakhir - barisawal + 1 & "]C:R[-1]C)" To: belajar-excel@yahoogroups.com
From: jkssbma@live.com
Date: Thu, 23 Jun 2011 08:54:08 +0700
Subject: RE: [belajar-excel] Tanya: merapikan tabel dengan makro

sori , ada yang ketinggalan yaitu Call formatisi .

Range("A" & barisawal & ":K" & barisakhir).Select
Call formatisi
' summary
To: belajar-excel@yahoogroups.com
From: jkssbma@live.com
Date: Thu, 23 Jun 2011 08:47:20 +0700
Subject: RE: [belajar-excel] Tanya: merapikan tabel dengan makro

ADF

























Periode
From
5/1/2011











Periode
To
5/31/2011











Company
20
Toko A










Dept Name
HRD
























NO
KLS
DESCRIPTION
QTY
UNITPRICE
TOTAL
Kondisi
PROPOSED
CAUSED BY
APPROVED
RESPONSIBILITY OF


A1
K245
kursi
1
100,000
100,000
retak
repair
Handling di
store
repair di store



A2
K624
kursi
1
100,000
100,000
retak
repair
Handling
di store
repair
di store



A3
K921
meja
1
200,000
100,000
retak
Pengiriman
Handling
di store
repair
di store








300,000







macro utama :
Sub FormatFormula()
Dim barisawal, barisakhir As Long
barisawal = 11
'===
Call formatjudul
'----
Range("A" & barisawal).Select ' awal sel tabel
Selection.End(xlDown).Select
barisakhir = Selection.Row
Range("A" & barisawal & ":K" & barisakhir).Select Call formatisi

' summary
Range("F" & barisakhir + 1).Select
Call formatFooter
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
' Responsibilty
Range("K" & barisakhir + 1).Select
Call formatFooter
' proposed , menentukan warna yang sesuai
Range("H" & barisawal & ":H" & barisakhir).Select
Call formatKondisional
End Sub

untuk sub rutin formatjudul , formatisi dan formatFooter , ambil saja koding anda . Karena hanya menentukan lebar kolom, tinggi baris dan border.

untuk kondisiona format gunakan sub rutin berikut :
Sub formatKondisional()
Cells.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""pengiriman"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = True
End Sub

Semoga sesuai
To: belajar-excel@yahoogroups.com
From: santi.rosaindah@indexfurnishings.com
Date: Wed, 22 Jun 2011 15:21:13 +0700
Subject: [belajar-excel] Tanya: merapikan tabel dengan makro

Dear all,

Saya punya contoh data seperti terlampir.

kendalanya, kalau dibagian responsibility kosong, maka ketika makro

dijalankan, maka bordernya sampai akhir baris.

maunya terborder sampai baris yang sama dengan kolom sebelahnya

Mohon bantuan infonya untuk bahasa VBAnya seperti apa yaa?

Terima kasih

--

Sincerely,

Santi Rosaindah

*There will always be Sun-SHinE after the rain*

--

This message has been scanned for viruses and

dangerous content by MailScanner, and is

believed to be clean.

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

3e.

Re: Tanya: merapikan tabel dengan makro

Posted by: "Sudarsono Suhenk" jkssbma@live.com   jkssbma

Wed Jun 22, 2011 7:20 pm (PDT)

Dari sheet tampak bahwa total , belum ada rumusnya . dan Sheet hasil juga . apa memang manual . kalau rumusnya : total = Qty* UnitPrice. maka perlu penulisan ulang makroutama :' paling atas sebelum makro atau subrutin yang lain Dim barisawal, barisakhir As Long ' ini makro yang diperlukan
Sub FormatFormula() barisawal = 11
'===
Call formatjudul
'----
Range("A" & barisawal).Select ' awal sel tabel
Selection.End(xlDown).Select
barisakhir = Selection.Row
Range("A" & barisawal & ":K" & barisakhir).Select
Call formatisi
' rumus total
Call rumustotal
' summary
Range("F" & barisakhir + 1).Select
Call formatFooter
ActiveCell.FormulaR1C1 = "=SUM(R[-" & barisakhir - barisawal + 1 & "]C:R[-1]C)"
' Responsibilty
Range("K" & barisakhir + 1).Select
Call formatFooter
' proposed , menentukan warna yang sesuai
Range("H" & barisawal & ":H" & barisakhir).Select
Call formatKondisional
End Sub
Sub rumustotal()
Range("F" & barisawal).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Application.CutCopyMode = False
Selection.Copy
Range("F" & barisawal + 1 & ":F" & barisakhir).Select
MsgBox Selection.Address
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Sub formatKondisional()
Cells.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""pengiriman"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = True
End Sub
To: belajar-excel@yahoogroups.com
From: jkssbma@live.com
Date: Thu, 23 Jun 2011 09:00:39 +0700
Subject: RE: [belajar-excel] Tanya: merapikan tabel dengan makro

ada saja yang kurang nih, maklum terburu-buru. pagi2 baca milis be-excel, baru mau mulai kerja. sebab ini kesenangan saya ( vba ), daripada di dahuluin yang lain.... ( just kiding Hooi ).

ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"

ganti menjadi:

ActiveCell.FormulaR1C1 = "=SUM(R[-" & barisakhir - barisawal + 1 & "]C:R[-1]C)"
To: belajar-excel@yahoogroups.com
From: jkssbma@live.com
Date: Thu, 23 Jun 2011 08:54:08 +0700
Subject: RE: [belajar-excel] Tanya: merapikan tabel dengan makro

sori , ada yang ketinggalan yaitu Call formatisi .

Range("A" & barisawal & ":K" & barisakhir).Select
Call formatisi
' summary
To: belajar-excel@yahoogroups.com
From: jkssbma@live.com
Date: Thu, 23 Jun 2011 08:47:20 +0700
Subject: RE: [belajar-excel] Tanya: merapikan tabel dengan makro

ADF

























Periode
From
5/1/2011











Periode
To
5/31/2011











Company
20
Toko A










Dept Name
HRD
























NO
KLS
DESCRIPTION
QTY
UNITPRICE
TOTAL
Kondisi
PROPOSED
CAUSED BY
APPROVED
RESPONSIBILITY OF


A1
K245
kursi
1
100,000
100,000
retak
repair
Handling di
store
repair di store



A2
K624
kursi
1
100,000
100,000
retak
repair
Handling
di store
repair
di store



A3
K921
meja
1
200,000
100,000
retak
Pengiriman
Handling
di store
repair
di store








300,000







macro utama :
Sub FormatFormula() .
. Range("A" & barisawal & ":K" & barisakhir).Select Call formatisi

' summary
Range("F" & barisakhir + 1).Select
. .End Sub

untuk sub rutin formatjudul , formatisi dan formatFooter , ambil saja koding anda . Karena hanya menentukan lebar kolom, tinggi baris dan border.

untuk kondisiona format gunakan sub rutin berikut :
Sub formatKondisional()
. ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
.End Sub

Semoga sesuai
To: belajar-excel@yahoogroups.com
From: santi.rosaindah@indexfurnishings.com
Date: Wed, 22 Jun 2011 15:21:13 +0700
Subject: [belajar-excel] Tanya: merapikan tabel dengan makro

Dear all,

Saya punya contoh data seperti terlampir.

kendalanya, kalau dibagian responsibility kosong, maka ketika makro

dijalankan, maka bordernya sampai akhir baris.

maunya terborder sampai baris yang sama dengan kolom sebelahnya

Mohon bantuan infonya untuk bahasa VBAnya seperti apa yaa?

Terima kasih

--

Sincerely,

Santi Rosaindah

*There will always be Sun-SHinE after the rain*

--

This message has been scanned for viruses and

dangerous content by MailScanner, and is

believed to be clean.

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

4a.

Re: Fwd: ]] XL-mania [[ bahasakan Surat penyerahan barang

Posted by: "Bachelor Ard" bachelor_ard@yahoo.com   bachelor_ard

Wed Jun 22, 2011 1:24 am (PDT)

maaf tiadak dapat sy sebutkan satu-satu...!
terima kasih banyak untuk semuanya, akhirnya solusinya banyak saya dapatkan dari
pakar2 exel di "Be-Excell"

-ard-

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)

4b.

Re: Fwd: ]] XL-mania [[ bahasakan Surat penyerahan barang

Posted by: "STDEV(i)" setiyowati.devi@gmail.com   siti_vi

Wed Jun 22, 2011 7:14 pm (PDT)

wah kok begini lagi ya..

*buka kasus di milis mana ? *
*ditanggpainya di milis mana.... ?*

from Bachelor Ard bachelor_ard@yahoo.com reply-to
belajar-excel@yahoogroups.com
to *belajar-excel@yahoogroups.com*
date Wed, Jun 22, 2011 at 3:22 PM subject [belajar-excel] Re: *Fwd: ]]
XL-mania [[* bahasakan Surat penyerahan

2011/6/22 Bachelor Ard <bachelor_ard@yahoo.com>

> **
> maaf tiadak dapat sy sebutkan satu-satu...!
> terima kasih banyak untuk semuanya, akhirnya solusinya banyak saya dapatkan
> dari pakar2 exel di "Be-Excell"
>
>> -ard-
>>
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)

5a.

Mengelompokkan Sheet Berdasakan Salah Satu Kolom

Posted by: "akbar arsyad" akbar_arsyad@yahoo.co.id   akbar_arsyad

Wed Jun 22, 2011 7:26 pm (PDT)

Dear All,

Saya mempunyai permasalahan, saya punya beberapa file,
yaitu file tahun 2003.xls, 2004.xls, dan 2005.xls
Masing-masing berisi satu sheet, contohnya sbb:

-2003.xls
Kolom A Kolom B
Binatang Makanannya<<< Header
Kucing Ikan
Anjing Ikan
Kelinci Wortel
Kuda Rumput
Sapi Rumput

-2004.xls
Kolom A Kolom B
Binatang Makanannya <<< Header
Kucing Ikan
Anjing Daging
Kelinci Wortel
Kuda Wortel
Sapi Rumput

-2005.xls
Kolom A Kolom B
Binatang Makanannya <<< Header
Kucing Daging
Anjing Tulang
Kelinci Rumput
Kuda Rumput
Sapi Jagung

Saya ingin menggenerate file2 yang judulnya berdasarkan makanannya, jadi ada
file Ikan.xls, wortel.wls, rumput.xls, daging.xls, tulang.xls, dan jagung.xls.
Masing2 file terdiri dari tiga sheet, yaitu 2003, 2004, dan 2005. Isin rownya
hanya header dan row yang pada kolom makanan sesuai dengan nama filenya.

Terima Kasih,
Akbar Jamaluddin Arsyad

===========================================================
mod note:
Pendekar bertangan kosong memang menandakan kesaktiannya.
Tetapi Penanya Kasus datang bertangan kosong [tanpa melampirkan
workbook kasusnya] > menunjukkan kurang seriusnya.
(proses menjawaban kasus tsb memerlukan workbook berisi contoh data)

Atau anda memang MENYURUH calon penanggap membuatkan workbook
untuk anda?
===========================================================

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)

5b.

Function untuk Goal Seek

Posted by: "Tauffik Effendi" taufike@pertamina.com

Wed Jun 22, 2011 7:58 pm (PDT)

Dear All,

Apakah ada yang bisa bantu saya membuat function goal seek di visual basic editor di file terlampir.

Kalao saya record macro, goal seek harus dijalankan satu-persatu tiap cell atau langsung menjalankan secara keseluruhan.

Diinginkan membuat function goal seek sehingga formulanya bisa di drag ke bawah jika ada data tambahan tiap hari…

Terima-kasih atas bantuannya


***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)

5c.

Re: Function untuk Goal Seek

Posted by: "zainul ulum [yahoo]" zainul_ulum@yahoo.com   zainul_ulum

Wed Jun 22, 2011 8:25 pm (PDT)

Kalo dilihat dari rumusnya bisa nggak pakai goal seek:

B5=SQRT(250/C5)

Seandainya ada rumus yang lain bisa menggunakan fasilitas solver untuk multiple goal seek.

Silahkan baca di http://cadex.wordpress.com/2011/01/08/gen-pmg-03-pemakaian-solver-untuk-multiple-goal-seek/

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of Tauffik Effendi
Sent: Thursday, June 23, 2011 9:59 AM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Function untuk Goal Seek

Dear All,

Apakah ada yang bisa bantu saya membuat function goal seek di visual basic editor di file terlampir.

Kalao saya record macro, goal seek harus dijalankan satu-persatu tiap cell atau langsung menjalankan secara keseluruhan.

Diinginkan membuat function goal seek sehingga formulanya bisa di drag ke bawah jika ada data tambahan tiap hari…

Terima-kasih atas bantuannya

***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)

5d.

Re: Function untuk Goal Seek

Posted by: "Tauffik Effendi" taufike@pertamina.com

Wed Jun 22, 2011 8:42 pm (PDT)

Terima kasih pak zainul
Tapi file itu hanya contoh saja pak, karena perhitungan aslinya melibatkan banyak cell sehingga rumus B5=SQRT (250/C5) tidak dapat digunakan

Ada yang bisa bantu ????

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of zainul ulum [yahoo]
Sent: Thursday, June 23, 2011 10:25 AM
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Function untuk Goal Seek


Kalo dilihat dari rumusnya bisa nggak pakai goal seek:
B5=SQRT(250/C5)

Seandainya ada rumus yang lain bisa menggunakan fasilitas solver untuk multiple goal seek.
Silahkan baca di http://cadex.wordpress.com/2011/01/08/gen-pmg-03-pemakaian-solver-untuk-multiple-goal-seek/


From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of Tauffik Effendi
Sent: Thursday, June 23, 2011 9:59 AM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Function untuk Goal Seek


Dear All,

Apakah ada yang bisa bantu saya membuat function goal seek di visual basic editor di file terlampir.

Kalao saya record macro, goal seek harus dijalankan satu-persatu tiap cell atau langsung menjalankan secara keseluruhan.

Diinginkan membuat function goal seek sehingga formulanya bisa di drag ke bawah jika ada data tambahan tiap hari…

Terima-kasih atas bantuannya


***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****

***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)

5e.

Re: Function untuk Goal Seek

Posted by: "zainul ulum [yahoo]" zainul_ulum@yahoo.com   zainul_ulum

Wed Jun 22, 2011 8:50 pm (PDT)

Kalo rumusnya tidak bisa share di milis, bisa pakai solver

Kalo ada rumusnya bisa dishare (diketahui formulanya) pakai iterative calculation.

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of Tauffik Effendi
Sent: Thursday, June 23, 2011 10:43 AM
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Function untuk Goal Seek

Terima kasih pak zainul

Tapi file itu hanya contoh saja pak, karena perhitungan aslinya melibatkan banyak cell sehingga rumus B5=SQRT (250/C5) tidak dapat digunakan

Ada yang bisa bantu ????

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of zainul ulum [yahoo]
Sent: Thursday, June 23, 2011 10:25 AM
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Function untuk Goal Seek

Kalo dilihat dari rumusnya bisa nggak pakai goal seek:

B5=SQRT(250/C5)

Seandainya ada rumus yang lain bisa menggunakan fasilitas solver untuk multiple goal seek.

Silahkan baca di http://cadex.wordpress.com/2011/01/08/gen-pmg-03-pemakaian-solver-untuk-multiple-goal-seek/

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of Tauffik Effendi
Sent: Thursday, June 23, 2011 9:59 AM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Function untuk Goal Seek

Dear All,

Apakah ada yang bisa bantu saya membuat function goal seek di visual basic editor di file terlampir.

Kalao saya record macro, goal seek harus dijalankan satu-persatu tiap cell atau langsung menjalankan secara keseluruhan.

Diinginkan membuat function goal seek sehingga formulanya bisa di drag ke bawah jika ada data tambahan tiap hari…

Terima-kasih atas bantuannya

***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****

***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)

5f.

Re: Function untuk Goal Seek

Posted by: "Sudarsono Suhenk" jkssbma@live.com   jkssbma

Wed Jun 22, 2011 8:51 pm (PDT)

kalau menggunakan makro tentu harus mengetahui : 1. kriteria apa yang diperlukan2. rumus rumus yang dipakai untuk memperoleh nilai yang diinginkan. 3. variabel2 yang diperlukan untuk memperoleh nilai yang diinginkan4. nilai ketelitian ( bias ) antara nilai real dan nilai hasil perhitungan.sebaiknya file excelnya di sertakan
To: belajar-excel@yahoogroups.com
From: taufike@pertamina.com
Date: Thu, 23 Jun 2011 03:42:49 +0000
Subject: RE: [belajar-excel] Function untuk Goal Seek

Terima kasih pak zainul
Tapi file itu hanya contoh saja pak, karena perhitungan aslinya melibatkan banyak cell sehingga rumus B5=SQRT (250/C5) tidak dapat digunakan

Ada yang bisa bantu ????


From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
On Behalf Of zainul ulum [yahoo]

Sent: Thursday, June 23, 2011 10:25 AM

To: belajar-excel@yahoogroups.com

Subject: RE: [belajar-excel] Function untuk Goal Seek

Kalo dilihat dari rumusnya bisa nggak pakai goal seek:
B5=SQRT(250/C5)

Seandainya ada rumus yang lain bisa menggunakan fasilitas solver untuk multiple goal seek.
Silahkan baca di

http://cadex.wordpress.com/2011/01/08/gen-pmg-03-pemakaian-solver-untuk-multiple-goal-seek/



From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
On Behalf Of Tauffik Effendi

Sent: Thursday, June 23, 2011 9:59 AM

To: belajar-excel@yahoogroups.com

Subject: [belajar-excel] Function untuk Goal Seek

Dear All,

Apakah ada yang bisa bantu saya membuat function goal seek di visual basic editor di file terlampir.

Kalao saya record macro, goal seek harus dijalankan satu-persatu tiap cell atau langsung menjalankan secara keseluruhan.

Diinginkan membuat function goal seek sehingga formulanya bisa di drag ke bawah jika ada data tambahan tiap hari…

Terima-kasih atas bantuannya



***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message
or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of
the information contained in this communication nor for any delay in its receipt. *****

***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If
you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. *****

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)

5g.

Re: Function untuk Goal Seek

Posted by: "Tauffik Effendi" taufike@pertamina.com

Wed Jun 22, 2011 8:56 pm (PDT)

Terlampir rumusnya… pak Zainul
Ditunggu bantuannya
Terima-kasih

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of zainul ulum [yahoo]
Sent: Thursday, June 23, 2011 10:50 AM
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Function untuk Goal Seek


Kalo rumusnya tidak bisa share di milis, bisa pakai solver
Kalo ada rumusnya bisa dishare (diketahui formulanya) pakai iterative calculation.

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of Tauffik Effendi
Sent: Thursday, June 23, 2011 10:43 AM
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Function untuk Goal Seek


Terima kasih pak zainul
Tapi file itu hanya contoh saja pak, karena perhitungan aslinya melibatkan banyak cell sehingga rumus B5=SQRT (250/C5) tidak dapat digunakan

Ada yang bisa bantu ????

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of zainul ulum [yahoo]
Sent: Thursday, June 23, 2011 10:25 AM
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Function untuk Goal Seek


Kalo dilihat dari rumusnya bisa nggak pakai goal seek:
B5=SQRT(250/C5)

Seandainya ada rumus yang lain bisa menggunakan fasilitas solver untuk multiple goal seek.
Silahkan baca di http://cadex.wordpress.com/2011/01/08/gen-pmg-03-pemakaian-solver-untuk-multiple-goal-seek/


From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of Tauffik Effendi
Sent: Thursday, June 23, 2011 9:59 AM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Function untuk Goal Seek


Dear All,

Apakah ada yang bisa bantu saya membuat function goal seek di visual basic editor di file terlampir.

Kalao saya record macro, goal seek harus dijalankan satu-persatu tiap cell atau langsung menjalankan secara keseluruhan.

Diinginkan membuat function goal seek sehingga formulanya bisa di drag ke bawah jika ada data tambahan tiap hari…

Terima-kasih atas bantuannya


***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****
***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****

***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)

5h.

Re: Function untuk Goal Seek

Posted by: "zainul ulum [yahoo]" zainul_ulum@yahoo.com   zainul_ulum

Wed Jun 22, 2011 9:08 pm (PDT)

Dicoba pakai solver aja ya…

Buat rumus di [CG3] =MIN(CG4:CG11)

Setting solver sbb:

Click solve….

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of Tauffik Effendi
Sent: Thursday, June 23, 2011 10:57 AM
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Function untuk Goal Seek

Terlampir rumusnya… pak Zainul

Ditunggu bantuannya

Terima-kasih

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of zainul ulum [yahoo]
Sent: Thursday, June 23, 2011 10:50 AM
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Function untuk Goal Seek

Kalo rumusnya tidak bisa share di milis, bisa pakai solver

Kalo ada rumusnya bisa dishare (diketahui formulanya) pakai iterative calculation.

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of Tauffik Effendi
Sent: Thursday, June 23, 2011 10:43 AM
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Function untuk Goal Seek

Terima kasih pak zainul

Tapi file itu hanya contoh saja pak, karena perhitungan aslinya melibatkan banyak cell sehingga rumus B5=SQRT (250/C5) tidak dapat digunakan

Ada yang bisa bantu ????

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of zainul ulum [yahoo]
Sent: Thursday, June 23, 2011 10:25 AM
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Function untuk Goal Seek

Kalo dilihat dari rumusnya bisa nggak pakai goal seek:

B5=SQRT(250/C5)

Seandainya ada rumus yang lain bisa menggunakan fasilitas solver untuk multiple goal seek.

Silahkan baca di http://cadex.wordpress.com/2011/01/08/gen-pmg-03-pemakaian-solver-untuk-multiple-goal-seek/

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of Tauffik Effendi
Sent: Thursday, June 23, 2011 9:59 AM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Function untuk Goal Seek

Dear All,

Apakah ada yang bisa bantu saya membuat function goal seek di visual basic editor di file terlampir.

Kalao saya record macro, goal seek harus dijalankan satu-persatu tiap cell atau langsung menjalankan secara keseluruhan.

Diinginkan membuat function goal seek sehingga formulanya bisa di drag ke bawah jika ada data tambahan tiap hari…

Terima-kasih atas bantuannya

***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****

***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****

***** This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. PT Pertamina (Persero) is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. *****

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)

6a.

Masalah penomoran lagi ...

Posted by: "Yulius Minsai" yulius_minsai@yahoo.co.id   yulius_minsai

Wed Jun 22, 2011 7:35 pm (PDT)

Dear semua,

Aku punya PR seperti terlampir di bawah, sudah coba berbagai macam pake
contoh data milis sebelumnya.., tp msh mentok ..

Mohon pencerahannya yaa ..

Data : : data tanggal : Output
penomoran yg diinginkan :

KTU-SMD : 20/06/2011 : KTU-2006-001

KTU-BPP : 23/06/2011 : KTU-2306-002

GDR-PNJ : 23/06/2011 : GDR-2306-003

STK-PNJ : 26/06/2011 : STK-2606-004

STK-PNJ : 27/06/2011 : STK-2706-005

Salam,

Yulius

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)

6b.

Re: Masalah penomoran lagi ...

Posted by: "Sudarsono Suhenk" jkssbma@live.com   jkssbma

Wed Jun 22, 2011 8:16 pm (PDT)

awal baris=LEFT(B2,3) & "-"& YEAR(C2)& "-" &TEXT(MONTH(C2),"0#") & "-" & TEXT(1,"00#") baris berikutnya :=LEFT(B3,3)&"-"&YEAR(C3)&"-"&TEXT(MONTH(C3),"0#")&"-"&TEXT(VALUE(RIGHT(F2,3))+1,"00#")

B
C
D


2












KTU-SMD












6/20/2011
KTU-2011-06-001


3

KTU-BPP

6/23/2011
KTU-2011-06-002


4

GDR-PNJ

6/23/2011
GDR-2011-06-003


5

STK-PNJ

6/26/2011
STK-2011-06-004


6

STK-PNJ

6/27/2011
STK-2011-06-005


To: belajar-excel@yahoogroups.com
From: yulius_minsai@yahoo.co.id
Date: Thu, 23 Jun 2011 10:35:16 +0800
Subject: [belajar-excel] Masalah penomoran lagi ...

Dear semua, Aku punya PR seperti terlampir di bawah, sudah coba berbagai macam pake contoh data milis sebelumnya.., tp msh mentok ..Mohon pencerahannya yaa .. Data : : data tanggal : Output penomoran yg diinginkan :KTU-SMD : 20/06/2011 : KTU-2006-001KTU-BPP : 23/06/2011 : KTU-2306-002GDR-PNJ : 23/06/2011 : GDR-2306-003STK-PNJ : 26/06/2011 : STK-2606-004STK-PNJ : 27/06/2011 : STK-2706-005 Salam,Yulius

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)

6c.

Re: Masalah penomoran lagi ...

Posted by: "STDEV(i)" setiyowati.devi@gmail.com   siti_vi

Wed Jun 22, 2011 8:17 pm (PDT)

kalau DATE-FORMAT pada PC anda (control panel > regional setting) *=
dd/MM/yyyy*
=LEFT(A2,FIND("-",A2))&TEXT(B2,"DDMM")&"-"&TEXT(ROWS($A$2:A2),"000")

kalau DATE-FORMAT pada PC anda (control panel > regional setting) *=
MM/dd/yyyy*
=LEFT(A2,FIND("-",A2))&SUBSTITUTE(LEFT(B2,5),"/","")&"-"&TEXT(ROWS($A$2:A2
),"000")

2011/6/23 Yulius Minsai <yulius_minsai@yahoo.co.id>

> **
>
> Dear semua,****
>
> Aku punya PR seperti terlampir di bawah, sudah coba berbagai macam pake
> contoh data milis sebelumnya.., tp msh mentok ..
>
> Mohon pencerahannya yaa ..****
>
> ** **
>
> Data : : data tanggal : Output
> penomoran yg diinginkan :****
>
> KTU-SMD : 20/06/2011 : KTU-2006-001****
>
> KTU-BPP : 23/06/2011 : KTU-2306-002****
>
> GDR-PNJ : 23/06/2011 : GDR-2306-003****
>
> STK-PNJ : 26/06/2011 : STK-2606-004***
> *
>
> STK-PNJ : 27/06/2011 : STK-2706-005***
> *
>
> ** **
>
> Salam,****
>
> Yulius****
>
>
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)

6d.

Re: Masalah penomoran lagi ...

Posted by: "STDEV(i)" setiyowati.devi@gmail.com   siti_vi

Wed Jun 22, 2011 8:28 pm (PDT)

lampiran
(hasil kerja bakti)

2011/6/23 STDEV(i) <setiyowati.devi@gmail.com>

> kalau DATE-FORMAT pada PC anda (control panel > regional setting) *=
> dd/MM/yyyy*
> =LEFT(A2,FIND("-",A2))&TEXT(B2,"DDMM")&"-"&TEXT(ROWS($A$2:A2),"000")
>
> kalau DATE-FORMAT pada PC anda (control panel > regional setting) *=
> MM/dd/yyyy*
> =LEFT(A2,FIND("-",A2))&SUBSTITUTE(LEFT(B2,5),"/","")&"-"&TEXT(ROWS($A$2:A2
> ),"000")
>
>
> 2011/6/23 Yulius Minsai <yulius_minsai@yahoo.co.id>
>
>> **
>>
>> Data : : data tanggal : Output penomoran yg diinginkan :
>>
>> KTU-SMD : 20/06/2011 : KTU-2006-001****
>>
>> KTU-BPP : 23/06/2011 : KTU-2306-002****
>>
>> GDR-PNJ : 23/06/2011 : GDR-2306-003****
>>
>> STK-PNJ : 26/06/2011 : STK-2606-004****
>>
>> STK-PNJ : 27/06/2011 : STK-2706-005****
>>
>> Salam,
>>
>> Yulius
>>
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)

7a.

Re: Bon Toko

Posted by: "Yvonne" yvonne@astridogroup.com

Wed Jun 22, 2011 8:28 pm (PDT)

tp kenapa ya saya coba praktekkan persis sama dengan contoh sampe copy rumusnya tetap tidak bisa...
selalu jawabannya N/A

----- Original Message -----
From: STDEV(i)
To: belajar-excel@yahoogroups.com
Sent: Tuesday, June 21, 2011 4:45 PM
Subject: Re: [belajar-excel] Bon Toko

filtering dengan formula !!

topik ini memang masih sering ditanyakan
sampai formula utamanya (batch Array Formula) serasa sudah hafal diujung jari...
=SMALL(IF((Trans!B3:B29=B1)*(Trans!C3:C29="TST"),ROW(1:27)),ROW(1:27))

lihat lampiran

2011/6/21 Yvonne <yvonne@astridogroup.com>

hehehe maaf lupa mr.
ini saya kirim ulang.

----- Original Message -----
From: Mr. Kid
Sent: Tuesday, June 21, 2011 4:14 PM

Mbok yao disempatkan ngezip atau mengurangi jumlah contoh data.
hehehe....
Kid.

2011/6/21 Yvonne <yvonne@astridogroup.com>
>
> Dear TTM,
> Mohon bantuannya untuk membuat bon toko seperti attachment.
> Penjelasan ada di dalam file excel yang saya kirimkan.
> Terima kasih.

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

7b.

Re: Bon Toko

Posted by: "STDEV(i)" setiyowati.devi@gmail.com   siti_vi

Wed Jun 22, 2011 8:37 pm (PDT)

kemungkinan besar karena : di sheet Yvonne
*TYPE DATA* yg dibandingkan = TIDAK SAMA

(lihat rumus di bawah ini)

=SMALL(IF((*Trans!B3:B29=B1*)*(*Trans!C3:C29="TST"*),ROW(1:27)),ROW(1:27))

Trans!(B3:B29) type datanya harus DATE ; B1 juga harus DATE
Trans!(C3:C29) type datanya harus TEXT ; "TST" ,memang sudah TEXT

2011/6/23 Yvonne <yvonne@astridogroup.com>

> **
>
> tp kenapa ya saya coba praktekkan persis sama dengan contoh sampe copy
> rumusnya tetap tidak bisa...
> selalu jawabannya N/A
>
>
> ----- Original Message -----
> *From:* STDEV(i) <setiyowati.devi@gmail.com>
> *To:* belajar-excel@yahoogroups.com
> *Sent:* Tuesday, June 21, 2011 4:45 PM
> *Subject:* Re: [belajar-excel] Bon Toko
>
>
> filtering dengan formula !!
> topik ini memang masih sering ditanyakan
> sampai formula utamanya (batch Array Formula) serasa sudah hafal diujung
> jari...
> =SMALL(IF((Trans!B3:B29=B1)*(Trans!C3:C29="TST"),ROW(1:27)),ROW(1:27))
>
> lihat lampiran
>
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

Recent Activity

•  13
  New Members

Visit Your Group

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Find helpful tips

for Moderators

on the Yahoo!

Groups team blog.

Yahoo! Groups

Parenting Zone

Community resources

for parents

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web

Messages

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

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe