Senin, 06 April 2015

[belajar-excel] Digest Number 3373

10 Messages

Digest #3373

Messages

Sun Apr 5, 2015 1:24 pm (PDT) . Posted by:

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

Pak HK,

Coba workbooknya dibuka. Jadi setelah membuka salah satu workbook, lakukan
buka file yang lainnya melalui menu File (bukan dengan double click dari
windows explorer)

2015-04-04 16:04 GMT+11:00 hendrik karnadi hendrikkarnadi@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Mr. Kid,
> Mengambil contoh dari jawaban Mr. Kid mengenai vlookup dengan banyak
> sheet, yaitu mengenai penulisan fungsi Indirect berikut ini,
> *=Indirect( "'[workbook lainnya.xlsb]lain-lain&#39;!ops31" )*
> , saya mencoba membuat Fungsi Indirect untuk mengambil isi sel dari work
> book lain yang ada dalam satu folder (*D:\Tes*).
>
> Folder D:\Tes terdiri dari 2 workbook, yaitu:
> - Book1.xlsx
> - Book 2.xlsx
>
> Book1, Sheet1 yang saya rujuk mempunyai isi2 sel sbb:
> sel A1 berisi kata Satu
> *sel A2 berisi kata Dua*
> sel A3 berisi kata Tiga
>
> Saya buka Book2 (Book1 dalam keadaan tertutup) kmd saya tulis fungsi
> Indirect berikut ini,
> =Indirect("'[*D:\Tes&#92;Book1.xlsx]Sheet1&#39;!$A$2*")
> pada sel A1...
> seharusnya hasilnya "*Dua*" (tanpa apostrophi)
> tetapi yang tampak adalah
> *#REF!*
>
> Pertanyaan saya :
> Dimana salahnya ?
>
> Terima kasih.
>
> Salam,
> HK
>
> NB:
> Compressed Folder "Tes" (zip file) terlampir, perlu diextract dulu ke
> folder D
>
>
>

Sun Apr 5, 2015 1:30 pm (PDT) . Posted by:

nubienubi

mungkin sudah pada tau semua paling cuma saya yang nubie yg belum tau, rumus entri data

Sun Apr 5, 2015 7:21 pm (PDT) . Posted by:

"Toby kenzo" tobykenzo

Pake VBA dengan menggunakan kaidah database, coba di buka-buka lagi yang email yang dulu-dulu, di forum ini banyak link yang mengajarkan tentang kaidah database dengan menggunakan VBA.

Pada Senin, 6 April 2015 3:30, "nubienubi@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  mungkin sudah pada tau semua paling cuma saya yang nubie yg belum tau, rumus entri data #yiv1010834125 #yiv1010834125 -- #yiv1010834125ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1010834125 #yiv1010834125ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1010834125 #yiv1010834125ygrp-mkp #yiv1010834125hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1010834125 #yiv1010834125ygrp-mkp #yiv1010834125ads {margin-bottom:10px;}#yiv1010834125 #yiv1010834125ygrp-mkp .yiv1010834125ad {padding:0 0;}#yiv1010834125 #yiv1010834125ygrp-mkp .yiv1010834125ad p {margin:0;}#yiv1010834125 #yiv1010834125ygrp-mkp .yiv1010834125ad a {color:#0000ff;text-decoration:none;}#yiv1010834125 #yiv1010834125ygrp-sponsor #yiv1010834125ygrp-lc {font-family:Arial;}#yiv1010834125 #yiv1010834125ygrp-sponsor #yiv1010834125ygrp-lc #yiv1010834125hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1010834125 #yiv1010834125ygrp-sponsor #yiv1010834125ygrp-lc .yiv1010834125ad {margin-bottom:10px;padding:0 0;}#yiv1010834125 #yiv1010834125actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1010834125 #yiv1010834125activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1010834125 #yiv1010834125activity span {font-weight:700;}#yiv1010834125 #yiv1010834125activity span:first-child {text-transform:uppercase;}#yiv1010834125 #yiv1010834125activity span a {color:#5085b6;text-decoration:none;}#yiv1010834125 #yiv1010834125activity span span {color:#ff7900;}#yiv1010834125 #yiv1010834125activity span .yiv1010834125underline {text-decoration:underline;}#yiv1010834125 .yiv1010834125attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1010834125 .yiv1010834125attach div a {text-decoration:none;}#yiv1010834125 .yiv1010834125attach img {border:none;padding-right:5px;}#yiv1010834125 .yiv1010834125attach label {display:block;margin-bottom:5px;}#yiv1010834125 .yiv1010834125attach label a {text-decoration:none;}#yiv1010834125 blockquote {margin:0 0 0 4px;}#yiv1010834125 .yiv1010834125bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1010834125 .yiv1010834125bold a {text-decoration:none;}#yiv1010834125 dd.yiv1010834125last p a {font-family:Verdana;font-weight:700;}#yiv1010834125 dd.yiv1010834125last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1010834125 dd.yiv1010834125last p span.yiv1010834125yshortcuts {margin-right:0;}#yiv1010834125 div.yiv1010834125attach-table div div a {text-decoration:none;}#yiv1010834125 div.yiv1010834125attach-table {width:400px;}#yiv1010834125 div.yiv1010834125file-title a, #yiv1010834125 div.yiv1010834125file-title a:active, #yiv1010834125 div.yiv1010834125file-title a:hover, #yiv1010834125 div.yiv1010834125file-title a:visited {text-decoration:none;}#yiv1010834125 div.yiv1010834125photo-title a, #yiv1010834125 div.yiv1010834125photo-title a:active, #yiv1010834125 div.yiv1010834125photo-title a:hover, #yiv1010834125 div.yiv1010834125photo-title a:visited {text-decoration:none;}#yiv1010834125 div#yiv1010834125ygrp-mlmsg #yiv1010834125ygrp-msg p a span.yiv1010834125yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1010834125 .yiv1010834125green {color:#628c2a;}#yiv1010834125 .yiv1010834125MsoNormal {margin:0 0 0 0;}#yiv1010834125 o {font-size:0;}#yiv1010834125 #yiv1010834125photos div {float:left;width:72px;}#yiv1010834125 #yiv1010834125photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv1010834125 #yiv1010834125photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1010834125 #yiv1010834125reco-category {font-size:77%;}#yiv1010834125 #yiv1010834125reco-desc {font-size:77%;}#yiv1010834125 .yiv1010834125replbq {margin:4px;}#yiv1010834125 #yiv1010834125ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1010834125 #yiv1010834125ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1010834125 #yiv1010834125ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1010834125 #yiv1010834125ygrp-mlmsg select, #yiv1010834125 input, #yiv1010834125 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1010834125 #yiv1010834125ygrp-mlmsg pre, #yiv1010834125 code {font:115% monospace;}#yiv1010834125 #yiv1010834125ygrp-mlmsg * {line-height:1.22em;}#yiv1010834125 #yiv1010834125ygrp-mlmsg #yiv1010834125logo {padding-bottom:10px;}#yiv1010834125 #yiv1010834125ygrp-msg p a {font-family:Verdana;}#yiv1010834125 #yiv1010834125ygrp-msg p#yiv1010834125attach-count span {color:#1E66AE;font-weight:700;}#yiv1010834125 #yiv1010834125ygrp-reco #yiv1010834125reco-head {color:#ff7900;font-weight:700;}#yiv1010834125 #yiv1010834125ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1010834125 #yiv1010834125ygrp-sponsor #yiv1010834125ov li a {font-size:130%;text-decoration:none;}#yiv1010834125 #yiv1010834125ygrp-sponsor #yiv1010834125ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1010834125 #yiv1010834125ygrp-sponsor #yiv1010834125ov ul {margin:0;padding:0 0 0 8px;}#yiv1010834125 #yiv1010834125ygrp-text {font-family:Georgia;}#yiv1010834125 #yiv1010834125ygrp-text p {margin:0 0 1em 0;}#yiv1010834125 #yiv1010834125ygrp-text tt {font-size:120%;}#yiv1010834125 #yiv1010834125ygrp-vital ul li:last-child {border-right:none !important;}#yiv1010834125

Mon Apr 6, 2015 12:25 am (PDT) . Posted by:

"Hendrik Karnadi" hendrikkarnadi

Mr Kid,
Tetap blm bisa walaupun file dibuka dua2nya dgn file open).
Kalau saya hilangkan dir nya menjadi =Indirect ("'[Book1.xlsx] Sheet1'! $A$2) baru bisa.

Salam,
HK

Sent from Samsung Mobile

<div>-------- Original message --------</div><div>From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> </div><div>Date:06/04/2015 03:24 (GMT+07:00) </div><div>To: BeExcel <belajar-excel@yahoogroups.com> </div><div>Subject: Re: [belajar-excel] vlookup dari workbook lain (satu folder) </div><div>
</div>Pak HK,

Coba workbooknya dibuka. Jadi setelah membuka salah satu workbook, lakukan buka file yang lainnya melalui menu File (bukan dengan double click dari windows explorer)

2015-04-04 16:04 GMT+11:00 hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

Mr. Kid,
Mengambil contoh dari jawaban Mr. Kid mengenai vlookup dengan banyak sheet, yaitu mengenai penulisan fungsi Indirect berikut ini,
=Indirect( "'[workbook lainnya.xlsb]lain-lain&#39;!ops31" )
, saya mencoba membuat Fungsi Indirect untuk mengambil isi sel dari work book lain yang ada dalam satu folder (D:\Tes).

Folder D:\Tes terdiri dari 2 workbook, yaitu:
- Book1.xlsx
- Book 2.xlsx

Book1, Sheet1 yang saya rujuk mempunyai isi2 sel sbb:
sel A1 berisi kata Satu
sel A2 berisi kata Dua
sel A3 berisi kata Tiga

Saya buka Book2 (Book1 dalam keadaan tertutup) kmd saya tulis fungsi Indirect berikut ini,
=Indirect("'[D:&#92;Tes\Book1.xlsx]Sheet1'!$A$2")
pada sel A1...
seharusnya hasilnya "Dua" (tanpa apostrophi)
tetapi yang tampak adalah
#REF!

Pertanyaan saya :
Dimana salahnya ?

Terima kasih.

Salam,
HK

NB:
Compressed Folder "Tes" (zip file) terlampir, perlu diextract dulu ke folder D

Mon Apr 6, 2015 12:37 am (PDT) . Posted by:

"hendrik karnadi" hendrikkarnadi

Maaf, seharusnya:=Indirect("'[Book1.xlsx]Sheet1'!$A$2")

Salam,HK
----- Forwarded Message -----
From: "Hendrik Karnadi hendrikkarnadi@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
Sent: Monday, 6 April 2015, 14:27
Subject: Re: [belajar-excel] vlookup dari workbook lain (satu folder)

  Mr Kid,Tetap blm bisa walaupun file dibuka dua2nya dgn file open).Kalau saya hilangkan dir nya menjadi =Indirect ("'[Book1.xlsx] Sheet1'! $A$2) baru bisa.
Salam,HK

Sent from Samsung Mobile

-------- Original message --------From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" Date:06/04/2015 03:24 (GMT+07:00)

To: BeExcel Subject: Re: [belajar-excel] vlookup dari workbook lain (satu folder)
  Pak HK,

Coba workbooknya dibuka. Jadi setelah membuka salah satu workbook, lakukan buka file yang lainnya melalui menu File (bukan dengan double click dari windows explorer)

2015-04-04 16:04 GMT+11:00 hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  Mr. Kid,Mengambil contoh dari jawaban Mr. Kid mengenai vlookup dengan banyak sheet, yaitu mengenai penulisan fungsi Indirect berikut ini,=Indirect( "'[workbook lainnya.xlsb]lain-lain&#39;!ops31" ), saya mencoba membuat Fungsi Indirect untuk mengambil isi sel dari work book lain yang ada dalam satu folder (D:\Tes).
Folder D:\Tes terdiri dari 2 workbook, yaitu:- Book1.xlsx- Book 2.xlsx
Book1, Sheet1 yang saya rujuk mempunyai isi2 sel sbb:sel A1 berisi kata Satusel A2 berisi kata Duasel A3 berisi kata Tiga
Saya buka Book2 (Book1 dalam keadaan tertutup) kmd saya tulis fungsi Indirect berikut ini,=Indirect("'[D:\Tes&#92;Book1.xlsx]Sheet1&#39;!$A$2")
pada sel A1...seharusnya hasilnya "Dua" (tanpa apostrophi)tetapi yang tampak adalah 
| #REF! |

Pertanyaan saya :
Dimana salahnya ?
Terima kasih.
Salam,HK

NB:Compressed Folder "Tes" (zip file) terlampir, perlu diextract dulu ke folder D


#yiv7002388656 #yiv7002388656 -- #yiv7002388656ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7002388656 #yiv7002388656ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7002388656 #yiv7002388656ygrp-mkp #yiv7002388656hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv7002388656 #yiv7002388656ygrp-mkp #yiv7002388656ads {margin-bottom:10px;}#yiv7002388656 #yiv7002388656ygrp-mkp .yiv7002388656ad {padding:0 0;}#yiv7002388656 #yiv7002388656ygrp-mkp .yiv7002388656ad p {margin:0;}#yiv7002388656 #yiv7002388656ygrp-mkp .yiv7002388656ad a {color:#0000ff;text-decoration:none;}#yiv7002388656 #yiv7002388656ygrp-sponsor #yiv7002388656ygrp-lc {font-family:Arial;}#yiv7002388656 #yiv7002388656ygrp-sponsor #yiv7002388656ygrp-lc #yiv7002388656hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7002388656 #yiv7002388656ygrp-sponsor #yiv7002388656ygrp-lc .yiv7002388656ad {margin-bottom:10px;padding:0 0;}#yiv7002388656 #yiv7002388656actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7002388656 #yiv7002388656activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7002388656 #yiv7002388656activity span {font-weight:700;}#yiv7002388656 #yiv7002388656activity span:first-child {text-transform:uppercase;}#yiv7002388656 #yiv7002388656activity span a {color:#5085b6;text-decoration:none;}#yiv7002388656 #yiv7002388656activity span span {color:#ff7900;}#yiv7002388656 #yiv7002388656activity span .yiv7002388656underline {text-decoration:underline;}#yiv7002388656 .yiv7002388656attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv7002388656 .yiv7002388656attach div a {text-decoration:none;}#yiv7002388656 .yiv7002388656attach img {border:none;padding-right:5px;}#yiv7002388656 .yiv7002388656attach label {display:block;margin-bottom:5px;}#yiv7002388656 .yiv7002388656attach label a {text-decoration:none;}#yiv7002388656 blockquote {margin:0 0 0 4px;}#yiv7002388656 .yiv7002388656bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv7002388656 .yiv7002388656bold a {text-decoration:none;}#yiv7002388656 dd.yiv7002388656last p a {font-family:Verdana;font-weight:700;}#yiv7002388656 dd.yiv7002388656last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7002388656 dd.yiv7002388656last p span.yiv7002388656yshortcuts {margin-right:0;}#yiv7002388656 div.yiv7002388656attach-table div div a {text-decoration:none;}#yiv7002388656 div.yiv7002388656attach-table {width:400px;}#yiv7002388656 div.yiv7002388656file-title a, #yiv7002388656 div.yiv7002388656file-title a:active, #yiv7002388656 div.yiv7002388656file-title a:hover, #yiv7002388656 div.yiv7002388656file-title a:visited {text-decoration:none;}#yiv7002388656 div.yiv7002388656photo-title a, #yiv7002388656 div.yiv7002388656photo-title a:active, #yiv7002388656 div.yiv7002388656photo-title a:hover, #yiv7002388656 div.yiv7002388656photo-title a:visited {text-decoration:none;}#yiv7002388656 div#yiv7002388656ygrp-mlmsg #yiv7002388656ygrp-msg p a span.yiv7002388656yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7002388656 .yiv7002388656green {color:#628c2a;}#yiv7002388656 .yiv7002388656MsoNormal {margin:0 0 0 0;}#yiv7002388656 o {font-size:0;}#yiv7002388656 #yiv7002388656photos div {float:left;width:72px;}#yiv7002388656 #yiv7002388656photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv7002388656 #yiv7002388656photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7002388656 #yiv7002388656reco-category {font-size:77%;}#yiv7002388656 #yiv7002388656reco-desc {font-size:77%;}#yiv7002388656 .yiv7002388656replbq {margin:4px;}#yiv7002388656 #yiv7002388656ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv7002388656 #yiv7002388656ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7002388656 #yiv7002388656ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7002388656 #yiv7002388656ygrp-mlmsg select, #yiv7002388656 input, #yiv7002388656 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv7002388656 #yiv7002388656ygrp-mlmsg pre, #yiv7002388656 code {font:115% monospace;}#yiv7002388656 #yiv7002388656ygrp-mlmsg * {line-height:1.22em;}#yiv7002388656 #yiv7002388656ygrp-mlmsg #yiv7002388656logo {padding-bottom:10px;}#yiv7002388656 #yiv7002388656ygrp-msg p a {font-family:Verdana;}#yiv7002388656 #yiv7002388656ygrp-msg p#yiv7002388656attach-count span {color:#1E66AE;font-weight:700;}#yiv7002388656 #yiv7002388656ygrp-reco #yiv7002388656reco-head {color:#ff7900;font-weight:700;}#yiv7002388656 #yiv7002388656ygrp-reco {margin-bottom:20px;padding:0px;}#yiv7002388656 #yiv7002388656ygrp-sponsor #yiv7002388656ov li a {font-size:130%;text-decoration:none;}#yiv7002388656 #yiv7002388656ygrp-sponsor #yiv7002388656ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv7002388656 #yiv7002388656ygrp-sponsor #yiv7002388656ov ul {margin:0;padding:0 0 0 8px;}#yiv7002388656 #yiv7002388656ygrp-text {font-family:Georgia;}#yiv7002388656 #yiv7002388656ygrp-text p {margin:0 0 1em 0;}#yiv7002388656 #yiv7002388656ygrp-text tt {font-size:120%;}#yiv7002388656 #yiv7002388656ygrp-vital ul li:last-child {border-right:none !important;}#yiv7002388656

Mon Apr 6, 2015 1:33 am (PDT) . Posted by:

"Dede"

Dear Mr HK,

Ini pendapat bodoh saya, dari formula Mr HK dbi :

=Indirect("'[D:\Tes\Book1.xlsx]Sheet1'!$A$2")

Kayaknya masalahnya pada text yang saya beri warna hijau kayaknya.

Kalau saya punya formula seperti ini :

=INDIRECT("'D:\Tes\[Book1.xlsx]Sheet1'!$A$2")

Salam,

DD

From: hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 06 April 2015 14:37
To: belajar-excel@yahoogroups.com
Subject: Fw: [belajar-excel] vlookup dari workbook lain (satu folder)

Maaf, seharusnya:

=Indirect("'[Book1.xlsx]Sheet1'!$A$2")

Salam,

HK

----- Forwarded Message -----
From: "Hendrik Karnadi hendrikkarnadi@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
Sent: Monday, 6 April 2015, 14:27
Subject: Re: [belajar-excel] vlookup dari workbook lain (satu folder)

Mr Kid,

Tetap blm bisa walaupun file dibuka dua2nya dgn file open).

Kalau saya hilangkan dir nya menjadi =Indirect ("'[Book1.xlsx] Sheet1'! $A$2) baru bisa.

Salam,

HK

Sent from Samsung Mobile

-------- Original message --------

From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]"

Date:06/04/2015 03:24 (GMT+07:00)

To: BeExcel

Subject: Re: [belajar-excel] vlookup dari workbook lain (satu folder)

Pak HK,

Coba workbooknya dibuka. Jadi setelah membuka salah satu workbook, lakukan buka file yang lainnya melalui menu File (bukan dengan double click dari windows explorer)

2015-04-04 16:04 GMT+11:00 hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

Mr. Kid,

Mengambil contoh dari jawaban Mr. Kid mengenai vlookup dengan banyak sheet, yaitu mengenai penulisan fungsi Indirect berikut ini,

=Indirect( "'[workbook lainnya.xlsb]lain-lain&#39;!ops31" )

, saya mencoba membuat Fungsi Indirect untuk mengambil isi sel dari work book lain yang ada dalam satu folder (D:\Tes).

Folder D:\Tes terdiri dari 2 workbook, yaitu:

- Book1.xlsx

- Book 2.xlsx

Book1, Sheet1 yang saya rujuk mempunyai isi2 sel sbb:

sel A1 berisi kata Satu

sel A2 berisi kata Dua

sel A3 berisi kata Tiga

Saya buka Book2 (Book1 dalam keadaan tertutup) kmd saya tulis fungsi Indirect berikut ini,

=Indirect("'[D:&#92;Tes\Book1.xlsx]Sheet1'!$A$2")

pada sel A1...

seharusnya hasilnya "Dua" (tanpa apostrophi)

tetapi yang tampak adalah

#REF!

Pertanyaan saya :
Dimana salahnya ?

Terima kasih.

Salam,

HK

NB:

Compressed Folder "Tes" (zip file) terlampir, perlu diextract dulu ke folder D

_____

This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual to whom it is addressed. If you have received this email in error please send it back to the person that sent it to you. Any views or opinions presented are solely those of its author and do not necessarily represent those of NABATI or any of its subsidiary companies. Unauthorized publication, use, dissemination, forwarding, printing or copying of this email and its associated attachments is strictly prohibited.

Mon Apr 6, 2015 1:49 am (PDT) . Posted by:

"hendrik karnadi" hendrikkarnadi

Pak Dede,Saya coba =INDIRECT("'D:\Tes\[Book1.xlsx]Sheet1'!$A$2") blm jalan juga pak....Kalau ini =INDIRECT("'[Book1.xlsx]Sheet1'!$A$2") bisa jalan tapi kedua workbook harus dalam keadaan terbuka.
Salam,HK
From: "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
Sent: Monday, 6 April 2015, 15:32
Subject: RE: [belajar-excel] vlookup dari workbook lain (satu folder)

  DearMr HK,   Inipendapat bodoh saya, dari formula Mr HK dbi : =Indirect("'[D:\Tes\Book1.xlsx]Sheet1'!$A$2") Kayaknyamasalahnya pada text yang saya beri warna hijau kayaknya.   Kalausaya punya formula seperti ini : =INDIRECT("'D:\Tes\[Book1.xlsx]Sheet1'!$A$2")   Salam, DD  

From: hendrik karnadihendrikkarnadi@yahoo.com [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 06 April 2015 14:37
To: belajar-excel@yahoogroups.com
Subject: Fw: [belajar-excel] vlookup dari workbook lain (satu folder)     Maaf, seharusnya: =Indirect("'[Book1.xlsx]Sheet1'!$A$2")   Salam, HK   ----- Forwarded Message -----
From:"Hendrik Karnadi hendrikkarnadi@yahoo.com [belajar-excel]"<belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
Sent: Monday, 6 April 2015, 14:27
Subject: Re: [belajar-excel] vlookup dari workbook lain (satu folder)     Mr Kid, Tetap blm bisa walaupun file dibuka dua2nya dgn file open). Kalau saya hilangkan dir nya menjadi =Indirect("'[Book1.xlsx] Sheet1'! $A$2) baru bisa.   Salam, HK       Sent from Samsung Mobile   -------- Original message -------- From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" Date:06/04/2015 03:24 (GMT+07:00)   To: BeExcel Subject: Re: [belajar-excel] vlookup dari workbook lain (satu folder)     Pak HK, Coba workbooknya dibuka. Jadi setelah membuka salah satu workbook,lakukan buka file yang lainnya melalui menu File (bukan dengan double clickdari windows explorer)   2015-04-04 16:04 GMT+11:00 hendrik karnadi hendrikkarnadi@yahoo.com[belajar-excel] <belajar-excel@yahoogroups.com>:   Mr. Kid, Mengambil contoh dari jawaban Mr.Kid mengenai vlookup dengan banyak sheet, yaitu mengenai penulisan fungsiIndirect berikut ini, =Indirect( "'[workbooklainnya.xlsb]lain-lain&#39;!ops31" ) , saya mencoba membuat FungsiIndirect untuk mengambil isi sel dari work book lain yang ada dalam satu folder(D:\Tes).   Folder D:\Tes terdiri dari 2workbook, yaitu: - Book1.xlsx - Book 2.xlsx   Book1, Sheet1 yang saya rujukmempunyai isi2 sel sbb: sel A1 berisi kata Satu sel A2 berisikata Dua sel A3 berisi kata Tiga   Saya buka Book2 (Book1 dalam keadaantertutup) kmd saya tulis fungsi Indirect berikut ini, =Indirect("'[D:&#92;Tes\Book1.xlsx]Sheet1'!$A$2") pada sel A1... seharusnya hasilnya "Dua"(tanpa apostrophi) tetapi yang tampak adalah 
| #REF! |

  Pertanyaan saya :
Dimana salahnya ?   Terima kasih.   Salam, HK NB: Compressed Folder "Tes" (zip file) terlampir, perludiextract dulu ke folder D         This e-mail and any files transmitted with it are confidential andintended solely for the use of the individual to whom it is addressed. If youhave received this email in error please send it back to the person that sentit to you. Any views or opinions presented are solely those of its author anddo not necessarily represent those of NABATI or any of its subsidiarycompanies. Unauthorized publication, use, dissemination, forwarding, printingor copying of this email and its associated attachments is strictly prohibited. #yiv6062702915 #yiv6062702915 -- #yiv6062702915ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6062702915 #yiv6062702915ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6062702915 #yiv6062702915ygrp-mkp #yiv6062702915hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv6062702915 #yiv6062702915ygrp-mkp #yiv6062702915ads {margin-bottom:10px;}#yiv6062702915 #yiv6062702915ygrp-mkp .yiv6062702915ad {padding:0 0;}#yiv6062702915 #yiv6062702915ygrp-mkp .yiv6062702915ad p {margin:0;}#yiv6062702915 #yiv6062702915ygrp-mkp .yiv6062702915ad a {color:#0000ff;text-decoration:none;}#yiv6062702915 #yiv6062702915ygrp-sponsor #yiv6062702915ygrp-lc {font-family:Arial;}#yiv6062702915 #yiv6062702915ygrp-sponsor #yiv6062702915ygrp-lc #yiv6062702915hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6062702915 #yiv6062702915ygrp-sponsor #yiv6062702915ygrp-lc .yiv6062702915ad {margin-bottom:10px;padding:0 0;}#yiv6062702915 #yiv6062702915actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6062702915 #yiv6062702915activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6062702915 #yiv6062702915activity span {font-weight:700;}#yiv6062702915 #yiv6062702915activity span:first-child {text-transform:uppercase;}#yiv6062702915 #yiv6062702915activity span a {color:#5085b6;text-decoration:none;}#yiv6062702915 #yiv6062702915activity span span {color:#ff7900;}#yiv6062702915 #yiv6062702915activity span .yiv6062702915underline {text-decoration:underline;}#yiv6062702915 .yiv6062702915attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv6062702915 .yiv6062702915attach div a {text-decoration:none;}#yiv6062702915 .yiv6062702915attach img {border:none;padding-right:5px;}#yiv6062702915 .yiv6062702915attach label {display:block;margin-bottom:5px;}#yiv6062702915 .yiv6062702915attach label a {text-decoration:none;}#yiv6062702915 blockquote {margin:0 0 0 4px;}#yiv6062702915 .yiv6062702915bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv6062702915 .yiv6062702915bold a {text-decoration:none;}#yiv6062702915 dd.yiv6062702915last p a {font-family:Verdana;font-weight:700;}#yiv6062702915 dd.yiv6062702915last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6062702915 dd.yiv6062702915last p span.yiv6062702915yshortcuts {margin-right:0;}#yiv6062702915 div.yiv6062702915attach-table div div a {text-decoration:none;}#yiv6062702915 div.yiv6062702915attach-table {width:400px;}#yiv6062702915 div.yiv6062702915file-title a, #yiv6062702915 div.yiv6062702915file-title a:active, #yiv6062702915 div.yiv6062702915file-title a:hover, #yiv6062702915 div.yiv6062702915file-title a:visited {text-decoration:none;}#yiv6062702915 div.yiv6062702915photo-title a, #yiv6062702915 div.yiv6062702915photo-title a:active, #yiv6062702915 div.yiv6062702915photo-title a:hover, #yiv6062702915 div.yiv6062702915photo-title a:visited {text-decoration:none;}#yiv6062702915 div#yiv6062702915ygrp-mlmsg #yiv6062702915ygrp-msg p a span.yiv6062702915yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6062702915 .yiv6062702915green {color:#628c2a;}#yiv6062702915 .yiv6062702915MsoNormal {margin:0 0 0 0;}#yiv6062702915 o {font-size:0;}#yiv6062702915 #yiv6062702915photos div {float:left;width:72px;}#yiv6062702915 #yiv6062702915photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv6062702915 #yiv6062702915photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv6062702915 #yiv6062702915reco-category {font-size:77%;}#yiv6062702915 #yiv6062702915reco-desc {font-size:77%;}#yiv6062702915 .yiv6062702915replbq {margin:4px;}#yiv6062702915 #yiv6062702915ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv6062702915 #yiv6062702915ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv6062702915 #yiv6062702915ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv6062702915 #yiv6062702915ygrp-mlmsg select, #yiv6062702915 input, #yiv6062702915 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv6062702915 #yiv6062702915ygrp-mlmsg pre, #yiv6062702915 code {font:115% monospace;}#yiv6062702915 #yiv6062702915ygrp-mlmsg * {line-height:1.22em;}#yiv6062702915 #yiv6062702915ygrp-mlmsg #yiv6062702915logo {padding-bottom:10px;}#yiv6062702915 #yiv6062702915ygrp-msg p a {font-family:Verdana;}#yiv6062702915 #yiv6062702915ygrp-msg p#yiv6062702915attach-count span {color:#1E66AE;font-weight:700;}#yiv6062702915 #yiv6062702915ygrp-reco #yiv6062702915reco-head {color:#ff7900;font-weight:700;}#yiv6062702915 #yiv6062702915ygrp-reco {margin-bottom:20px;padding:0px;}#yiv6062702915 #yiv6062702915ygrp-sponsor #yiv6062702915ov li a {font-size:130%;text-decoration:none;}#yiv6062702915 #yiv6062702915ygrp-sponsor #yiv6062702915ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv6062702915 #yiv6062702915ygrp-sponsor #yiv6062702915ov ul {margin:0;padding:0 0 0 8px;}#yiv6062702915 #yiv6062702915ygrp-text {font-family:Georgia;}#yiv6062702915 #yiv6062702915ygrp-text p {margin:0 0 1em 0;}#yiv6062702915 #yiv6062702915ygrp-text tt {font-size:120%;}#yiv6062702915 #yiv6062702915ygrp-vital ul li:last-child {border-right:none !important;}#yiv6062702915

Mon Apr 6, 2015 1:03 am (PDT) . Posted by:

adji.tricom

Sy dulu aktiv di grup ini masih jamannya mbak siti V. Beliau sudah tiada ya? Kapan meninggalnya? Sakit ya?

Mon Apr 6, 2015 1:07 am (PDT) . Posted by:

adji.tricom

Saya kerja di sebuah perguruan tinggi baru, rencana mau bikin database mahasiswa. Tapi belum biasa pake Ms. Access namun lumayan bisa excel. Apa bisa Excel dipake buat database mahasiswa yang kira2 bisa memenuhi semua kebutuhan database tanpa harus menggunakan Ms. Access? Jika bisa, apa ada yang bisa membantu?


Mon Apr 6, 2015 1:42 am (PDT) . Posted by:

"hendrik karnadi" hendrikkarnadi

Pak Adji,Database yang "bisa memenuhi semua kebutuhan database" mengandung pengertian yang sangat luas, sementara Excel walaupun baris dan kolomnya banyak akan bekerja kurang maksimal apabila datanya banyak mengandung formula atau link.Datanyapun tidak terstruktur seperti Access yang terdiri dari kumpulan table.
Jadi, menurut pendapat saya, kayaknya kurang pas kalau pakai Excel.
Salam,HK 
From: "adji.tricom@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
Sent: Monday, 6 April 2015, 15:07
Subject: [belajar-excel] Database Mahasiswa Di Excel

  Saya kerja di sebuah perguruan tinggi baru, rencana mau bikin database mahasiswa. Tapi belum biasa pake Ms. Access namun lumayan bisa excel. Apa bisa Excel dipake buat database mahasiswa yang kira2 bisa memenuhi semua kebutuhan database tanpa harus menggunakan Ms. Access? Jika bisa, apa ada yang bisa membantu?

#yiv9142560030 #yiv9142560030 -- #yiv9142560030ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9142560030 #yiv9142560030ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9142560030 #yiv9142560030ygrp-mkp #yiv9142560030hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9142560030 #yiv9142560030ygrp-mkp #yiv9142560030ads {margin-bottom:10px;}#yiv9142560030 #yiv9142560030ygrp-mkp .yiv9142560030ad {padding:0 0;}#yiv9142560030 #yiv9142560030ygrp-mkp .yiv9142560030ad p {margin:0;}#yiv9142560030 #yiv9142560030ygrp-mkp .yiv9142560030ad a {color:#0000ff;text-decoration:none;}#yiv9142560030 #yiv9142560030ygrp-sponsor #yiv9142560030ygrp-lc {font-family:Arial;}#yiv9142560030 #yiv9142560030ygrp-sponsor #yiv9142560030ygrp-lc #yiv9142560030hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9142560030 #yiv9142560030ygrp-sponsor #yiv9142560030ygrp-lc .yiv9142560030ad {margin-bottom:10px;padding:0 0;}#yiv9142560030 #yiv9142560030actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9142560030 #yiv9142560030activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9142560030 #yiv9142560030activity span {font-weight:700;}#yiv9142560030 #yiv9142560030activity span:first-child {text-transform:uppercase;}#yiv9142560030 #yiv9142560030activity span a {color:#5085b6;text-decoration:none;}#yiv9142560030 #yiv9142560030activity span span {color:#ff7900;}#yiv9142560030 #yiv9142560030activity span .yiv9142560030underline {text-decoration:underline;}#yiv9142560030 .yiv9142560030attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9142560030 .yiv9142560030attach div a {text-decoration:none;}#yiv9142560030 .yiv9142560030attach img {border:none;padding-right:5px;}#yiv9142560030 .yiv9142560030attach label {display:block;margin-bottom:5px;}#yiv9142560030 .yiv9142560030attach label a {text-decoration:none;}#yiv9142560030 blockquote {margin:0 0 0 4px;}#yiv9142560030 .yiv9142560030bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9142560030 .yiv9142560030bold a {text-decoration:none;}#yiv9142560030 dd.yiv9142560030last p a {font-family:Verdana;font-weight:700;}#yiv9142560030 dd.yiv9142560030last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9142560030 dd.yiv9142560030last p span.yiv9142560030yshortcuts {margin-right:0;}#yiv9142560030 div.yiv9142560030attach-table div div a {text-decoration:none;}#yiv9142560030 div.yiv9142560030attach-table {width:400px;}#yiv9142560030 div.yiv9142560030file-title a, #yiv9142560030 div.yiv9142560030file-title a:active, #yiv9142560030 div.yiv9142560030file-title a:hover, #yiv9142560030 div.yiv9142560030file-title a:visited {text-decoration:none;}#yiv9142560030 div.yiv9142560030photo-title a, #yiv9142560030 div.yiv9142560030photo-title a:active, #yiv9142560030 div.yiv9142560030photo-title a:hover, #yiv9142560030 div.yiv9142560030photo-title a:visited {text-decoration:none;}#yiv9142560030 div#yiv9142560030ygrp-mlmsg #yiv9142560030ygrp-msg p a span.yiv9142560030yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9142560030 .yiv9142560030green {color:#628c2a;}#yiv9142560030 .yiv9142560030MsoNormal {margin:0 0 0 0;}#yiv9142560030 o {font-size:0;}#yiv9142560030 #yiv9142560030photos div {float:left;width:72px;}#yiv9142560030 #yiv9142560030photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv9142560030 #yiv9142560030photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9142560030 #yiv9142560030reco-category {font-size:77%;}#yiv9142560030 #yiv9142560030reco-desc {font-size:77%;}#yiv9142560030 .yiv9142560030replbq {margin:4px;}#yiv9142560030 #yiv9142560030ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9142560030 #yiv9142560030ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9142560030 #yiv9142560030ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9142560030 #yiv9142560030ygrp-mlmsg select, #yiv9142560030 input, #yiv9142560030 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9142560030 #yiv9142560030ygrp-mlmsg pre, #yiv9142560030 code {font:115% monospace;}#yiv9142560030 #yiv9142560030ygrp-mlmsg * {line-height:1.22em;}#yiv9142560030 #yiv9142560030ygrp-mlmsg #yiv9142560030logo {padding-bottom:10px;}#yiv9142560030 #yiv9142560030ygrp-msg p a {font-family:Verdana;}#yiv9142560030 #yiv9142560030ygrp-msg p#yiv9142560030attach-count span {color:#1E66AE;font-weight:700;}#yiv9142560030 #yiv9142560030ygrp-reco #yiv9142560030reco-head {color:#ff7900;font-weight:700;}#yiv9142560030 #yiv9142560030ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9142560030 #yiv9142560030ygrp-sponsor #yiv9142560030ov li a {font-size:130%;text-decoration:none;}#yiv9142560030 #yiv9142560030ygrp-sponsor #yiv9142560030ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9142560030 #yiv9142560030ygrp-sponsor #yiv9142560030ov ul {margin:0;padding:0 0 0 8px;}#yiv9142560030 #yiv9142560030ygrp-text {font-family:Georgia;}#yiv9142560030 #yiv9142560030ygrp-text p {margin:0 0 1em 0;}#yiv9142560030 #yiv9142560030ygrp-text tt {font-size:120%;}#yiv9142560030 #yiv9142560030ygrp-vital ul li:last-child {border-right:none !important;}#yiv9142560030

============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com

posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com

berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com

keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Tidak ada komentar:

Posting Komentar