[smf_addin] Digest Number 3394

EXCEL Stock Market Functions Add-in Group

6 Messages

Digest #3394

1a

Re: Latest version of smf is 2014? by jimmymc00

1b

Re: Latest version of smf is 2014? by "Randy Harmelink" rharmelink

1c

Re: Latest version of smf is 2014? by jimmymc00

1d

Re: Latest version of smf is 2014? by "Randy Harmelink" rharmelink

2a

Yahoo intra-day quotes not workings by mgbuck55

2b

Re: Yahoo intra-day quotes not workings by "Randy Harmelink" rharmelink

Messages

1a

Re: Latest version of smf is 2014?

Wed Apr 29, 2015 5:38 pm (PDT) . Posted by:

jimmymc00

I was admiring the RCHGetYahooQuotes-Template at http://ogres-crypt.com/SMF
and was able to create a similar template within my Excel Stock Portfolio file. It works great, and cut the processing time by 80%. Fantastic!

Can the RCHGetElementNumber be set up this way and reduce the time it takes for processing? By the way, where is this formula drawing its data from? Thanks. Jim

-----Original Message-----
From: Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com>
To: smf_addin <smf_addin@yahoogroups.com>
Sent: Tue, Apr 28, 2015 8:07 pm
Subject: Re: [smf_addin] Latest version of smf is 2014?

The 2014 version was the last official release. There's a beta release on the "Works in Progress" web page, from March of this year.


However, the most likely cause of #NAME? errors is either you don't have the add-in installed, or the worksheet you're opening had the add-in located in a different location than you do. Try running the smfFixLinks macro to fix any unresolved links due to location errors.



On Tue, Apr 28, 2015 at 4:29 PM, freefaller6@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:



Anyone know why on , http://ogres-crypt.com/SMF/, is says the latest version of the addin is for 2014? My spreadsheet isn't working - used it last month with no problem. Now ever cells says #NAME? Confused and in for a long night it seems.

Reply to sender . Reply to group . Reply via Web Post . All Messages (8) . Top ^

1b

Re: Latest version of smf is 2014?

Wed Apr 29, 2015 6:13 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

RCHGetYahooQuotes() grabs a CSV file from Yahoo based on all the parameters
sent. That's what makes it so fast -- all of the data can be retrieved with
one Internet access. The number of Internet accesses is the data retrieval
bottleneck.

However, since RCHGetElementNumber(), as well as most other add-in
functions, get their data by pulling it out of the source code of
individual web pages, the bottleneck can't be reduced. Five different
ticker symbols typically means at least five different web page retrievals.
More, depending on how the various data items are arranged on web pages.

So there would be no advantage even if you could array-enter the
RCHGetElementNumber() function.

The source of data for RCHGetElementNumber() data varies. That function is
basically just pulling up saved definitions that use other add-in
functions. The definitions of the elements can be retrieved easily. For
example:

=smfGetAParms(942)
=RCHGetElementNumber("Source", 942)
=RCHGetElementNumber("P-URL", 942)

The "sources" are also documented in the XLS document distributed in the
ZIP archive. I try to give each web page its own "Source" value. All of the
data items from a web page can be retrieved by a single Internet access,
because the add-in saves a copy of the web page before actually extracting
data from it. So if another data item comes from that web page, the add-in
goes to the saved copy of the web page instead of going back to the
Internet.

On Wed, Apr 29, 2015 at 5:38 PM, jmcatee133@aol.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I was admiring the RCHGetYahooQuotes-Template at
> http://ogres-crypt.com/SMF
> and was able to create a similar template within my Excel Stock Portfolio
> file. It works great, and cut the processing time by 80%. Fantastic!
>
> Can the RCHGetElementNumber be set up this way and reduce the time it
> takes for processing? By the way, where is this formula drawing its data
> from? Thanks. Jim
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (8) . Top ^

1c

Re: Latest version of smf is 2014?

Wed Apr 29, 2015 7:38 pm (PDT) . Posted by:

jimmymc00

Thanks Randy. Your detailed explanation accelerated my learning curve quite a bit. I found the XLS document with the sources and codes a bit earlier. What are the "Usage" codes in column C?

I also started playing with the Morning Star Stock - Template. Pretty neat! I'm still a little weak on evaluating stocks and this looks like it should help me. The values in the Current Evaluation section at the bottom of the template are coming back as Undefined (element nos. 14472 - 14492). I see that they are coming from MorningStarCV2 source. Does that suggest a problem with the source? More likely, I'm not set up right.

Thanks again. I hope you don't mind these newbie questions. Jim

-----Original Message-----
From: Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com>
To: smf_addin <smf_addin@yahoogroups.com>
Sent: Wed, Apr 29, 2015 9:13 pm
Subject: Re: [smf_addin] Latest version of smf is 2014?

RCHGetYahooQuotes() grabs a CSV file from Yahoo based on all the parameters sent. That's what makes it so fast -- all of the data can be retrieved with one Internet access. The number of Internet accesses is the data retrieval bottleneck.


However, since RCHGetElementNumber(), as well as most other add-in functions, get their data by pulling it out of the source code of individual web pages, the bottleneck can't be reduced. Five different ticker symbols typically means at least five different web page retrievals. More, depending on how the various data items are arranged on web pages.


So there would be no advantage even if you could array-enter the RCHGetElementNumber() function.


The source of data for RCHGetElementNumber() data varies. That function is basically just pulling up saved definitions that use other add-in functions. The definitions of the elements can be retrieved easily. For example:


=smfGetAParms(942)

=RCHGetElementNumber("Source", 942)
=RCHGetElementNumber("P-URL", 942)


The "sources" are also documented in the XLS document distributed in the ZIP archive. I try to give each web page its own "Source" value. All of the data items from a web page can be retrieved by a single Internet access, because the add-in saves a copy of the web page before actually extracting data from it. So if another data item comes from that web page, the add-in goes to the saved copy of the web page instead of going back to the Internet.











On Wed, Apr 29, 2015 at 5:38 PM, jmcatee133@aol.com [smf_addin] <smf_addin@yahoogroups.com> wrote:



I was admiring the RCHGetYahooQuotes-Template at http://ogres-crypt.com/SMF
and was able to create a similar template within my Excel Stock Portfolio file. It works great, and cut the processing time by 80%. Fantastic!

Can the RCHGetElementNumber be set up this way and reduce the time it takes for processing? By the way, where is this formula drawing its data from? Thanks. Jim

Reply to sender . Reply to group . Reply via Web Post . All Messages (8) . Top ^

1d

Re: Latest version of smf is 2014?

Wed Apr 29, 2015 8:39 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Usage codes -- S=Stock, E=ETF, F=Mutual Fund. Elements don't apply to all
types of equities.

The MorningStar Current Valuation elements were some of the last ones I
added, so you would need the latest version of the smf-elements-4.txt
<http://ogres-crypt.com/SMF/Works-In-Progress/smf-elements-4.txt> file from
the "Works in Progress" web page on the web site. After replacing the file
in the add-in's folder on your machine, you'll either need to restart EXCEL
or run the smfForceRecalculation macro for the add-in to reload the element
definitions from those external files.

On Wed, Apr 29, 2015 at 7:38 PM, jmcatee133@aol.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Thanks Randy. Your detailed explanation accelerated my learning curve
> quite a bit. I found the XLS document with the sources and codes a bit
> earlier. What are the "Usage" codes in column C?
>
> I also started playing with the Morning Star Stock - Template. Pretty
> neat! I'm still a little weak on evaluating stocks and this looks like it
> should help me. The values in the Current Evaluation section at the bottom
> of the template are coming back as Undefined (element nos. 14472 - 14492).
> I see that they are coming from MorningStarCV2 source. Does that suggest a
> problem with the source? More likely, I'm not set up right.
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (8) . Top ^

2a

Yahoo intra-day quotes not workings

Wed Apr 29, 2015 8:21 pm (PDT) . Posted by:

mgbuck55

Hi Randy,
Today, out of the blue, I was unable to gather intra-day prices from Yahoo Finance. No problem downloading end of day prices. Wondering if the problem is at my end, or if Yahoo is having trouble. When I enter the base URL for intra-day price downloads - "download.finance.yahoo.com" in a browser I get a page not found error from Yahoo. My fear is Yahoo has changed something at their end that has broken the download code. I would certainly appreciate any thoughts or feedback you might offer.


Thanks,
Mike

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

2b

Re: Yahoo intra-day quotes not workings

Wed Apr 29, 2015 8:48 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The "Download Data (Delayed)" from stock web pages still use that domain,
and still work. For example:

http://download.finance.yahoo.com/d/quotes.csv?s=MMM&f=sl1d1t1c1ohgv&e=.csv

I just tried a quick RCHGetYahooQuotes() function and was able to get the
latest closing price of a stock.

But, yes, the web address you cite below will give a "Page not Found"
error, because it doesn't exist. It's just the start of a downloading web
address.

Make sure your Internet Options are set to always get a fresh copy of a web
page. You may have something cached within the "Temporary Internet Files"
that was causing problems during the day?

On Wed, Apr 29, 2015 at 8:21 PM, mgbuck@comcast.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:

> Today, out of the blue, I was unable to gather intra-day prices from
> Yahoo Finance. No problem downloading end of day prices. Wondering if the
> problem is at my end, or if Yahoo is having trouble. When I enter the base
> URL for intra-day price downloads - "download.finance.yahoo.com" in a
> browser I get a page not found error from Yahoo. My fear is Yahoo has
> changed something at their end that has broken the download code. I would
> certainly appreciate any thoughts or feedback you might offer.
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Visit Your Group

• New Members 32

• Privacy • Unsubscribe • Terms of Use

[belajar-excel] Digest Number 3407

Milis Belajar Microsoft Excel Group

15 Messages

Digest #3407

1a

Re: Hiden Otomatis [1 Attachment] by "Dede"

1b

Re: Hiden Otomatis by "Dede"

1c

Re: Hiden Otomatis [1 Attachment] by "Dede"

1d

Re: Hiden Otomatis by "Toby kenzo" tobykenzo

1e

Re: Hiden Otomatis by "Toby kenzo" tobykenzo

1f

Re: Hiden Otomatis by "Dede"

1g

Re: Hiden Otomatis by "Toby kenzo" tobykenzo

1h

Re: Hiden Otomatis by "Dede"

1i

Re: Hiden Otomatis [1 Attachment] by "Mr. Kid" nmkid.family@ymail.com

2a

Re: Absensi Extra [1 Attachment] by "Mr. Kid" nmkid.family@ymail.com

3a

Rekap jumlah dan Nama Barang by "Muzakki Harun" muzakkiharun@ymail.com

3b

Re: Rekap jumlah dan Nama Barang [1 Attachment] by "Fatimah Nazmi"

3c

Re: Rekap jumlah dan Nama Barang [1 Attachment] by "Fatimah Nazmi"

3d

Bls: [belajar-excel] Rekap jumlah dan Nama Barang [1 Attachment] by "Muzakki Harun" muzakkiharun@ymail.com

4a

Re: Mengelompokkan nilai sesuai tema [1 Attachment] by "Fatimah Nazmi"

Messages

1a

Re: Hiden Otomatis [1 Attachment]

Wed Apr 29, 2015 2:27 am (PDT) . Posted by:

"Dede"

Dear Mr Toby,

Terima kasih atas feedbacknya, akan saya tanbahkan untuk kode yang lain.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 15:45
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Sebenarnya tinggal modif formula di sheet"View" aja dengan menambahkan "AND" di formula IF.

Berikut filenya, saya coba hanya untuk No.1000596 untuk no selanjutnya kamu bisa teruskan sama seperti No.10000596.

Selamat mencoba...

It's work in Office 2013 SP1.

TerimaKasih

Pada Rabu, 29 April 2015 14:48, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Daear Mr Toby,

Setelah saya pakai dan sedikit di refresh data yang ada di sheet "Rekap", ternyata masih belum sesuai harapan. Mungkin sayanya kurang detail penjelasannya.

Begini rulenya Mr...,

Kita bicara kode 1000596 yah.

Syarat Kolom : 1. Jika X58>0 dan AK58>0 dan AX58>0, maka jangan ada kolom yang di hide

2. Jika X58>0 dan AK58>0 dan AX58=0, maka kolom AL s/d AX di hide

3. Jika X58>0 dan AK58=0 dan AX58=0, maka kolom Y s/d AX di hide

Syarat Baris : 1. Sembunyikan mulai di baris berapa saja tergantung dari akhir kode di kolom C sheet "View".

Dan begitu juga untuk kode 1000597 dan 1000616.

Demikian penjelasannya semoga tidak bingung dan dapat membantu kembali.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:27
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis

Dear DD,

Sama-sama...asal ada kemauan dan kerja keras, ngga ada yang ga mungkin untuk di pecahkan..

TerimaKasih.

Pada Selasa, 28 April 2015 17:23, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear Mr Toby,

Luarrr Biasa....,

Saya mah gak pernah kepikiran klo masalah saya ini ternyata ada solusinya. Ckckckckck....

Saya haturkan banyak terima kasih Mr Toby, semoga ilmu Anda bermanfaaat dan membawa kebaikan.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:08
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Berikut Filenya,semoga bisa membantu.

TerimaKasih

Pada Selasa, 28 April 2015 15:53, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear BeExceller,

Bisakan sebuah ROW dan COLUMN ter"Hide" otomatis ?

Ini pengembangan kasus hyperlink tadi, semoga teman-teman tidak bosan yah...

Tinggal satu langkah lagi untuk menyempurnakan report terlampir, yaitu dengan meng"Hide" otomatis baris kosong di sheet "View" dan kolom yang tidak ada datanya pada saat saya klik kode subdist di sheet "List Kirim GT" apakah hal itu memungkinkan ?

Mohon pencerahannya dari para master sekalian.

Salam,

DD

_____

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.

Reply to sender . Reply to group . Reply via Web Post . All Messages (26) . Top ^

1b

Re: Hiden Otomatis

Wed Apr 29, 2015 2:52 am (PDT) . Posted by:

"Dede"

Dear Mr Toby,

Saya tambahkan untuk kode 1000597 dan 1000616 seperti dbi :

Private Sub Worksheet_Activate()

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Else

End If

End Sub

Tapi belum bisa, salah dimana yah? Maklum kurang ngudeng VB.., semoga tidak bosan ya Mr....

Salam,

DD

From: Dede pmaho_log4@nabatisnack.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 16:27
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Hiden Otomatis

Dear Mr Toby,

Terima kasih atas feedbacknya, akan saya tanbahkan untuk kode yang lain.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 15:45
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Sebenarnya tinggal modif formula di sheet"View" aja dengan menambahkan "AND" di formula IF.

Berikut filenya, saya coba hanya untuk No.1000596 untuk no selanjutnya kamu bisa teruskan sama seperti No.10000596.

Selamat mencoba...

It's work in Office 2013 SP1.

TerimaKasih

Pada Rabu, 29 April 2015 14:48, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Daear Mr Toby,

Setelah saya pakai dan sedikit di refresh data yang ada di sheet "Rekap", ternyata masih belum sesuai harapan. Mungkin sayanya kurang detail penjelasannya.

Begini rulenya Mr...,

Kita bicara kode 1000596 yah.

Syarat Kolom : 1. Jika X58>0 dan AK58>0 dan AX58>0, maka jangan ada kolom yang di hide

2. Jika X58>0 dan AK58>0 dan AX58=0, maka kolom AL s/d AX di hide

3. Jika X58>0 dan AK58=0 dan AX58=0, maka kolom Y s/d AX di hide

Syarat Baris : 1. Sembunyikan mulai di baris berapa saja tergantung dari akhir kode di kolom C sheet "View".

Dan begitu juga untuk kode 1000597 dan 1000616.

Demikian penjelasannya semoga tidak bingung dan dapat membantu kembali.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:27
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis

Dear DD,

Sama-sama...asal ada kemauan dan kerja keras, ngga ada yang ga mungkin untuk di pecahkan..

TerimaKasih.

Pada Selasa, 28 April 2015 17:23, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear Mr Toby,

Luarrr Biasa....,

Saya mah gak pernah kepikiran klo masalah saya ini ternyata ada solusinya. Ckckckckck....

Saya haturkan banyak terima kasih Mr Toby, semoga ilmu Anda bermanfaaat dan membawa kebaikan.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:08
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Berikut Filenya,semoga bisa membantu.

TerimaKasih

Pada Selasa, 28 April 2015 15:53, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear BeExceller,

Bisakan sebuah ROW dan COLUMN ter"Hide" otomatis ?

Ini pengembangan kasus hyperlink tadi, semoga teman-teman tidak bosan yah...

Tinggal satu langkah lagi untuk menyempurnakan report terlampir, yaitu dengan meng"Hide" otomatis baris kosong di sheet "View" dan kolom yang tidak ada datanya pada saat saya klik kode subdist di sheet "List Kirim GT" apakah hal itu memungkinkan ?

Mohon pencerahannya dari para master sekalian.

Salam,

DD

_____

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.

Reply to sender . Reply to group . Reply via Web Post . All Messages (26) . Top ^

1c

Re: Hiden Otomatis [1 Attachment]

Wed Apr 29, 2015 3:07 am (PDT) . Posted by:

"Dede"

Eh sori seperti ini :

Private Sub Worksheet_Activate()

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Else

End If

End Sub

Tapi kode 1000616 untuk hide barisnya belum sesuai.

Salam,

DD

From: Dede pmaho_log4@nabatisnack.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 16:52
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear Mr Toby,

Saya tambahkan untuk kode 1000597 dan 1000616 seperti dbi :

Private Sub Worksheet_Activate()

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Else

End If

End Sub

Tapi belum bisa, salah dimana yah? Maklum kurang ngudeng VB.., semoga tidak bosan ya Mr....

Salam,

DD

From: Dede pmaho_log4@nabatisnack.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 16:27
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Hiden Otomatis

Dear Mr Toby,

Terima kasih atas feedbacknya, akan saya tanbahkan untuk kode yang lain.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 15:45
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Sebenarnya tinggal modif formula di sheet"View" aja dengan menambahkan "AND" di formula IF.

Berikut filenya, saya coba hanya untuk No.1000596 untuk no selanjutnya kamu bisa teruskan sama seperti No.10000596.

Selamat mencoba...

It's work in Office 2013 SP1.

TerimaKasih

Pada Rabu, 29 April 2015 14:48, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Daear Mr Toby,

Setelah saya pakai dan sedikit di refresh data yang ada di sheet "Rekap", ternyata masih belum sesuai harapan. Mungkin sayanya kurang detail penjelasannya.

Begini rulenya Mr...,

Kita bicara kode 1000596 yah.

Syarat Kolom : 1. Jika X58>0 dan AK58>0 dan AX58>0, maka jangan ada kolom yang di hide

2. Jika X58>0 dan AK58>0 dan AX58=0, maka kolom AL s/d AX di hide

3. Jika X58>0 dan AK58=0 dan AX58=0, maka kolom Y s/d AX di hide

Syarat Baris : 1. Sembunyikan mulai di baris berapa saja tergantung dari akhir kode di kolom C sheet "View".

Dan begitu juga untuk kode 1000597 dan 1000616.

Demikian penjelasannya semoga tidak bingung dan dapat membantu kembali.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:27
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis

Dear DD,

Sama-sama...asal ada kemauan dan kerja keras, ngga ada yang ga mungkin untuk di pecahkan..

TerimaKasih.

Pada Selasa, 28 April 2015 17:23, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear Mr Toby,

Luarrr Biasa....,

Saya mah gak pernah kepikiran klo masalah saya ini ternyata ada solusinya. Ckckckckck....

Saya haturkan banyak terima kasih Mr Toby, semoga ilmu Anda bermanfaaat dan membawa kebaikan.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:08
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Berikut Filenya,semoga bisa membantu.

TerimaKasih

Pada Selasa, 28 April 2015 15:53, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear BeExceller,

Bisakan sebuah ROW dan COLUMN ter"Hide" otomatis ?

Ini pengembangan kasus hyperlink tadi, semoga teman-teman tidak bosan yah...

Tinggal satu langkah lagi untuk menyempurnakan report terlampir, yaitu dengan meng"Hide" otomatis baris kosong di sheet "View" dan kolom yang tidak ada datanya pada saat saya klik kode subdist di sheet "List Kirim GT" apakah hal itu memungkinkan ?

Mohon pencerahannya dari para master sekalian.

Salam,

DD

_____

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.

Reply to sender . Reply to group . Reply via Web Post . All Messages (26) . Top ^

1d

Re: Hiden Otomatis

Wed Apr 29, 2015 3:20 am (PDT) . Posted by:

"Toby kenzo" tobykenzo

Dear DD,

untuk yg 1000596 apa sudah sesuai..?

Sent from Yahoo Mail on Android

Reply to sender . Reply to group . Reply via Web Post . All Messages (26) . Top ^

1e

Re: Hiden Otomatis

Wed Apr 29, 2015 3:27 am (PDT) . Posted by:

"Toby kenzo" tobykenzo

Dear DD,

untuk yg 1000596 apa sudah sesuai..?

Sent from Yahoo Mail on Android

Reply to sender . Reply to group . Reply via Web Post . All Messages (26) . Top ^

1f

Re: Hiden Otomatis

Wed Apr 29, 2015 3:37 am (PDT) . Posted by:

"Dede"

Dear Mr Toby,

Blm bisa dikatakan sesuai...

Setelah saya analisa, View yang saya lihat tidak mengambalikan data sesungguhnya setelahnya. Maksudnya begini...

Misalkan sayalihat stok 1000596, secara View sudah sesuai harapan (Baris yang terhiden 24 s/d 57), namun ketika saya klik 1000616 View tetap menyembunyikan baris 24 s/d 57, seharusnya yang terhiden mulai di baris 27 s/d 57.

Masalahnya adalah stock yang mempunyai baris sedikit itu tidak mau mengembalikan stock yang jumlah barisnya lebih banyak, karena kalau saya klik 1000616 (awal buka file), stock ini kan mempunyai 15 baris, View akan sesuai harapan kalau kode berikutnya mempunyai jumlah baris yang sama atau lebih kecil. Tapi kalau kode berikutnya mempunyai jumlah stock yang lebih banyak View tetap dengan baris 15.

Semoga tidak bingung Mr...

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 17:20
To: belajar-excel@yahoogroups.com
Subject: Re: RE: [belajar-excel] Hiden Otomatis

Dear DD,

untuk yg 1000596 apa sudah sesuai..?

Sent from <http://overview.mail.yahoo.com/mobile/?.src=Android> Yahoo Mail on Android

_____

From: Dede pmaho_log4@nabatisnack.co.id [belajar-excel] <belajar-excel@yahoogroups.com>;
To: <belajar-excel@yahoogroups.com>;
Subject: RE: [belajar-excel] Hiden Otomatis
Sent: Wed, Apr 29, 2015 10:07:09 AM

Eh sori seperti ini :

Private Sub Worksheet_Activate()

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Else

End If

End Sub

Tapi kode 1000616 untuk hide barisnya belum sesuai.

Salam,

DD

From: Dede pmaho_log4@nabatisnack.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 16:52
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear Mr Toby,

Saya tambahkan untuk kode 1000597 dan 1000616 seperti dbi :

Private Sub Worksheet_Activate()

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Else

End If

End Sub

Tapi belum bisa, salah dimana yah? Maklum kurang ngudeng VB.., semoga tidak bosan ya Mr....

Salam,

DD

From: Dede pmaho_log4@nabatisnack.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 16:27
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Hiden Otomatis

Dear Mr Toby,

Terima kasih atas feedbacknya, akan saya tanbahkan untuk kode yang lain.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 15:45
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Sebenarnya tinggal modif formula di sheet"View" aja dengan menambahkan "AND" di formula IF.

Berikut filenya, saya coba hanya untuk No.1000596 untuk no selanjutnya kamu bisa teruskan sama seperti No.10000596.

Selamat mencoba...

It's work in Office 2013 SP1.

TerimaKasih

Pada Rabu, 29 April 2015 14:48, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Daear Mr Toby,

Setelah saya pakai dan sedikit di refresh data yang ada di sheet "Rekap", ternyata masih belum sesuai harapan. Mungkin sayanya kurang detail penjelasannya.

Begini rulenya Mr...,

Kita bicara kode 1000596 yah.

Syarat Kolom : 1. Jika X58>0 dan AK58>0 dan AX58>0, maka jangan ada kolom yang di hide

2. Jika X58>0 dan AK58>0 dan AX58=0, maka kolom AL s/d AX di hide

3. Jika X58>0 dan AK58=0 dan AX58=0, maka kolom Y s/d AX di hide

Syarat Baris : 1. Sembunyikan mulai di baris berapa saja tergantung dari akhir kode di kolom C sheet "View".

Dan begitu juga untuk kode 1000597 dan 1000616.

Demikian penjelasannya semoga tidak bingung dan dapat membantu kembali.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:27
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis

Dear DD,

Sama-sama...asal ada kemauan dan kerja keras, ngga ada yang ga mungkin untuk di pecahkan..

TerimaKasih.

Pada Selasa, 28 April 2015 17:23, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear Mr Toby,

Luarrr Biasa....,

Saya mah gak pernah kepikiran klo masalah saya ini ternyata ada solusinya. Ckckckckck....

Saya haturkan banyak terima kasih Mr Toby, semoga ilmu Anda bermanfaaat dan membawa kebaikan.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:08
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Berikut Filenya,semoga bisa membantu.

TerimaKasih

Pada Selasa, 28 April 2015 15:53, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear BeExceller,

Bisakan sebuah ROW dan COLUMN ter"Hide" otomatis ?

Ini pengembangan kasus hyperlink tadi, semoga teman-teman tidak bosan yah...

Tinggal satu langkah lagi untuk menyempurnakan report terlampir, yaitu dengan meng"Hide" otomatis baris kosong di sheet "View" dan kolom yang tidak ada datanya pada saat saya klik kode subdist di sheet "List Kirim GT" apakah hal itu memungkinkan ?

Mohon pencerahannya dari para master sekalian.

Salam,

DD

_____

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.

Reply to sender . Reply to group . Reply via Web Post . All Messages (26) . Top ^

1g

Re: Hiden Otomatis

Wed Apr 29, 2015 4:09 am (PDT) . Posted by:

"Toby kenzo" tobykenzo

Dear DD,

Don't panic...kalo kamu lihat formulanya lebih detail di sheet"view&quot; itu bisa di otak-atik sesuai dgn kebutuhan..tp ngga apa2 mungkin kmu terburu-buru untuk menyelesaikannya,ok nanti saya coba buatkan sesuai dgn yg kamu inginkan...

Sent from Yahoo Mail on Android

Reply to sender . Reply to group . Reply via Web Post . All Messages (26) . Top ^

1h

Re: Hiden Otomatis

Wed Apr 29, 2015 4:13 am (PDT) . Posted by:

"Dede"

Dear Mr Tobu,

Thank's berat atas dukungannya.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 18:09
To: belajar-excel@yahoogroups.com
Subject: Re: RE: RE: [belajar-excel] Hiden Otomatis

Dear DD,

Don't panic...kalo kamu lihat formulanya lebih detail di sheet"view" itu bisa di otak-atik sesuai dgn kebutuhan..tp ngga apa2 mungkin kmu terburu-buru untuk menyelesaikannya,ok nanti saya coba buatkan sesuai dgn yg kamu inginkan...

Sent from <http://overview.mail.yahoo.com/mobile/?.src=Android> Yahoo Mail on Android

_____

From: Dede pmaho_log4@nabatisnack.co.id [belajar-excel] <belajar-excel@yahoogroups.com>;
To: <belajar-excel@yahoogroups.com>;
Subject: RE: RE: [belajar-excel] Hiden Otomatis
Sent: Wed, Apr 29, 2015 10:37:01 AM

Dear Mr Toby,

Blm bisa dikatakan sesuai...

Setelah saya analisa, View yang saya lihat tidak mengambalikan data sesungguhnya setelahnya. Maksudnya begini...

Misalkan sayalihat stok 1000596, secara View sudah sesuai harapan (Baris yang terhiden 24 s/d 57), namun ketika saya klik 1000616 View tetap menyembunyikan baris 24 s/d 57, seharusnya yang terhiden mulai di baris 27 s/d 57.

Masalahnya adalah stock yang mempunyai baris sedikit itu tidak mau mengembalikan stock yang jumlah barisnya lebih banyak, karena kalau saya klik 1000616 (awal buka file), stock ini kan mempunyai 15 baris, View akan sesuai harapan kalau kode berikutnya mempunyai jumlah baris yang sama atau lebih kecil. Tapi kalau kode berikutnya mempunyai jumlah stock yang lebih banyak View tetap dengan baris 15.

Semoga tidak bingung Mr...

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 17:20
To: belajar-excel@yahoogroups.com
Subject: Re: RE: [belajar-excel] Hiden Otomatis

Dear DD,

untuk yg 1000596 apa sudah sesuai..?

Sent from Yahoo Mail on Android <http://overview.mail.yahoo.com/mobile/?.src=Android>

_____

From: Dede pmaho_log4@nabatisnack.co.id [belajar-excel] <belajar-excel@yahoogroups.com>;
To: <belajar-excel@yahoogroups.com>;
Subject: RE: [belajar-excel] Hiden Otomatis
Sent: Wed, Apr 29, 2015 10:07:09 AM

Eh sori seperti ini :

Private Sub Worksheet_Activate()

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Else

End If

End Sub

Tapi kode 1000616 untuk hide barisnya belum sesuai.

Salam,

DD

From: Dede pmaho_log4@nabatisnack.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 16:52
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear Mr Toby,

Saya tambahkan untuk kode 1000597 dan 1000616 seperti dbi :

Private Sub Worksheet_Activate()

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000596 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000597 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Dim ans As String

ans = Sheet2.Range("C1").Value

If Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value > 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = False

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value > 0 And Sheet2.Range("AX58").Value = 0 Then

Range("Y:AX").EntireColumn.Hidden = False

Range("AL:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

ElseIf Sheet2.Range("D3").Value = 1000616 And Sheet2.Range("X58").Value > 0 And Sheet2.Range("AK58").Value = 0 And Sheet2.Range("AX58").Value = 0 Then

Range("AL:AX").EntireColumn.Hidden = False

Range("Y:AX").EntireColumn.Hidden = True

Range(Cells(ans, 1), Cells(57, 1)).EntireRow.Hidden = True

Else

End If

End Sub

Tapi belum bisa, salah dimana yah? Maklum kurang ngudeng VB.., semoga tidak bosan ya Mr....

Salam,

DD

From: Dede pmaho_log4@nabatisnack.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 16:27
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Hiden Otomatis

Dear Mr Toby,

Terima kasih atas feedbacknya, akan saya tanbahkan untuk kode yang lain.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 29 April 2015 15:45
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Sebenarnya tinggal modif formula di sheet"View" aja dengan menambahkan "AND" di formula IF.

Berikut filenya, saya coba hanya untuk No.1000596 untuk no selanjutnya kamu bisa teruskan sama seperti No.10000596.

Selamat mencoba...

It's work in Office 2013 SP1.

TerimaKasih

Pada Rabu, 29 April 2015 14:48, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Daear Mr Toby,

Setelah saya pakai dan sedikit di refresh data yang ada di sheet "Rekap", ternyata masih belum sesuai harapan. Mungkin sayanya kurang detail penjelasannya.

Begini rulenya Mr...,

Kita bicara kode 1000596 yah.

Syarat Kolom : 1. Jika X58>0 dan AK58>0 dan AX58>0, maka jangan ada kolom yang di hide

2. Jika X58>0 dan AK58>0 dan AX58=0, maka kolom AL s/d AX di hide

3. Jika X58>0 dan AK58=0 dan AX58=0, maka kolom Y s/d AX di hide

Syarat Baris : 1. Sembunyikan mulai di baris berapa saja tergantung dari akhir kode di kolom C sheet "View".

Dan begitu juga untuk kode 1000597 dan 1000616.

Demikian penjelasannya semoga tidak bingung dan dapat membantu kembali.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:27
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis

Dear DD,

Sama-sama...asal ada kemauan dan kerja keras, ngga ada yang ga mungkin untuk di pecahkan..

TerimaKasih.

Pada Selasa, 28 April 2015 17:23, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear Mr Toby,

Luarrr Biasa....,

Saya mah gak pernah kepikiran klo masalah saya ini ternyata ada solusinya. Ckckckckck....

Saya haturkan banyak terima kasih Mr Toby, semoga ilmu Anda bermanfaaat dan membawa kebaikan.

Salam,

DD

From: Toby kenzo tobykenzo@yahoo.co.id [belajar-excel] [mailto:belajar-excel@yahoogroups.com]
Sent: 28 April 2015 17:08
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Hiden Otomatis [1 Attachment]

Dear DD,

Berikut Filenya,semoga bisa membantu.

TerimaKasih

Pada Selasa, 28 April 2015 15:53, "Dede pmaho_log4@nabatisnack.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Dear BeExceller,

Bisakan sebuah ROW dan COLUMN ter"Hide" otomatis ?

Ini pengembangan kasus hyperlink tadi, semoga teman-teman tidak bosan yah...

Tinggal satu langkah lagi untuk menyempurnakan report terlampir, yaitu dengan meng"Hide" otomatis baris kosong di sheet "View" dan kolom yang tidak ada datanya pada saat saya klik kode subdist di sheet "List Kirim GT" apakah hal itu memungkinkan ?

Mohon pencerahannya dari para master sekalian.

Salam,

DD

_____

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.

Reply to sender . Reply to group . Reply via Web Post . All Messages (26) . Top ^

1i

Re: Hiden Otomatis [1 Attachment]

Wed Apr 29, 2015 5:44 am (PDT) . Posted by:

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

Hai DD,

File terlampir.

Wassalam,
Kid

On Tue, Apr 28, 2015 at 6:49 PM, Dede pmaho_log4@nabatisnack.co.id
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:

>
>
> Dear BeExceller,
>
>
>
> Bisakan sebuah ROW dan COLUMN ter"Hide" otomatis ?
>
> Ini pengembangan kasus hyperlink tadi, semoga teman-teman tidak bosan
> yah...
>
> Tinggal satu langkah lagi untuk menyempurnakan report terlampir, yaitu
> dengan meng"Hide" otomatis baris kosong di sheet "View" dan kolom yang
> tidak ada datanya pada saat saya klik kode subdist di sheet "List Kirim GT"
> apakah hal itu memungkinkan ?
>
> Mohon pencerahannya dari para master sekalian.
>
>
>
> Salam,
>
> DD
>
> ------------------------------
> 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.
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (26) . Top ^

2a

Re: Absensi Extra [1 Attachment]

Wed Apr 29, 2015 5:52 am (PDT) . Posted by:

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

Hai Catur,

Bisa jadi, dengan Anda membuatkan tabel yang menjelaskan record mana saja
yang berstatus karyawan dan mana yang manajemen akan membuat permasalahan
menjadi lebih jelas.

Bila perlu, dibuat juga tabel schema punishment yang lebih baik dengan
kolom-kolom :
1. jenis data (karyawan atau manajemen) -> seperti yang sudah ada di 1
kolom (termasuk header tidak usah di merge)
2. batas bawah nilai menit mulai terlambat -> berupa bilangan menit (booleh
bertipe numerik atau bertipe time)
3. persen potongan uang makan -> kalau tidak ada potongan isi dengan 0%
(tanpa ada keterengan atau kalimat indah lainnya)
4. rupiah denda -> kalau tidak ada isi dengan 0 (berupa bilangan alias
tanpa ada keterangan '/ menit' atau kalimat indah lainnya)

Semoga dengan penambahan informasi tersebut akan memudahkan BeExceller
sebagai calon pengurun solusi, sehingga lebih cepat mendapat respon.

Wassalam,
Kid

2015-04-28 21:03 GMT+10:00 caturbp@yahoo.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> dh Excel,
>
> Mohon bantuan untuk mengatasi permasalahan perihal absensi di kantor saya
>
> Management kami akan memberlakukan permasalah absensi dengan denda sesuai
> dengan keterlambatan masuk kerja
> di hitung per'menit bervariasi
>
> *Soal dan denda terlampir
>
>
>
>
>
>
>
> *Terima KasihHormat
> SayaCaturBP089603973154----------------------------------------------------------*
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

3a

Rekap jumlah dan Nama Barang

Wed Apr 29, 2015 9:21 am (PDT) . Posted by:

"Muzakki Harun" muzakkiharun@ymail.com

Assalamu"alaikum Wr. WB.
Dear Master B-excel ...Selama ini saya punya tugas mencatat Belanja Barang konsumsi semua anggota KPRI di tempat kerja saya. Yang menjadi kendala adalah ketika saya merekap nama barang belanaan dan banyaknya barang belanjaan saya lakukan secara manual dan cukup banyak menyita waktu.
Mohon pencerahan para Master B-excel, formula yang bisa dipakai untuk merekap dan menjumlah banyaknya barang. File terlampir.Atas bantuan master B-excel saya haturkan terimakasih

Wassalamualaikum Wr.Wb.
Salam,
Muzakki Harun

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

3b

Re: Rekap jumlah dan Nama Barang [1 Attachment]

Wed Apr 29, 2015 3:27 pm (PDT) . Posted by:

"Fatimah Nazmi"

kalau saya sih pakai rumus SUMIF aja mas/om
=SUMIF($D$4:$D$14,H4,$E$4:$E$14)

Pada tanggal 29/04/15, Muzakki Harun muzakkiharun@ymail.com
[belajar-excel] <belajar-excel@yahoogroups.com> menulis:
> Assalamu"alaikum Wr. WB.
> Dear Master B-excel ...Selama ini saya punya tugas mencatat Belanja Barang
> konsumsi semua anggota KPRI di tempat kerja saya. Yang menjadi kendala
> adalah ketika saya merekap nama barang belanaan dan banyaknya barang
> belanjaan saya lakukan secara manual dan cukup banyak menyita waktu.
> Mohon pencerahan para Master B-excel, formula yang bisa dipakai untuk
> merekap dan menjumlah banyaknya barang. File terlampir.Atas bantuan master
> B-excel saya haturkan terimakasih
>
>
> Wassalamualaikum Wr.Wb.
> Salam,
> Muzakki Harun
>
>
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

3c

Re: Rekap jumlah dan Nama Barang [1 Attachment]

Wed Apr 29, 2015 3:35 pm (PDT) . Posted by:

"Fatimah Nazmi"

rumus di kolom I kalau saya sih pakai rumus SUMIF aja mas/om
=SUMIF($D$4:$D$14,H4,$E$4:$E$14)
tapi itu masalahnya di kolom H.... (error juga saya)
rumusnya yang nemu hasil copas sana copas sini jadinya :
=Iferror(Index($G$4:$G$14,Small(If(Match($G$4:$G$14,$G$4:$G$14,0)=Row(Indirect("1:"&Rows($G$4:$G$14))),Match($G$4:$G$14,$G$4:$G$14,0),""),Row(Indirect("1:"&Rows($G$4:$G$14))))),"")
dan urutannya belum sesuai dengan permintaan.
dengan ngeblok seluruh kolom dari H4:H11 lalu tekan ctrl+alt+enter
itupun pakai kolom bantu di kolom G4:G14 yang pake rumus :
=Iferror(Index($D$4:$D$14,Small(If($D$4:$D$14<>"",Row(Indirect("1:"&Rows($D$4:$D$14)))),Row(Indirect("1:"&Rows($D$4:$D$14))))),"").
pake ctrl+alt+enter juga.
muncul masalah baru.... bingung juga membuat rumus di kolom H4:H14
tanpa kolom bantu yang ada di G4:G14 dan membuat urutan di kolom
H4:H11 sesuai abjad (sesuai lampiran file mas/om)
sama-sama kita berdoa agar ada yang membantu kita ya mas/om.....biar
dapat ilmu baru.......

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

3d

Bls: [belajar-excel] Rekap jumlah dan Nama Barang [1 Attachment]

Wed Apr 29, 2015 4:24 pm (PDT) . Posted by:

"Muzakki Harun" muzakkiharun@ymail.com

Terimakasih Miss Fatimah Nazmi, untuk nama barang yang diinginkan memang berurutan sesuai abjad. Hehehe, Amiin semoga do'a kita terkabul. Insya Allah kasus ini akan terselesaikan oleh master-master b-excel.

Pada Kamis, 30 April 2015 5:33, "Fatimah Nazmi fatimahnazmy@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  rumus di kolom I kalau saya sih pakai rumus SUMIF aja mas/om
=SUMIF($D$4:$D$14,H4,$E$4:$E$14)
tapi itu masalahnya di kolom H.... (error juga saya)
rumusnya yang nemu hasil copas sana copas sini jadinya :
=Iferror(Index($G$4:$G$14,Small(If(Match($G$4:$G$14,$G$4:$G$14,0)=Row(Indirect("1:"&Rows($G$4:$G$14))),Match($G$4:$G$14,$G$4:$G$14,0),""),Row(Indirect("1:"&Rows($G$4:$G$14))))),"")
dan urutannya belum sesuai dengan permintaan.
dengan ngeblok seluruh kolom dari H4:H11 lalu tekan ctrl+alt+enter
itupun pakai kolom bantu di kolom G4:G14 yang pake rumus :
=Iferror(Index($D$4:$D$14,Small(If($D$4:$D$14<>"",Row(Indirect("1:"&Rows($D$4:$D$14)))),Row(Indirect("1:"&Rows($D$4:$D$14))))),"").
pake ctrl+alt+enter juga.
muncul masalah baru.... bingung juga membuat rumus di kolom H4:H14
tanpa kolom bantu yang ada di G4:G14 dan membuat urutan di kolom
H4:H11 sesuai abjad (sesuai lampiran file mas/om)
sama-sama kita berdoa agar ada yang membantu kita ya mas/om.....biar
dapat ilmu baru....... #yiv3162343492 #yiv3162343492 -- #yiv3162343492ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3162343492 #yiv3162343492ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3162343492 #yiv3162343492ygrp-mkp #yiv3162343492hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3162343492 #yiv3162343492ygrp-mkp #yiv3162343492ads {margin-bottom:10px;}#yiv3162343492 #yiv3162343492ygrp-mkp .yiv3162343492ad {padding:0 0;}#yiv3162343492 #yiv3162343492ygrp-mkp .yiv3162343492ad p {margin:0;}#yiv3162343492 #yiv3162343492ygrp-mkp .yiv3162343492ad a {color:#0000ff;text-decoration:none;}#yiv3162343492 #yiv3162343492ygrp-sponsor #yiv3162343492ygrp-lc {font-family:Arial;}#yiv3162343492 #yiv3162343492ygrp-sponsor #yiv3162343492ygrp-lc #yiv3162343492hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3162343492 #yiv3162343492ygrp-sponsor #yiv3162343492ygrp-lc .yiv3162343492ad {margin-bottom:10px;padding:0 0;}#yiv3162343492 #yiv3162343492actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3162343492 #yiv3162343492activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3162343492 #yiv3162343492activity span {font-weight:700;}#yiv3162343492 #yiv3162343492activity span:first-child {text-transform:uppercase;}#yiv3162343492 #yiv3162343492activity span a {color:#5085b6;text-decoration:none;}#yiv3162343492 #yiv3162343492activity span span {color:#ff7900;}#yiv3162343492 #yiv3162343492activity span .yiv3162343492underline {text-decoration:underline;}#yiv3162343492 .yiv3162343492attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3162343492 .yiv3162343492attach div a {text-decoration:none;}#yiv3162343492 .yiv3162343492attach img {border:none;padding-right:5px;}#yiv3162343492 .yiv3162343492attach label {display:block;margin-bottom:5px;}#yiv3162343492 .yiv3162343492attach label a {text-decoration:none;}#yiv3162343492 blockquote {margin:0 0 0 4px;}#yiv3162343492 .yiv3162343492bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3162343492 .yiv3162343492bold a {text-decoration:none;}#yiv3162343492 dd.yiv3162343492last p a {font-family:Verdana;font-weight:700;}#yiv3162343492 dd.yiv3162343492last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3162343492 dd.yiv3162343492last p span.yiv3162343492yshortcuts {margin-right:0;}#yiv3162343492 div.yiv3162343492attach-table div div a {text-decoration:none;}#yiv3162343492 div.yiv3162343492attach-table {width:400px;}#yiv3162343492 div.yiv3162343492file-title a, #yiv3162343492 div.yiv3162343492file-title a:active, #yiv3162343492 div.yiv3162343492file-title a:hover, #yiv3162343492 div.yiv3162343492file-title a:visited {text-decoration:none;}#yiv3162343492 div.yiv3162343492photo-title a, #yiv3162343492 div.yiv3162343492photo-title a:active, #yiv3162343492 div.yiv3162343492photo-title a:hover, #yiv3162343492 div.yiv3162343492photo-title a:visited {text-decoration:none;}#yiv3162343492 div#yiv3162343492ygrp-mlmsg #yiv3162343492ygrp-msg p a span.yiv3162343492yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3162343492 .yiv3162343492green {color:#628c2a;}#yiv3162343492 .yiv3162343492MsoNormal {margin:0 0 0 0;}#yiv3162343492 o {font-size:0;}#yiv3162343492 #yiv3162343492photos div {float:left;width:72px;}#yiv3162343492 #yiv3162343492photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv3162343492 #yiv3162343492photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3162343492 #yiv3162343492reco-category {font-size:77%;}#yiv3162343492 #yiv3162343492reco-desc {font-size:77%;}#yiv3162343492 .yiv3162343492replbq {margin:4px;}#yiv3162343492 #yiv3162343492ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3162343492 #yiv3162343492ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3162343492 #yiv3162343492ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3162343492 #yiv3162343492ygrp-mlmsg select, #yiv3162343492 input, #yiv3162343492 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3162343492 #yiv3162343492ygrp-mlmsg pre, #yiv3162343492 code {font:115% monospace;}#yiv3162343492 #yiv3162343492ygrp-mlmsg * {line-height:1.22em;}#yiv3162343492 #yiv3162343492ygrp-mlmsg #yiv3162343492logo {padding-bottom:10px;}#yiv3162343492 #yiv3162343492ygrp-msg p a {font-family:Verdana;}#yiv3162343492 #yiv3162343492ygrp-msg p#yiv3162343492attach-count span {color:#1E66AE;font-weight:700;}#yiv3162343492 #yiv3162343492ygrp-reco #yiv3162343492reco-head {color:#ff7900;font-weight:700;}#yiv3162343492 #yiv3162343492ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3162343492 #yiv3162343492ygrp-sponsor #yiv3162343492ov li a {font-size:130%;text-decoration:none;}#yiv3162343492 #yiv3162343492ygrp-sponsor #yiv3162343492ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3162343492 #yiv3162343492ygrp-sponsor #yiv3162343492ov ul {margin:0;padding:0 0 0 8px;}#yiv3162343492 #yiv3162343492ygrp-text {font-family:Georgia;}#yiv3162343492 #yiv3162343492ygrp-text p {margin:0 0 1em 0;}#yiv3162343492 #yiv3162343492ygrp-text tt {font-size:120%;}#yiv3162343492 #yiv3162343492ygrp-vital ul li:last-child {border-right:none !important;}#yiv3162343492

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

4a

Re: Mengelompokkan nilai sesuai tema [1 Attachment]

Wed Apr 29, 2015 4:08 pm (PDT) . Posted by:

"Fatimah Nazmi"

wah terima kasih Mr..... atas penjelasannya....
ternyata pola : *1,12, 23, 34* , *2, 13, 24, 35*, *dst* bisa dibuat
tanpa di ketik manual....... 1x lagi terima kasih Mr....

Reply to sender . Reply to group . Reply via Web Post . All Messages (6) . Top ^

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

Visit Your Group

• New Members 5

• Privacy • Unsubscribe • Terms of Use