PDA

View Full Version : Solved: Understanding what used in this file



anandbohra
11-20-2007, 03:43 AM
Hi All
I am member of SMF Yahoo add in group there while surfing I got this file named as Get Yahoo Multiple History.xls
In this file there is one strange concept i come to know that after downloading prices the moment u double click on scrip heading u get the prices to be plotted in the chart next sheet
there that genius fellow used formula for series as
=SERIES(,,'Get Yahoo Multiple History double click chart.xls'!GraphRange1,1)

but strange thing is I am unable to locate range "GraphRange1", & how he update it runtime.
the file i am uploading is without password so pl have a look & tell me what logic he has used & when & where he update this range.
(not able to contact original author as found this file uploaded in files section of yahoo group SMF Addin )

so pl help me & also download it to get Yahoo historical prices at your Excel sheet immediately

Bob Phillips
11-20-2007, 04:25 AM
It's a hidden range, referring to

=OFFSET(Symbols!$A$3,0,Symbols!$F$1,COUNT(Symbols!$A:$A)+1,1)

anandbohra
11-20-2007, 05:23 AM
Thanks for the reply XLD
but where it is mentioned in the sheet coz I am unable to locate GraphRange1 in Name manager in Excel

Bob Phillips
11-20-2007, 05:54 AM
It is hidden as I said.

anandbohra
11-20-2007, 09:45 PM
hope I am not irritating you by asking this question again & again
but will u pl explain me how u got this hidden range?

if u r giving me the explanation logically ( as we generally do for dynamic range in charts) so how this fellow has done the same in excel where some one is using the range but not able to see it & also not able to alter it.

I check programmatically there is no hidden sheet ( Sheet3 that is only visible = false & not visible = Xlveryhidden) so how come he can hide the range.

as u said the range is hidden so can u pl explain me how to hide the range/name in excel

Bob Phillips
11-21-2007, 12:12 AM
It is the def8ined name thatis hidden not the sheet



ThisWorkbook.Names.Add Name:="myName", RefersTo:="=OFFSET(Symbols!$A$3,0,Symbols!$F$1,COUNT(Symbols!$A:$A)+1,1)"
ThisWorkbook.Names("myName").Visible = False


You can find them with a biy of simple VBA



Dim nme As Name
For Each nme In ThisWorkbook.Names
If Not nme.Visible Then
Debug.Print nme.Name
End If
Next nme

anandbohra
11-21-2007, 12:23 AM
:clap: Fantastic Reply :clap:
This hiding name is totally new to me.:dunno

Thank u Very Much

:bow: :bow: :bow: :bow: :bow: