[milis-belajar-excel] formula mengganti secara otomatis rujukan sheet berubah sesuai referensi sel

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[milis-belajar-excel] formula mengganti secara otomatis rujukan sheet berubah sesuai referensi sel

adrian piter
Selamat siang para suhu dan member belajar excel
mohon solusi dan pencerahannya untuk pertanyaan berikut.
terima kasih.
">

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/904391991.1314676.1615960336401%40mail.yahoo.com.

1615960239390blob.jpg (80K) Download Attachment
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] formula mengganti secara otomatis rujukan sheet berubah sesuai referensi sel

Kid
Administrator
Ini maksudnya pakai fungsi Indirect ?
=Indirect( "'nama sheetnya'!alamatCells" )      -> perhatikan cara penulisan nama sheet yang diapit '
=Indirect( "'data lama'!z3)

Jika ada banyak cells yang berisi fungsi yang bersifat volatile seperti Indirect, Rand, RandBetween, Offset, Now, maka Excel akan lebih cepat terasa lemot 
karena cells yang berisi fungsi ini pasti akan dikalkulasi ulang walaupun yang diubah adalah cells baru di worksheet yang baru sekalipun.






On Wed, Mar 17, 2021 at 1:14 PM 'adrian piter' via milis Belajar-Excel <[hidden email]> wrote:
Selamat siang para suhu dan member belajar excel
mohon solusi dan pencerahannya untuk pertanyaan berikut.
terima kasih.
Inline image

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/904391991.1314676.1615960336401%40mail.yahoo.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPtOwY4-vgxp-_NdqnB38UwBuNzaLG4PoCGyUt0no7Z%3DWg%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] formula mengganti secara otomatis rujukan sheet berubah sesuai referensi sel

adrian piter
Terima kasih atas solusinya Mr Kid...maaf baru respon.
formula sudah terpecahkan (dengan sedikit utak atik dari formula yang Mr Kid berikan)
tapi ada satu masalah lagi yaitu :
      bagaimana formula yang tepat bila formula tersebut mau saya copas ke bawahnya dan cel rujukan juga berubah sesuai posisinya.
 
berikut saya sertakan file nya.

 Terima kasih atas pencerahannya.
On Friday, March 19, 2021, 01:30:25 AM GMT+7, Mr. Kid <[hidden email]> wrote:


Ini maksudnya pakai fungsi Indirect ?
=Indirect( "'nama sheetnya'!alamatCells" )      -> perhatikan cara penulisan nama sheet yang diapit '
=Indirect( "'data lama'!z3)

Jika ada banyak cells yang berisi fungsi yang bersifat volatile seperti Indirect, Rand, RandBetween, Offset, Now, maka Excel akan lebih cepat terasa lemot 
karena cells yang berisi fungsi ini pasti akan dikalkulasi ulang walaupun yang diubah adalah cells baru di worksheet yang baru sekalipun.






On Wed, Mar 17, 2021 at 1:14 PM 'adrian piter' via milis Belajar-Excel <[hidden email]> wrote:
Selamat siang para suhu dan member belajar excel
mohon solusi dan pencerahannya untuk pertanyaan berikut.
terima kasih.
Inline image

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/904391991.1314676.1615960336401%40mail.yahoo.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPtOwY4-vgxp-_NdqnB38UwBuNzaLG4PoCGyUt0no7Z%3DWg%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/464563036.66511.1616634934648%40mail.yahoo.com.

=?UTF-8?b?VGFueWEgRXhjZWxfSW5kaXJlY3QueGxzeA==?= (17K) Download Attachment
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] formula mengganti secara otomatis rujukan sheet berubah sesuai referensi sel

Kid
Administrator
Bagian alamat rujukan disusun dengan penggabungan teks.

=Indirect( "'" & cells_berisiNamasheetTujuan & "'!" & cellsBerisiNamaKolomExcelyangakandiambilABCDEFdst & cellsBerisiNomorBarisYangAkanDiambil123456dst )







On Thu, Mar 25, 2021 at 8:15 AM adrian piter <[hidden email]> wrote:
Terima kasih atas solusinya Mr Kid...maaf baru respon.
formula sudah terpecahkan (dengan sedikit utak atik dari formula yang Mr Kid berikan)
tapi ada satu masalah lagi yaitu :
      bagaimana formula yang tepat bila formula tersebut mau saya copas ke bawahnya dan cel rujukan juga berubah sesuai posisinya.
 
berikut saya sertakan file nya.

 Terima kasih atas pencerahannya.
On Friday, March 19, 2021, 01:30:25 AM GMT+7, Mr. Kid <[hidden email]> wrote:


Ini maksudnya pakai fungsi Indirect ?
=Indirect( "'nama sheetnya'!alamatCells" )      -> perhatikan cara penulisan nama sheet yang diapit '
=Indirect( "'data lama'!z3)

Jika ada banyak cells yang berisi fungsi yang bersifat volatile seperti Indirect, Rand, RandBetween, Offset, Now, maka Excel akan lebih cepat terasa lemot 
karena cells yang berisi fungsi ini pasti akan dikalkulasi ulang walaupun yang diubah adalah cells baru di worksheet yang baru sekalipun.






On Wed, Mar 17, 2021 at 1:14 PM 'adrian piter' via milis Belajar-Excel <[hidden email]> wrote:
Selamat siang para suhu dan member belajar excel
mohon solusi dan pencerahannya untuk pertanyaan berikut.
terima kasih.
Inline image

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/904391991.1314676.1615960336401%40mail.yahoo.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPtOwY4-vgxp-_NdqnB38UwBuNzaLG4PoCGyUt0no7Z%3DWg%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPv2acQ_Uk7OpaNYGo%3Dri1oPU4Abv9ztrvF3yzNYhCXAXw%40mail.gmail.com.