[smf_addin] Digest Number 2232[2 Attachments]

EXCEL Stock Market Functions Add-in

Messages In This Digest (11 Messages)

1a.

Re: element numbers From: NicholasDavid

1b.

Re: element numbers From: Randy Harmelink

2a.

Error in Formula From: Christina Guo

2b.

Re: Error in Formula From: Ron Spruell

2c.

Re: Error in Formula From: Christina Guo

2d.

Re: Error in Formula From: Randy Harmelink

2e.

Re: Error in Formula From: Christina Guo

2f.

Re: Error in Formula From: Ron Spruell

2g.

Re: Error in Formula From: Christina Guo

2h.

Re: Error in Formula From: Christina Guo

2i.

Re: Error in Formula [1 Attachment] From: Randy Harmelink

View All Topics | Create New Topic

Messages

1a.

Re: element numbers

Posted by: "NicholasDavid" nicholasdavid@comcast.net   dnicholas4967

Thu May 31, 2012 8:51 am (PDT)

Where do I go to download the current "Elements Definitions" .....thanks!

----- Original Message -----
From: "Gordon" <gordonj5@aol.com>
To: "smf addin" <smf_addin@yahoogroups.com>
Sent: Monday, May 28, 2012 1:48:04 PM
Subject: [smf_addin] element numbers

 

I looked at the elements definitions and I seen no change from previous.
I'm looking for the last price of a stock.
The element # should be 25 to bring this information in.
It doesn't.
Am I misinterpreting the information in element definitions or am I using the wrong formula.

Sorry that I can't seem to understand your answer about element definitions.

What replaces 25?
What replaces 30?
What replaces 31?

Thank you, Gordon Jones

Back to top

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

1b.

Re: element numbers

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu May 31, 2012 9:21 am (PDT)

http://finance.groups.yahoo.com/group/smf_addin/message/18529

On Thu, May 31, 2012 at 8:51 AM, NicholasDavid <nicholasdavid@comcast.net>wrote:

>
> Where do I go to download the current "Elements Definitions" .....thanks!
>

Back to top

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

2a.

Error in Formula

Posted by: "Christina Guo" cguo563@yahoo.com   cguo563

Thu May 31, 2012 12:05 pm (PDT)

Hi Randy,
 
I'm trying to input Revenue and Net Income from YahooFinance for a list of 100 companies for 2009, 2010, and 2011 into Excel usingRCHGetTableCell. I looked at the templates, but they all displayed Error cellswhen I opened the worksheets.
 
I came up with this formula:
=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"annual",1,">Revenue")
 
But I get Error.
 
This formula works:
=RCHGetTableCell("http://finance.yahoo.com/q/ks?s="&E1,1,">Revenue")
 
But this won't let me look at other years?
 
Thanks so much!
 

 
Christina Guo
Harvard College 2013

Back to top

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

2b.

Re: Error in Formula

Posted by: "Ron Spruell" hashky@yahoo.com   hashky

Thu May 31, 2012 12:37 pm (PDT)

Try
=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"annual",2,"Annual Data",,">Revenue")

For Revenue 

Change the Cell# to move to the other years, i.e., 3, 4.

>________________________________
> From: Christina Guo <cguo563@yahoo.com>
>To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
>Sent: Thursday, May 31, 2012 1:40 PM
>Subject: [smf_addin] Error in Formula
>
>
> 
>Hi Randy,
> 
>I'm trying to input Revenue and Net Income from YahooFinance for a list of 100 companies for 2009, 2010, and 2011 into Excel usingRCHGetTableCell. I looked at the templates, but they all displayed Error cellswhen I opened the worksheets.
> 
>I came up with this formula:
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"annual",1,">Revenue")
> 
>But I get Error.
> 
>This formula works:
>=RCHGetTableCell("http://finance.yahoo.com/q/ks?s="&E1,1,">Revenue")
> 
>But this won't let me look at other years?
> 
>Thanks so much!
> 
>
> 
>Christina Guo
>Harvard College 2013
>
>
>

Back to top

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

2c.

Re: Error in Formula

Posted by: "Christina Guo" cguo563@yahoo.com   cguo563

Thu May 31, 2012 1:01 pm (PDT)

[Attachment(s) from Christina Guo included below]

Thanks for the help, Ron. But it's still giving me Error for some reason. I've attached my Excel sheet here. Thank you so much for you help!
 
Christina
Harvard College 2013


________________________________
From: Ron Spruell <hashky@yahoo.com>
To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
Sent: Thursday, May 31, 2012 12:37 PM
Subject: Re: [smf_addin] Error in Formula


 

Try
=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"annual",2,"Annual Data",,">Revenue")
For Revenue 

Change the Cell# to move to the other years, i.e., 3, 4.

>________________________________
> From: Christina Guo <cguo563@yahoo.com>
>To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
>Sent: Thursday, May 31, 2012 1:40 PM
>Subject: [smf_addin] Error in Formula
>
>
> 
>Hi Randy,
>
>I'm trying to input Revenue and Net Income from YahooFinance for a list of 100 companies for 2009, 2010, and 2011 into Excel usingRCHGetTableCell. I looked at the templates, but they all displayed Error cellswhen I opened the worksheets.
>
>I came up with this formula:
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"annual",1,">Revenue")
>
>But I get Error.
>
>This formula works:
>=RCHGetTableCell("http://finance.yahoo.com/q/ks?s="&E1,1,">Revenue")
>
>But this won't let me look at other years?
>
>Thanks so much!
>
>
> 
>Christina Guo
>Harvard College 2013
>
>

Attachment(s) from Christina Guo

1 of 1 File(s)

With Add-in.xlsx

Back to top

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

2d.

Re: Error in Formula

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu May 31, 2012 1:18 pm (PDT)

You're getting errors because your URL is wrong. You're telling it to
concatenate "annual" directly to the ticker symbol, so instead of a ticker
symbol of MMM, you end up with MMMannual.

You need an additional ampersand directly in front of "annual", as part of
the literal.

Also, I don't see a line item defined by ">Revenue", at least for ticker
MMM. And the leading ">" only works if they code the web page that way.
Here, they don't.

And, as Ron noted, you need to vary the "1" parameter to look at additional
table cells following the search string. Check the documentation for the
function for more information.

Instead, try:

=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",1,"Total
Revenue")
=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",2,"Total
Revenue")
=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",3,"Total
Revenue")

On Thu, May 31, 2012 at 11:40 AM, Christina Guo <cguo563@yahoo.com> wrote:

>
> I'm trying to input Revenue and Net Income from YahooFinance for a list of
> 100 companies for 2009, 2010, and 2011 into Excel usingRCHGetTableCell. I
> looked at the templates, but they all displayed Error cellswhen I opened
> the worksheets.
>
> I came up with this formula:
> =RCHGetTableCell("
> http://finance.yahoo.com/q/is?s="&A1&"annual",1,">Revenue")
>
> But I get Error.
>
> This formula works:
> =RCHGetTableCell("http://finance.yahoo.com/q/ks?s="&E1,1,">Revenue")
>
> But this won't let me look at other years?
>
> Thanks so much!
>

Back to top

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

2e.

Re: Error in Formula

Posted by: "Christina Guo" cguo563@yahoo.com   cguo563

Thu May 31, 2012 1:33 pm (PDT)

Thank you so much, Randy! It works and I really appreciate it.
 
Just one last quick thing: How would I change the formula for Net Income? I tried replacing "Total Revenue" with "Net Income" but got Search in the cell.
 
Christina Guo
Harvard College 2013


________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Thursday, May 31, 2012 1:18 PM
Subject: Re: [smf_addin] Error in Formula


 

You're getting errors because your URL is wrong. You're telling it to concatenate "annual" directly to the ticker symbol, so instead of a ticker symbol of MMM, you end up with MMMannual.

You need an additional ampersand directly in front of "annual", as part of the literal.

Also, I don't see a line item defined by ">Revenue", at least for ticker MMM. And the leading ">" only works if they code the web page that way. Here, they don't.

And, as Ron noted, you need to vary the "1" parameter to look at additional table cells following the search string. Check the documentation for the function for more information.

Instead, try:

=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",1,"Total Revenue")
=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",2,"Total Revenue")
=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",3,"Total Revenue")

On Thu, May 31, 2012 at 11:40 AM, Christina Guo <cguo563@yahoo.com> wrote:

 
>
>I'm trying to input Revenue and Net Income from YahooFinance for a list of 100 companies for 2009, 2010, and 2011 into Excel usingRCHGetTableCell. I looked at the templates, but they all displayed Error cellswhen I opened the worksheets.
>
>I came up with this formula:
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s=%22&A1&%22annual%22,1,%22%3ERevenue")
>
>But I get Error.
>
>This formula works:
>=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=%22&E1,1,%22%3ERevenue")
>
>But this won't let me look at other years?
>
>Thanks so much!
>

Back to top

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

2f.

Re: Error in Formula

Posted by: "Ron Spruell" hashky@yahoo.com   hashky

Thu May 31, 2012 1:59 pm (PDT)

You have to position the reference further down the page with something like "Other Items".  There are several "Net Incomes" on the page.

=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",1,"Other Items","Net Income")

>________________________________
> From: Christina Guo <cguo563@yahoo.com>
>To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
>Sent: Thursday, May 31, 2012 3:33 PM
>Subject: Re: [smf_addin] Error in Formula
>
>
> 
>Thank you so much, Randy! It works and I really appreciate it.
> 
>Just one last quick thing: How would I change the formula for Net Income? I tried replacing "Total Revenue" with "Net Income" but got Search in the cell.
> 
>Christina Guo
>Harvard College 2013
>
>From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Thursday, May 31, 2012 1:18 PM
>Subject: Re: [smf_addin] Error in Formula
>
>
> 
>You're getting errors because your URL is wrong. You're telling it to concatenate "annual" directly to the ticker symbol, so instead of a ticker symbol of MMM, you end up with MMMannual.
>
>You need an additional ampersand directly in front of "annual", as part of the literal.
>
>Also, I don't see a line item defined by ">Revenue", at least for ticker MMM. And the leading ">" only works if they code the web page that way. Here, they don't.
>
>And, as Ron noted, you need to vary the "1" parameter to look at additional table cells following the search string. Check the documentation for the function for more information.
>
>Instead, try:
>
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",1,"Total Revenue")
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",2,"Total Revenue")
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",3,"Total Revenue")
>
>
>On Thu, May 31, 2012 at 11:40 AM, Christina Guo <cguo563@yahoo.com> wrote:
>
> 
>>
>>I'm trying to input Revenue and Net Income from YahooFinance for a list of 100 companies for 2009, 2010, and 2011 into Excel usingRCHGetTableCell. I looked at the templates, but they all displayed Error cellswhen I opened the worksheets.
>> 
>>I came up with this formula:
>>=RCHGetTableCell("http://finance.yahoo.com/q/is?s=%22&A1&%22annual%22,1,%22%3ERevenue")
>> 
>>But I get Error.
>> 
>>This formula works:
>>=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=%22&E1,1,%22%3ERevenue")
>> 
>>But this won't let me look at other years?
>> 
>>Thanks so much!
>>
>
>
>
>
>
>

Back to top

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

2g.

Re: Error in Formula

Posted by: "Christina Guo" cguo563@yahoo.com   cguo563

Thu May 31, 2012 4:04 pm (PDT)

Great, thanks so much! Everything works.

________________________________
From: Ron Spruell <hashky@yahoo.com>
To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
Sent: Thursday, May 31, 2012 1:59 PM
Subject: Re: [smf_addin] Error in Formula


 

You have to position the reference further down the page with something like "Other Items".  There are several "Net Incomes" on the page.

=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",1,"Other Items","Net Income")

>________________________________
> From: Christina Guo <cguo563@yahoo.com>
>To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
>Sent: Thursday, May 31, 2012 3:33 PM
>Subject: Re: [smf_addin] Error in Formula
>
>
> 
>Thank you so much, Randy! It works and I really appreciate it.
> 
>Just one last quick thing: How would I change the formula for Net Income? I tried replacing "Total Revenue" with "Net Income" but got Search in the cell.
>
>Christina Guo
>Harvard College 2013
>
>
>________________________________
> From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Thursday, May 31, 2012 1:18 PM
>Subject: Re: [smf_addin] Error in Formula
>
>
> 
>You're getting errors because your URL is wrong. You're telling it to concatenate "annual" directly to the ticker symbol, so instead of a ticker symbol of MMM, you end up with MMMannual.
>
>You need an additional ampersand directly in front of "annual", as part of the literal.
>
>Also, I don't see a line item defined by ">Revenue", at least for ticker MMM. And the leading ">" only works if they code the web page that way. Here, they don't.
>
>And, as Ron noted, you need to vary the "1" parameter to look at additional table cells following the search string. Check the documentation for the function for more information.
>
>Instead, try:
>
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",1,"Total Revenue")
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",2,"Total Revenue")
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",3,"Total Revenue")
>
>
>On Thu, May 31, 2012 at 11:40 AM, Christina Guo <cguo563@yahoo.com> wrote:
>
> 
>>
>>I'm trying to input Revenue and Net Income from YahooFinance for a list of 100 companies for 2009, 2010, and 2011 into Excel usingRCHGetTableCell. I looked at the templates, but they all displayed Error cellswhen I opened the worksheets.
>>
>>I came up with this formula:
>>=RCHGetTableCell("http://finance.yahoo.com/q/is?s=%22&A1&%22annual%22,1,%22%3ERevenue")
>>
>>But I get Error.
>>
>>This formula works:
>>=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=%22&E1,1,%22%3ERevenue")
>>
>>But this won't let me look at other years?
>>
>>Thanks so much!
>>
>
>
>
>
>

Back to top

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

2h.

Re: Error in Formula

Posted by: "Christina Guo" cguo563@yahoo.com   cguo563

Thu May 31, 2012 4:22 pm (PDT)

[Attachment(s) from Christina Guo included below]

Darn, I thought everything was running smoothly. But here's my spreadsheet once more. Do you know why the formula stopped working for the chunk of data from line 49-86?
 
Christina Guo
Harvard College 2013


________________________________
From: Ron Spruell <hashky@yahoo.com>
To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
Sent: Thursday, May 31, 2012 1:59 PM
Subject: Re: [smf_addin] Error in Formula


 

You have to position the reference further down the page with something like "Other Items".  There are several "Net Incomes" on the page.

=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",1,"Other Items","Net Income")

>________________________________
> From: Christina Guo <cguo563@yahoo.com>
>To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
>Sent: Thursday, May 31, 2012 3:33 PM
>Subject: Re: [smf_addin] Error in Formula
>
>
> 
>Thank you so much, Randy! It works and I really appreciate it.
> 
>Just one last quick thing: How would I change the formula for Net Income? I tried replacing "Total Revenue" with "Net Income" but got Search in the cell.
>
>Christina Guo
>Harvard College 2013
>
>
>________________________________
> From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Thursday, May 31, 2012 1:18 PM
>Subject: Re: [smf_addin] Error in Formula
>
>
> 
>You're getting errors because your URL is wrong. You're telling it to concatenate "annual" directly to the ticker symbol, so instead of a ticker symbol of MMM, you end up with MMMannual.
>
>You need an additional ampersand directly in front of "annual", as part of the literal.
>
>Also, I don't see a line item defined by ">Revenue", at least for ticker MMM. And the leading ">" only works if they code the web page that way. Here, they don't.
>
>And, as Ron noted, you need to vary the "1" parameter to look at additional table cells following the search string. Check the documentation for the function for more information.
>
>Instead, try:
>
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",1,"Total Revenue")
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",2,"Total Revenue")
>=RCHGetTableCell("http://finance.yahoo.com/q/is?s="&A1&"&annual",3,"Total Revenue")
>
>
>On Thu, May 31, 2012 at 11:40 AM, Christina Guo <cguo563@yahoo.com> wrote:
>
> 
>>
>>I'm trying to input Revenue and Net Income from YahooFinance for a list of 100 companies for 2009, 2010, and 2011 into Excel usingRCHGetTableCell. I looked at the templates, but they all displayed Error cellswhen I opened the worksheets.
>>
>>I came up with this formula:
>>=RCHGetTableCell("http://finance.yahoo.com/q/is?s=%22&A1&%22annual%22,1,%22%3ERevenue")
>>
>>But I get Error.
>>
>>This formula works:
>>=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=%22&E1,1,%22%3ERevenue")
>>
>>But this won't let me look at other years?
>>
>>Thanks so much!
>>
>
>
>
>
>

Attachment(s) from Christina Guo

1 of 1 File(s)

With Add-in.xlsx

Back to top

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

2i.

Re: Error in Formula [1 Attachment]

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu May 31, 2012 4:36 pm (PDT)

Lines 49-86 worked fine here, except for those with bad ticker symbols.

On Thu, May 31, 2012 at 4:22 PM, Christina Guo <cguo563@yahoo.com> wrote:

>
> Darn, I thought everything was running smoothly. But here's my spreadsheet
> once more. Do you know why the formula stopped working for the chunk of
> data from line 49-86?
>

Back to top

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

Recent Activity

•  76
  New Members

Visit Your Group

Yahoo! News

Fashion News

What's the word on

fashion and style?

Search Ads

Get new customers.

List your web site

in Yahoo! Search.

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Need to Reply?

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

Create New Topic | Visit Your Group on the Web

Messages | Files | Photos | Polls

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

READ MORE....

[belajar-excel] Digest Number 1633

Milis Belajar Microsoft Excel

Messages In This Digest (9 Messages)

1a.

Bls: [belajar-excel] memisah abjad nama From: ghozi alkatiri

2a.

daftar kode pengawas otomatis From: Agus Siahabudin

2b.

Re: daftar kode pengawas otomatis From: Kid Mr.

3a.

Bls: [belajar-excel] Ambil data tertentu From: Magung Saputra

4a.

Kolom A, B, C dst menjadi 1, 2, 3 dst From: Harry

4b.

Re: Kolom A, B, C dst menjadi 1, 2, 3 dst From: Rara Wilis

5a.

Membuat Validasi utk Input Tanggal atau date From: milorambaldy

5b.

Re: Membuat Validasi utk Input Tanggal atau date From: bagus

6.

Akses database MS. Access dari Excel From: Heru Safrudin

View All Topics | Create New Topic

Messages

1a.

Bls: [belajar-excel] memisah abjad nama

Posted by: "ghozi alkatiri" ghozialkatiri@yahoo.co.id   ghozialkatiri

Thu May 31, 2012 3:16 am (PDT)

pada cell D2 tulis formula:
=MID($B2;COLUMNS($D2:D2);1)
copy ke kanan dan ke bawah.

wassalam

Ghozi Alkatiri

________________________________
Dari: Husnul Faizin <husnulfaizin@yahoo.co.id>
Kepada: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
Dikirim: Rabu, 30 Mei 2012 9:15
Judul: [belajar-excel] memisah abjad nama


 
salam para pakar excel...
mohon bantuan rumus untuk permasalahan cara memisahkan tiap abjad dari nama-nama seperti yang terdapat pada lampiran file.
terimakasih sebelumnya atas jawaban yang tidak terlalu lama..
wassalam.

Back to top

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

2a.

daftar kode pengawas otomatis

Posted by: "Agus Siahabudin" phont_4ng@yahoo.co.id   phont_4ng

Thu May 31, 2012 6:36 am (PDT)

salam kenal,
saya member baru. Baru-baru ini saya tertarik dengan bekerja menggunakan excel. Ternyata manfaat excel jauh lebih luas dari pengetahuan saya tentang excel..

saat ini saya menemukan permasalahan di excel. saya ingin membuat sebuah daftar pengawas ujian, namun sebelumnya saya harus mengetahui kesediaan. saya ingin kode pengawas muncul secara otomatis ketika seorang calon pengawas menyatakan kesediaannya.
untuk lebih jelasnya saya sertakan filenya..

terima kasih

Back to top

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

2b.

Re: daftar kode pengawas otomatis

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

Thu May 31, 2012 6:47 am (PDT)

Salam kenal Pak Agus,

Coba formula :
=COUNTIF(D$6:D6,D6)*(D6="YA")
kemudian di format cells -> Number -> Custom -> tulis di textbox format :
000;;

kemudian copy ke baris lainnya.

Wassalam
Kid.

2012/5/31 Agus Siahabudin <phont_4ng@yahoo.co.id>

> **
>
>
> salam kenal,
> saya member baru. Baru-baru ini saya tertarik dengan bekerja menggunakan
> excel. Ternyata manfaat excel jauh lebih luas dari pengetahuan saya tentang
> excel..
>
> saat ini saya menemukan permasalahan di excel. saya ingin membuat sebuah
> daftar pengawas ujian, namun sebelumnya saya harus mengetahui kesediaan.
> saya ingin kode pengawas muncul secara otomatis ketika seorang calon
> pengawas menyatakan kesediaannya.
> untuk lebih jelasnya saya sertakan filenya..
>
> terima kasih
>
>
>

Back to top

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

3a.

Bls: [belajar-excel] Ambil data tertentu

Posted by: "Magung Saputra" saputra.magung@yahoo.com   saputra.magung

Thu May 31, 2012 8:04 am (PDT)

Terimakasih Ki Semprul

________________________________
Dari: Ki Semprul <semprul.ki@gmail.com>
Kepada: belajar-excel@yahoogroups.com
Dikirim: Rabu, 30 Mei 2012 0:32
Judul: Re: [belajar-excel] Ambil data tertentu


Baris "BIAYA OS" dan baris "BIAYA KENDARAAN" walaupun seandainya nilai prosentasinya <= 100%
dapat SELALU ikut ditampilkan; dengan formula filternya diubah sedikit sbb:

Seperti sebelumnya: rumusnya berupa Array Formula Multi Hasil 

=SMALL(IF(('Source Data'!E4:E18>100%)+(LEFT('Source Data'!B4:B18,5)="BIAYA"),ROW(1:15)),ROW(1:15))

Ini berarti kita menambah sendiri SYARAT yg sudah ditetapkan oleh pak Magung yaitu :"Hasil yang diinginkan adalah list biaya-biaya yang memiliki prosentase > 100%saja. "

Untuk lengkapnya, termasuk rumus2 di 4 kolom utama, mohon dibuka lampiran > Sheet2 ('hasil yg diinginkan'

wassalam,
Semprul..Ki!

2012/5/29 Ki Semprul <semprul.ki@gmail.com>

--- cut ---
>Selain itu, Ki Semprul, dlm lampiran (di sheet 2) sudah membuatkan 
>MEMFILTER DENGAN FORMULA
>mungkin mas Magung belum sempat membuka / melihat rumusnya
>
>
>Memang hasilnya tidak persis sama dengan CONTOH HASIL YG DIINGINKAN karena ada
>hal yg bertentangan (lihat gambar) ada baris bernilai % <=100% ingin ditampilkan
>padahal Kriteria filternya: % > 100%.
>
>
>
>

Back to top

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

4a.

Kolom A, B, C dst menjadi 1, 2, 3 dst

Posted by: "Harry" hariansyahptk@yahoo.com   hariansyahptk

Thu May 31, 2012 8:09 am (PDT)

Dear BeExeller,

Mohon informasi dan solusi akan pertanyaan saya dng situasi sbb:
Pada sata Workbook yang memiliki banyak rumus yang telah saya buat, tampilan Worksheetnya berubah tidak seperti yang seharusnya tetapi tidak terlalu signifikan.
Perubahan tersebut terjadi pada tampilannya Kolomnya.
Pada Worksheet seharusnya adalah Kolom dilambangkan dengan huruf Abjad yaitu A, B, C dst sedangkan Row dilambangkan dengan angka yaitu 1, 2, 3 dst.

Pada Workbook yang telah saya buat tersebut Kolomya berubah menjadi 1, 2, 3 dst dan Row-nya tetap 1, 2, 3 dst.

Pertanyaan saya adalah: Bagaimana cara dan apa yang harus diganti jika Kolom tersebut dikembalikan lagi menjadi A, B, C dst...???

Terima kasih atas berkenan jawabannya.

Regards,

Harry

.

Back to top

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

4b.

Re: Kolom A, B, C dst menjadi 1, 2, 3 dst

Posted by: "Rara Wilis" ra2wilis@gmail.com   rr_wilis

Thu May 31, 2012 10:37 am (PDT)

Dlm me-notasi-kan Formula, di Excel dikenal ada 2 cara :

*Style A1 *
yaitu COLUMN diberi nama dengan HURUF mulai A, ROW di beri nama dengan
ANGKA mulai 1
Style ini sepertinya paling umum dipakai, seperti Pak Harry ceritakan itu,

*Style R1C1*
ROW diberi nama dengan ANGKA mulai 1, COLUMN juga diberi nama dengan ANGKA
mulai 1
Jadi alamat cell dengan *rujukan absolut $F$16* dlm style A1; >> notasinya
menjadi *R16C6* dalam style R1C1.
Jika alamat cellnya dengan *Relative Reference F16* notasinya bisa
berubah-ubah tergantung di cell mana ditulisnnya.
Misal ingin merujuk F16 dari cell H17, notasinya *=R[-1]C[-2]* Gampang
kan ?

Kedua *Syle* itu dapat *dengan BEBAS anda pilih* dengan mengatur setting
Checkbox *"R1C1 Reference Style"*
Jika CheckBox nya di Contreng (enabled / True) maka berlaku Style R1C1
Jika Contrengannya dihilangkan (disabled / false) maka berlaku Style A1

Di Excel 2007 setting tsb ada di Office Button > Excel Options > Tab
Formula, di area 'Working with Formula'
Kalau di Excel 2003: Menu Tools > Options > Tab General
(lihat gambar di bawah ini)

[image: Inline image 1]

Kalau pegang 'textbook panduan standar pemakaian excel', hal ini
diejelaskan di bab-bab depan / basic.
Mungkin pak Harry terlewat membacanya, atau bukunya ilang...

~Rara

2012/5/31 Harry <hariansyahptk@yahoo.com>

> **
> Dear BeExeller,
> Mohon informasi dan solusi akan pertanyaan saya dng situasi sbb:
> Pada sata Workbook yang memiliki banyak rumus yang telah saya buat,
> tampilan Worksheetnya berubah tidak seperti yang seharusnya tetapi tidak
> terlalu signifikan.
> Perubahan tersebut terjadi pada tampilannya Kolomnya.
> Pada Worksheet seharusnya adalah Kolom dilambangkan dengan huruf Abjad
> yaitu A, B, C dst sedangkan Row dilambangkan dengan angka yaitu 1, 2, 3 dst.
>
> Pada Workbook yang telah saya buat tersebut Kolomya berubah menjadi 1, 2,
> 3 dst dan Row-nya tetap 1, 2, 3 dst.
> Pertanyaan saya adalah: Bagaimana cara dan apa yang harus diganti jika
> Kolom tersebut dikembalikan lagi menjadi A, B, C dst...???
> Terima kasih atas berkenan jawabannya.
>
> Regards,
> Harry
>

Back to top

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

5a.

Membuat Validasi utk Input Tanggal atau date

Posted by: "milorambaldy" maulidin_yusfriyanto@yahoo.com   milorambaldy

Thu May 31, 2012 8:56 pm (PDT)

Dear Be-exceler

Mohon di bantu pencerahannya apabila saya ingin mencoba membuat aturan di Cell tertentu worksheet hanya bisa di input tanggal hari ini maksimalnya dan minimalnya 2 hari sebelumnya di dalam aturan 5 hari kerja saja.

dengan tujuan agar users selalu mengupdate tepat waktu karena apabila lebih dari 2 hari tanggal yg di inputnya tidak akan bisa, dan hanya bisa di input mundur dengan membuka password

semoga ada yg bersedia membant

Thanks
Milorambaldy

Back to top

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

5b.

Re: Membuat Validasi utk Input Tanggal atau date

Posted by: "bagus" bagus@kingjim.co.id   bagus4bls

Thu May 31, 2012 11:49 pm (PDT)

Utk Excell 2003

Block colom tempat pengisian tanggal
Data => Validation..

Setelah itu protect kolom tsb.

Bagus


Sent from my Taxiphone®

----- Original Message -----
From: milorambaldy
To: belajar-excel@yahoogroups.com
Sent: Friday, June 01, 2012 10:49 AM
Subject: [belajar-excel] Membuat Validasi utk Input Tanggal atau date

Dear Be-exceler

Mohon di bantu pencerahannya apabila saya ingin mencoba membuat aturan di Cell tertentu worksheet hanya bisa di input tanggal hari ini maksimalnya dan minimalnya 2 hari sebelumnya di dalam aturan 5 hari kerja saja.

dengan tujuan agar users selalu mengupdate tepat waktu karena apabila lebih dari 2 hari tanggal yg di inputnya tidak akan bisa, dan hanya bisa di input mundur dengan membuka password

semoga ada yg bersedia membant

Thanks
Milorambaldy

Back to top

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

6.

Akses database MS. Access dari Excel

Posted by: "Heru Safrudin" superheruu@gmail.com   superheruu

Thu May 31, 2012 11:13 pm (PDT)

Dear Pakar,

Misalkan saya bikin UI atau Form di excel dan mau membaca/mengakses database dari Ms. Access, Bisa nggak ya ?

Contoh terlampir

Terima kasih

Back to top

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

Recent Activity

•  7
  New Members

Visit Your Group

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! Groups

Small Business Group

Ask questions,

share experiences

Y! Groups blog

The place to go

to stay informed

on Groups news!

Need to Reply?

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

Create New Topic | Visit Your Group on the Web

Messages

---------------------------------------------------------------------
bergabung ke milis (subscribe), kirim mail kosong ke:
belajar-excel-subscribe@yahoogroups.com

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

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

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

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

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

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

READ MORE....