Selasa, 31 Juli 2012

[ExcelVBA] Worksheet update Automatically from same cell in another workbook

 

Hi all, thanks for being there to help out. I am looking to have a workbook that automatically updates itself from the same cell in another workbook.
I.E. Workbook 1 has a few worksheets, 1 of which is called orders.
I would like to enter the info into a different workbook (workbook 2), with 1 worksheet called orders, so that when it is entered into workbook 2, into cell b5, it should automatically update (or at least when opened) in Workbook 1, worksheet "orders", into cell b5.
If no data in workbook 2, workbook 1 should be blank.
(If it is relevant: I want to do this because I enter some info from a mobile device, and workbook 1 has formulas that get changed when the file is opened on the mobile device. This way I can make a simple input workbook (2) for orders, and then it will get pulled into workbook 1 where the formulas can all go to work etc. I will set it up so that each cell should be the same in both workbooks. ).
Thanks so much for any help!

__._,_.___
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....

[belajar-excel] Digest Number 1711

15 New Messages

Digest #1711
1a
1b
Re: mencocokan data by "the_x_mikey@yahoo.com" the_x_mikey
1c
Re: mencocokan data by "Fano The Miner"
1d
Re: mencocokan data by "Kid Mr." nmkid.family@ymail.com
1e
Fw: [belajar-excel] mencocokan data by "hendrik karnadi" hendrikkarnadi
2a
Re: rumus filter data by "daniel_aho"
4a
mencari, mengambil dan menempel data by "Triyoso Rustamaji" trustamaji
4b
Re: mencari, mengambil dan menempel data by "Kid Mr." nmkid.family@ymail.com
5b
Re: menjumlah data kembar by "dwint ruswanto"

Messages

Tue Jul 31, 2012 1:36 am (PDT) . Posted by:

"MIKE"

Mohon bantuannya rumus yg digunakan untuk mencocokan data...

tq
mike

PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER:

This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager. This message contains confidential information
and is intended only for the individual named. If you are not the
named addressee you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately by e-mail if you have
received this e-mail by mistake and delete this e-mail from your
system. If you are not the intended recipient you are notified that
disclosing, copying, distributing or taking any action in reliance on
the contents of this information is strictly prohibited.

Tue Jul 31, 2012 1:47 am (PDT) . Posted by:

"the_x_mikey@yahoo.com" the_x_mikey

Hi Mike

Pakai rumus if(a3=a4,"cocok","tidak cocok")
-----Original Message-----
From: "MIKE" <personalia_manado@ag.co.id>
Sender: belajar-excel@yahoogroups.com
Date: Tue, 31 Jul 2012 11:39:06
To: <belajar-excel@yahoogroups.com>
Reply-To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] mencocokan data

Mohon bantuannya rumus yg digunakan untuk mencocokan data...

tq
mike












































PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER:

This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager. This message contains confidential information
and is intended only for the individual named. If you are not the
named addressee you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately by e-mail if you have
received this e-mail by mistake and delete this e-mail from your
system. If you are not the intended recipient you are notified that
disclosing, copying, distributing or taking any action in reliance on
the contents of this information is strictly prohibited.

Tue Jul 31, 2012 1:47 am (PDT) . Posted by:

"Fano The Miner"

Coba dengan rumus ini

=IF(A3=B3,"COCOK","TIDAK COCOK")

Salam

fano

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
On Behalf Of MIKE
Sent: Tuesday, July 31, 2012 12:39 PM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] mencocokan data

Mohon bantuannya rumus yg digunakan untuk mencocokan data...

tq

mike

PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER:

This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager. This message contains confidential information
and is intended only for the individual named. If you are not the
named addressee you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately by e-mail if you have
received this e-mail by mistake and delete this e-mail from your
system. If you are not the intended recipient you are notified that
disclosing, copying, distributing or taking any action in reliance on
the contents of this information is strictly prohibited.

Tue Jul 31, 2012 2:03 am (PDT) . Posted by:

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

Coba :
=IF(A3=B3,"","TIDAK ")&"COCOK"

Wassalam,
Kid.

On Tue, Jul 31, 2012 at 3:39 PM, Fano The Miner <fano.theminer@gmail.com>wrote:

> **
>
>
> Coba dengan rumus ini****
>
> ** **
>
> =IF(A3=B3,"COCOK","TIDAK COCOK")****
>
> ** **
>
> Salam****
>
> fano****
>
> ** **
>
> *From:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com] *On Behalf Of *MIKE
> *Sent:* Tuesday, July 31, 2012 12:39 PM
>
> *To:* belajar-excel@yahoogroups.com
> *Subject:* [belajar-excel] mencocokan data****
>
> ** **
>
> ****
>
> Mohon bantuannya rumus yg digunakan untuk mencocokan data...****
>
> ****
>
> tq****
>
> mike****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER:****
>
> ** **
>
> This email and any files transmitted with it are confidential and****
>
> intended solely for the use of the individual or entity to whom they****
>
> are addressed. If you have received this email in error please notify****
>
> the system manager. This message contains confidential information****
>
> and is intended only for the individual named. If you are not the****
>
> named addressee you should not disseminate, distribute or copy this****
>
> e-mail. Please notify the sender immediately by e-mail if you have****
>
> received this e-mail by mistake and delete this e-mail from your****
>
> system. If you are not the intended recipient you are notified that****
>
> disclosing, copying, distributing or taking any action in reliance on****
>
> the contents of this information is strictly prohibited.****
>
> ****
>
>
>

Tue Jul 31, 2012 2:11 am (PDT) . Posted by:

"hendrik karnadi" hendrikkarnadi

Khusus untuk Text, hati2 dengan karakter kosong (spasi) dibelakang huruf.
Untuk menceknya pakai formula : = Len(text).

File terlampir.

Salam,
Hendrik Karnadi

----- Forwarded Message -----
From: Fano The Miner <fano.theminer@gmail.com>
To: belajar-excel@yahoogroups.com
Sent: Tuesday, 31 July 2012, 15:39
Subject: RE: [belajar-excel] mencocokan data


 
Coba
dengan rumus ini
 
=IF(A3=B3,"COCOK","TIDAK
COCOK")
 
Salam
fano
 
From:belajar-excel@yahoogroups.com
[mailto:belajar-excel@yahoogroups.com] On Behalf Of MIKE
Sent: Tuesday, July 31, 2012 12:39 PM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] mencocokan data
 
 
Mohon bantuannya rumus yg digunakan untuk mencocokan data...
 
tq
mike
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER:
 
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager. This message contains confidential information
and is intended only for the individual named. If you are not the
named addressee you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately by e-mail if you have
received this e-mail by mistake and delete this e-mail from your
system. If you are not the intended recipient you are notified that
disclosing, copying, distributing or taking any action in reliance on
the contents of this information is strictly prohibited.

Tue Jul 31, 2012 5:33 pm (PDT) . Posted by:

"MIKE"

terima kasih semuanya....
mantap....

----- Original Message -----
From: hendrik karnadi
To: belajar-excel@yahoogroups.com
Sent: Tuesday, July 31, 2012 5:11 PM
Subject: Fw: [belajar-excel] mencocokan data

Khusus untuk Text, hati2 dengan karakter kosong (spasi) dibelakang huruf.
Untuk menceknya pakai formula : = Len(text).

File terlampir.

Salam,
Hendrik Karnadi

----- Forwarded Message -----
From: Fano The Miner <fano.theminer@gmail.com>
To: belajar-excel@yahoogroups.com
Sent: Tuesday, 31 July 2012, 15:39
Subject: RE: [belajar-excel] mencocokan data

Coba dengan rumus ini

=IF(A3=B3,"COCOK","TIDAK COCOK")

Salam
fano

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of MIKE
Sent: Tuesday, July 31, 2012 12:39 PM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] mencocokan data

Mohon bantuannya rumus yg digunakan untuk mencocokan data...

tq
mike

PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER: This email and any files transmitted with it are confidential andintended solely for the use of the individual or entity to whom theyare addressed. If you have received this email in error please notifythe system manager. This message contains confidential informationand is intended only for the individual named. If you are not thenamed addressee you should not disseminate, distribute or copy thise-mail. Please notify the sender immediately by e-mail if you
havereceived this e-mail by mistake and delete this e-mail from yoursystem. If you are not the intended recipient you are notified thatdisclosing, copying, distributing or taking any action in reliance onthe contents of this information is strictly prohibited.

PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER:

This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager. This message contains confidential information
and is intended only for the individual named. If you are not the
named addressee you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately by e-mail if you have
received this e-mail by mistake and delete this e-mail from your
system. If you are not the intended recipient you are notified that
disclosing, copying, distributing or taking any action in reliance on
the contents of this information is strictly prohibited.

Tue Jul 31, 2012 1:37 am (PDT) . Posted by:

"daniel_aho"

OK terima kasih banyak Mr Kid,

Solusi yang sangat membantu, seperti yang diharapkan.

Aho

On 30/07/2012 16:33, Kid Mr. wrote:
>
> Pak Aho,
>
> Untuk mendapatkan suatu data unik dari sebuah dataset bisa menggunakan
> beberapa cara seperti :
> - array formula unique records,
> - pivot table
> - fitur advanced filter
> - macro (VBA)
>
> File terlampir tanpa VBA.
>
> Wassalam,
> Kid.
>
> 2012/7/30 daniel_aho <daniel_aho@yahoo.co.id
> <mailto:daniel_aho@yahoo.co.id>>
>
> Dear Para master2 Excel,
>
> mau tanya bisa tolong dibantu, bagaimana rumus untuk filter data,
> file
> terlampir untuk jelasnya,
>
> Terima kasih sebelumnya,
>
> aho
>
>
>

Tue Jul 31, 2012 8:41 am (PDT) . Posted by:

"HENDRA VERNANDO" hendravernan

cell AH .keterangan
=IF(AF7=0,"selesai","Dalam Proses")
2.deadline akhir
=WORKDAY("9-may-2012",60)
3.Internal Proses
=DAYS360(P7,B7)

Mohon Bantuannya Mas,Gmna Cara Biar Data Saya Bisa Lancar Dengan Sistim Yg Baik.
deadline dua bulan itu ketentuanya > 2 bulan setelah request order,
ada ym mas,,biar bisa konsultasi

--- Mods ---
Huruf kapital seluruhnya pada sebuah kalimat dalam dunia maya bisa diartikan Anda sedang berteriak dan minta segera diperhatikan. Sebaiknya penggunaan huruf kapital diletakkan sesuai porsinya dan lingkungannya.

Tujukanlah posting kepada seluruh member milis, karena milis adalah wadah berkumpul banyak individu.
------------

Tue Jul 31, 2012 8:42 am (PDT) . Posted by:

"Triyoso Rustamaji" trustamaji

yang terhormat BeExeller,

mohon dicarikan solusi, 
1. dari data yang banyak (ratusan ribu)... :)
2. ingin saya mengambil / memilih dengan pencarian karakter tertentu.
3. dan setelah ditemukan dapat tertempel langsung di sheet tertentu.
4. dan bagaimana "rumus" an yang baik/pas jika pencarian di lakukan banyak karakter (exp: NIK).

5. terimakasih, semoga ilmunya mendapat balasan dari Yang Kuasa.....

file tersebut kami sertakan.

Tue Jul 31, 2012 9:08 am (PDT) . Posted by:

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

Pak Tri,

File terlampir memanfaatkan fitur Advanced Filter.
Jika lelah dan bosan karena harus menjalankan advanced filter, lakukan :
>> record Macro dan
>> jalankan advanced filter 1 kali,
>> kemudian stop macro
>> buat shape dan kllik kanan si shape, pilih assign macro, tunjuk nama
macro hasil record dan tekan OK
>> save file As .xlsm (macro enabled) atau .xlsb (binary)
>> coba pencet si shape
>> nanti tinggal ubah dikit supaya jadi dinamis, mangsute,
tinggal belajar VBA (macro) dikit supaya bisa membuatnya jadi dinamis
mengikuti berubahnya jumlah record data, column dan record kriteria, column
tabel hasil.

Wassalam,
Kid.

2012/7/31 Triyoso Rustamaji <trustamaji@yahoo.com>

> **
>
>
> yang terhormat BeExeller,
>
> mohon dicarikan solusi,
> 1. dari data yang banyak (ratusan ribu)... :)
> 2. ingin saya mengambil / memilih dengan pencarian karakter tertentu.
> 3. dan setelah ditemukan dapat tertempel langsung di sheet tertentu.
> 4. dan bagaimana "rumus" an yang baik/pas jika pencarian di lakukan banyak
> karakter (exp: NIK).
>
> 5. terimakasih, semoga ilmunya mendapat balasan dari Yang Kuasa.....
>
> file tersebut kami sertakan.
>
>
>

Tue Jul 31, 2012 3:33 pm (PDT) . Posted by:

"Mohammed Asseggaf" man_jufri

Halo para EXCEL man

Mau ngejelasin masalahnya susah menggunakan kalimat, lebih baik kita terjun
ke file di attachmentnya aja deh ;)

Setelah itu baru deh kita bahas

Makasih yah

Muh.Saggaf

Tue Jul 31, 2012 6:06 pm (PDT) . Posted by:

"M. Ferdiansyah" muh_f3rdi4n5yah

Dear pak saggaf,

Ikut nimbrung ya..

file terlampir..

semoga sesuai harapan..

regard,

Ferdy

> Halo para EXCEL man
>
>
>
> Mau ngejelasin masalahnya susah menggunakan kalimat, lebih baik kita
> terjun
> ke file di attachmentnya aja deh ;)
>
>
>
> Setelah itu baru deh kita bahas
>
>
>
> Makasih yah
>
>
>
> Muh.Saggaf
>
>

____________ _________ _________ _________ _________ _________ _________
Notice : This e-mail (and any attachment) is strictly confidential and
is intended only for the addressee(s)above. Is strictly prohibited to
forward, print, copy, or otherwise reproduce this message that would
allow it to be viewed by any individual not originally listed as
a recipient(s). If you have received this e-mail in error, please
immediately notify the sender and delete this message. The opinions
expressed in this e-mail (and any attachment) are those of the individual
sender and may not necessarily reflect the views of PT Pusri.
____________ _________ _________ _________ _________ _________ _________

Tue Jul 31, 2012 6:15 pm (PDT) . Posted by:

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

Pak Muh. Saggaf,

Coba manfaatkan pivot table.

Bisa juga dengan formula :
1. Minimum atau maksimum dengan kriteria : (array formula)
=Min( IF( data=nilai_kriteria , nilai_dikalkulasi ) )
Contoh :
=MIN(IF($A$2:$A$17=$F2,$B$2:$B$17))

Untuk Maksimum, ganti Min dengan Max
atau dipersingkat menjadi :
=Max( ( data=nilai_kriteria ) * nilai_dikalkulasi )
Contoh :
=MAX( ($A$2:$A$17=$F2) * $B$2:$B$17 )

2. Menyusun data unique (dalam hal ini untuk salary) dari suatu data
bertipe numerik yang harus memenuhi kriteria tertentu
bisa dilakukan dengan array formula Small( IF() )
Contoh :
=SMALL( IF( ($A$2:$A$17=$G$7) * ($B$2:$B$17>N(F9)) , $B$2:$B$17 ) ,
ROW()-ROW($F$9) )
Bahasa manusia :
"Ambil data ke- hasil kalkulasi ROW()-ROW($F$9) dari suatu data larik yang
telah diurutkan menaik (SMALL),
dimana data yang diurutkan tersebut adalah data bertipe numerik dikolom
salary ($B$2:$B$17) yang memenuhi kriteria tertentu.
Kriteria tersebut adalah
> untuk Occupation ($A$2:$A$17) yang sama dengan (=) pilihan user ($G$7)
DAN (*)
> nilai data salary ($B$2:$B$17) yang lebih dari (>) nilai data salary
terakhir tabel hasil ini N(F9)"

Array formula ini bisa menghasilkan error value ketika sudah tidak
ditemukan lagi data yang memenuhi kriteria yang ditentukan.
Untuk antisipasi Error value, gunakan IFError (xl2007 keatas) atau IsError
(semua versi Excel)

3. Hitung cacah dengan 1 kriteria berdasar lebih dari 1 kriteria adalah
menjumlahkan hasil perkalian dari proses pembandingan setiap kriterianya.
=SUMPRODUCT( ($A$2:$A$17=$G$7) * ($B$2:$B$17=F10) )
Bahasa manusia :
"Jumlahkan hasil perkalian setiap item dalam larik berikut :
> data salesman [$A$2:$A$17] sama dengan [=] pilihan user [$G$7], yang
pasti menghasilkan TRUE (setara 1) atau FALSE (setara 0)
DAN (*)
> nilai data salary [$B$2:$B$17] sama dengan [=] nilai salary dalam daftar
hasil [F10], yang pasti menghasilkan TRUE (setara 1) atau FALSE (setara 0)"

Jadi prosesnya akan menjumlahkan larik yang berisi angka 1 atau 0.
Hasilnya adalah jumlah cacah item larik yang sesuai kriteria.

Wassalam,
Kid.

2012/8/1 Mohammed Asseggaf <seggaf@sbtcgroup.com>

> **
>
>
> Halo para EXCEL man****
>
> ** **
>
> Mau ngejelasin masalahnya susah menggunakan kalimat, lebih baik kita
> terjun ke file di attachmentnya aja deh ;)****
>
> ** **
>
> Setelah itu baru deh kita bahas****
>
> ** **
>
> Makasih yah****
>
> ** **
>
> Muh.Saggaf****
>
>
>
>

Tue Jul 31, 2012 7:36 pm (PDT) . Posted by:

"Eko NS"

permisi para mastah XL.

saya ada permasalahan yg mungkin bisa dibantu.
saya ingin menjumlahkan data piutang dari perush lain (data ratusan),
sementara piutang perush tersebut ada beberapa periode. lebih lengkapnya
ada di attach.

thanks atas bantuannya.

--
*Reminder*

*Is not about who we are speaking to, but what we are speaking about?*
*Is not about who are you, but what would you have become?*
*The more we learned, the more we understand that we need to learn more*

Tue Jul 31, 2012 7:51 pm (PDT) . Posted by:

"dwint ruswanto"

coba gunakan

=SumPproduct(($C$5:$C$25=G5)*($E$5:$E$25)) >>> Syntax-nya menyimpang dari
yang normal karena merupakan pengembangan dari para pengguna Excel

atau

=SumIf($C$5:$C$25;G5;$E$5:$E$25)

salam,

-dwint-

Pada 1 Agustus 2012 09:36, Eko NS <ekonurs@gmail.com> menulis:

> **
>
>
> permisi para mastah XL.
>
> saya ada permasalahan yg mungkin bisa dibantu.
> saya ingin menjumlahkan data piutang dari perush lain (data ratusan),
> sementara piutang perush tersebut ada beberapa periode. lebih lengkapnya
> ada di attach.
>
> thanks atas bantuannya.
>
> --
> *Reminder*
>
> *Is not about who we are speaking to, but what we are speaking about?*
> *Is not about who are you, but what would you have become?*
> *The more we learned, the more we understand that we need to learn more*
>
>
>
GROUP FOOTER MESSAGE
---------------------------------------------------------------------
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
---------------------------------------------------------------------
READ MORE....