Dear All:



First, allow me to express my deepest thanks for all of the help y'all have provided me over the years while working and figuring out my absolutely FAVORITE application - Microsoft Excel!! Hopefully I can add another notch on the "solved" column after this one..



Following this preface, you will find my original post [which was] under the subject "lookup stock symbol on worksheet and return summary data"



Basically, I may have asked my question the wrong way. What we are trying to do basically is create a form in excel or at least some type of function/macro [in Excel] that can query the other existing worksheet FULL of data. All I really need to know right now is how to return this data using whatever means necessary. I'd REALLY prefer to just use a text entry box like he and I discussed and enter for example "DELL" or "IBM" or "MSFT" as the ticker symbol, and have some fields on my main worksheet populated with numbers/data from the source worksheet. Of course I will want to make calculations on this data - I think I should be able to figure out how to incorporate them into the code after someone can enlighten me as to how to initially retrieve said data from the source worksheet.



Take a look at the correspondence below, or just see the original thread for reference. Biff was helping, but I think it may have just been a "right church..wrong pew" kindof thing. I have worked with pivotables, and I don't think that is the avenue I want to take. And like I said before, this is obviously (DEFINITELY) a solution better provided by a database, but unfortunately the ability to create or have someone create one at this point in time is not an option.



And hell, they have the "Control Toolbox" and "Forms Toolbox" options in Excel, so I figure they must be there for a reason.



If someone could please point me in the right direction, I'd be much obliged.



Thank you very much for your time and efforts in this matter.



Best Regards:



Greg Purnell

Jgpurnell13 - at - Verizon.net





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



HERE IS MY INITIAL POST:





My friend tried to stump me, but he didn't say I couldn't use you guys to find an answer (not to mention if I do find a solution I can bill his firm)...so here it is:

He has this summary data on one worksheet (but with MUCH more data columns/rows):

Symbol 9/14/2006 9/15/2006
ACG -2.22% -2.09%
ADF -9.79% -10.39%
ADRA -0.51% 0.03%
ADRD -0.41% 0.04%
ADRE -0.63% -0.03%
ADRU -0.56% 0.07%
ADX -14.39% -14.35%
AFB 7.38% 7.58%


and this is on the other worksheet:

ACG Enter symbol here

-2.24% Returns the average


Obviously, the formula he has in the lower box (where result populated is -2.24%) is: =AVERAGE(Data!3:3) (keep in mind I hid 100's of columns so avg is incorrect)

Very basic, I know. So I called him after he sent the email to find out why he's making a mountain out of a molehill. Obviously he could
summarize the data on this worksheet simply by calculating the averages on the source worksheet and just using the =Cell() formula to
return the results in a more readable format.


He does, however, (and as I suspected) want to bring in a lot more than just averages (SDs, variances, etc., etc.). Basically, he wants
to type in a symbol and have it return a bunch of data. He has it figured out one way with an array formula, but with over 1000 symbols,
and market data dating back to early 2006, it is obvious he has way too much data. He did, of course, tell me that the array formulae did
take forever, so on to plan B.


I have some ideas, but I have other stuff I need to be working on, so I just figured I'd come to the place where I've always been helped
with Excel (immensly!).


Basically, he just wants to be able to type in a symbol (a market ticker symbol, but not for regular stocks, they deal with closed-end
funds) - and have it return some summary data. As I said before, he's starting with "averages" but wants to apply the formula that I (well,
"we") come up with in order to return other types of data or variations thereof.

I did tell him right off the bat that this is an obvious and definite application for Microsoft Access or SQL Server (or any DB app.), to
which he agreed. But his company is just a 2 man operation, and he doesn't have the time to learn how to develop and implement one (a
db), nor the funds to have somebody else do so.

Also, as a side note - if you have any comments, every time I bring up how he should be doing this stuff on a database, he brings up "Filemaker Pro" - does
anyone professionally use that app.? I've never heard of anyone using it in the tech circles I am in. Is it a "Mom & Pop" type business app, or just a personal db for someone to arrange their CDs/DVDs, or do businesses actually use the software?

Thank you very much in advance all. You guys and gals have been absolutely WONDERFUL with helping me with Excel over the years!! I
really appreciate it.

Best Regards:

Greg Purnell
jgpurnell13 - at - verizon.net





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



BIFF's INITIAL REPLY:



Hmmm.....

Based on your sample data the average for ACG is -2.16%.

If each symbol is listed only once there is no need to use array
formulas.

With your sample data in the range A2:C9

G2 = ACG

This formula will get the average:

=AVERAGE(INDEX(B2:C9,MATCH(G2,A2:A9,0),))

You can use the same basic syntax for other calculations.

=STDEV(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MIN(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MAX(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
etc

Biff



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------





MY REPLY:



Thanks for the reply Biff. The only thing is that he wants to be able to
type the symbol into a cell and hit "enter" or click a button or something
and have all of this data returned.

Also, I shortened the sample data by several columns, and just put 2 columns
for examples' sake.

Any other ideas?

Thanks,

Greg





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------





Well crikey! "He" has to do some work!



Maybe a pivot table but I really can't help you with that 'cause I hate pivot tables!

Biff





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Why not use VLOOKUP?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Greg Purnell" wrote:

Dear All:



First, allow me to express my deepest thanks for all of the help y'all have provided me over the years while working and figuring out my absolutely FAVORITE application - Microsoft Excel!! Hopefully I can add another notch on the "solved" column after this one..



Following this preface, you will find my original post [which was] under the subject "lookup stock symbol on worksheet and return summary data"



Basically, I may have asked my question the wrong way. What we are trying to do basically is create a form in excel or at least some type of function/macro [in Excel] that can query the other existing worksheet FULL of data. All I really need to know right now is how to return this data using whatever means necessary. I'd REALLY prefer to just use a text entry box like he and I discussed and enter for example "DELL" or "IBM" or "MSFT" as the ticker symbol, and have some fields on my main worksheet populated with numbers/data from the source worksheet. Of course I will want to make calculations on this data - I think I should be able to figure out how to incorporate them into the code after someone can enlighten me as to how to initially retrieve said data from the source worksheet.



Take a look at the correspondence below, or just see the original thread for reference. Biff was helping, but I think it may have just been a "right church..wrong pew" kindof thing. I have worked with pivotables, and I don't think that is the avenue I want to take. And like I said before, this is obviously (DEFINITELY) a solution better provided by a database, but unfortunately the ability to create or have someone create one at this point in time is not an option.



And hell, they have the "Control Toolbox" and "Forms Toolbox" options in Excel, so I figure they must be there for a reason.



If someone could please point me in the right direction, I'd be much obliged.



Thank you very much for your time and efforts in this matter.



Best Regards:



Greg Purnell

Jgpurnell13 - at - Verizon.net





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



HERE IS MY INITIAL POST:





My friend tried to stump me, but he didn't say I couldn't use you guys to find an answer (not to mention if I do find a solution I can bill his firm)...so here it is:

He has this summary data on one worksheet (but with MUCH more data columns/rows):

Symbol 9/14/2006 9/15/2006
ACG -2.22% -2.09%
ADF -9.79% -10.39%
ADRA -0.51% 0.03%
ADRD -0.41% 0.04%
ADRE -0.63% -0.03%
ADRU -0.56% 0.07%
ADX -14.39% -14.35%
AFB 7.38% 7.58%


and this is on the other worksheet:

ACG Enter symbol here

-2.24% Returns the average


Obviously, the formula he has in the lower box (where result populated is -2.24%) is: =AVERAGE(Data!3:3) (keep in mind I hid 100's of columns so avg is incorrect)

Very basic, I know. So I called him after he sent the email to find out why he's making a mountain out of a molehill. Obviously he could
summarize the data on this worksheet simply by calculating the averages on the source worksheet and just using the =Cell() formula to
return the results in a more readable format.


He does, however, (and as I suspected) want to bring in a lot more than just averages (SDs, variances, etc., etc.). Basically, he wants
to type in a symbol and have it return a bunch of data. He has it figured out one way with an array formula, but with over 1000 symbols,
and market data dating back to early 2006, it is obvious he has way too much data. He did, of course, tell me that the array formulae did
take forever, so on to plan B.


I have some ideas, but I have other stuff I need to be working on, so I just figured I'd come to the place where I've always been helped
with Excel (immensly!).


Basically, he just wants to be able to type in a symbol (a market ticker symbol, but not for regular stocks, they deal with closed-end
funds) - and have it return some summary data. As I said before, he's starting with "averages" but wants to apply the formula that I (well,
"we") come up with in order to return other types of data or variations thereof.

I did tell him right off the bat that this is an obvious and definite application for Microsoft Access or SQL Server (or any DB app.), to
which he agreed. But his company is just a 2 man operation, and he doesn't have the time to learn how to develop and implement one (a
db), nor the funds to have somebody else do so.

Also, as a side note - if you have any comments, every time I bring up how he should be doing this stuff on a database, he brings up "Filemaker Pro" - does
anyone professionally use that app.? I've never heard of anyone using it in the tech circles I am in. Is it a "Mom & Pop" type business app, or just a personal db for someone to arrange their CDs/DVDs, or do businesses actually use the software?

Thank you very much in advance all. You guys and gals have been absolutely WONDERFUL with helping me with Excel over the years!! I
really appreciate it.

Best Regards:

Greg Purnell
jgpurnell13 - at - verizon.net





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



BIFF's INITIAL REPLY:



Hmmm.....

Based on your sample data the average for ACG is -2.16%.

If each symbol is listed only once there is no need to use array
formulas.

With your sample data in the range A2:C9

G2 = ACG

This formula will get the average:

=AVERAGE(INDEX(B2:C9,MATCH(G2,A2:A9,0),))

You can use the same basic syntax for other calculations.

=STDEV(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MIN(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MAX(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
etc

Biff



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------





MY REPLY:



Thanks for the reply Biff. The only thing is that he wants to be able to
type the symbol into a cell and hit "enter" or click a button or something
and have all of this data returned.

Also, I shortened the sample data by several columns, and just put 2 columns
for examples' sake.

Any other ideas?

Thanks,

Greg





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------





Well crikey! "He" has to do some work!



Maybe a pivot table but I really can't help you with that 'cause I hate pivot tables!

Biff





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks Dave...any chance you could elaborate? I'm certain the VLOOKUP would
have to be embedded into some IF and other various arguments. How would this
look if you can spare the time.

Many thanks.


--
TIA,
Greg


"Dave F" wrote:

Why not use VLOOKUP?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Greg Purnell" wrote:

Dear All:



First, allow me to express my deepest thanks for all of the help y'all have provided me over the years while working and figuring out my absolutely FAVORITE application - Microsoft Excel!! Hopefully I can add another notch on the "solved" column after this one..



Following this preface, you will find my original post [which was] under the subject "lookup stock symbol on worksheet and return summary data"



Basically, I may have asked my question the wrong way. What we are trying to do basically is create a form in excel or at least some type of function/macro [in Excel] that can query the other existing worksheet FULL of data. All I really need to know right now is how to return this data using whatever means necessary. I'd REALLY prefer to just use a text entry box like he and I discussed and enter for example "DELL" or "IBM" or "MSFT" as the ticker symbol, and have some fields on my main worksheet populated with numbers/data from the source worksheet. Of course I will want to make calculations on this data - I think I should be able to figure out how to incorporate them into the code after someone can enlighten me as to how to initially retrieve said data from the source worksheet.



Take a look at the correspondence below, or just see the original thread for reference. Biff was helping, but I think it may have just been a "right church..wrong pew" kindof thing. I have worked with pivotables, and I don't think that is the avenue I want to take. And like I said before, this is obviously (DEFINITELY) a solution better provided by a database, but unfortunately the ability to create or have someone create one at this point in time is not an option.



And hell, they have the "Control Toolbox" and "Forms Toolbox" options in Excel, so I figure they must be there for a reason.



If someone could please point me in the right direction, I'd be much obliged.



Thank you very much for your time and efforts in this matter.



Best Regards:



Greg Purnell

Jgpurnell13 - at - Verizon.net





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



HERE IS MY INITIAL POST:





My friend tried to stump me, but he didn't say I couldn't use you guys to find an answer (not to mention if I do find a solution I can bill his firm)...so here it is:

He has this summary data on one worksheet (but with MUCH more data columns/rows):

Symbol 9/14/2006 9/15/2006
ACG -2.22% -2.09%
ADF -9.79% -10.39%
ADRA -0.51% 0.03%
ADRD -0.41% 0.04%
ADRE -0.63% -0.03%
ADRU -0.56% 0.07%
ADX -14.39% -14.35%
AFB 7.38% 7.58%


and this is on the other worksheet:

ACG Enter symbol here

-2.24% Returns the average


Obviously, the formula he has in the lower box (where result populated is -2.24%) is: =AVERAGE(Data!3:3) (keep in mind I hid 100's of columns so avg is incorrect)

Very basic, I know. So I called him after he sent the email to find out why he's making a mountain out of a molehill. Obviously he could
summarize the data on this worksheet simply by calculating the averages on the source worksheet and just using the =Cell() formula to
return the results in a more readable format.


He does, however, (and as I suspected) want to bring in a lot more than just averages (SDs, variances, etc., etc.). Basically, he wants
to type in a symbol and have it return a bunch of data. He has it figured out one way with an array formula, but with over 1000 symbols,
and market data dating back to early 2006, it is obvious he has way too much data. He did, of course, tell me that the array formulae did
take forever, so on to plan B.


I have some ideas, but I have other stuff I need to be working on, so I just figured I'd come to the place where I've always been helped
with Excel (immensly!).


Basically, he just wants to be able to type in a symbol (a market ticker symbol, but not for regular stocks, they deal with closed-end
funds) - and have it return some summary data. As I said before, he's starting with "averages" but wants to apply the formula that I (well,
"we") come up with in order to return other types of data or variations thereof.

I did tell him right off the bat that this is an obvious and definite application for Microsoft Access or SQL Server (or any DB app.), to
which he agreed. But his company is just a 2 man operation, and he doesn't have the time to learn how to develop and implement one (a
db), nor the funds to have somebody else do so.

Also, as a side note - if you have any comments, every time I bring up how he should be doing this stuff on a database, he brings up "Filemaker Pro" - does
anyone professionally use that app.? I've never heard of anyone using it in the tech circles I am in. Is it a "Mom & Pop" type business app, or just a personal db for someone to arrange their CDs/DVDs, or do businesses actually use the software?

Thank you very much in advance all. You guys and gals have been absolutely WONDERFUL with helping me with Excel over the years!! I
really appreciate it.

Best Regards:

Greg Purnell
jgpurnell13 - at - verizon.net





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



BIFF's INITIAL REPLY:



Hmmm.....

Based on your sample data the average for ACG is -2.16%.

If each symbol is listed only once there is no need to use array
formulas.

With your sample data in the range A2:C9

G2 = ACG

This formula will get the average:

=AVERAGE(INDEX(B2:C9,MATCH(G2,A2:A9,0),))

You can use the same basic syntax for other calculations.

=STDEV(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MIN(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MAX(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
etc

Biff



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------





MY REPLY:



Thanks for the reply Biff. The only thing is that he wants to be able to
type the symbol into a cell and hit "enter" or click a button or something
and have all of this data returned.

Also, I shortened the sample data by several columns, and just put 2 columns
for examples' sake.

Any other ideas?

Thanks,

Greg





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------





Well crikey! "He" has to do some work!



Maybe a pivot table but I really can't help you with that 'cause I hate pivot tables!

Biff





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

See if this helps where f1 contains the desired symbol, col c contains the symbols, col d and beyond contains the numbers to average.
=AVERAGE(OFFSET(C1,MATCH(F1,C:C)-1,1):OFFSET(C1,MATCH(F1,C:C)-1,5))

--
Don Guillett
SalesAid Software

"Greg Purnell" wrote in message ...
Dear All:



First, allow me to express my deepest thanks for all of the help y'all have provided me over the years while working and figuring out my absolutely FAVORITE application - Microsoft Excel!! Hopefully I can add another notch on the "solved" column after this one..



Following this preface, you will find my original post [which was] under the subject "lookup stock symbol on worksheet and return summary data"



Basically, I may have asked my question the wrong way. What we are trying to do basically is create a form in excel or at least some type of function/macro [in Excel] that can query the other existing worksheet FULL of data. All I really need to know right now is how to return this data using whatever means necessary. I'd REALLY prefer to just use a text entry box like he and I discussed and enter for example "DELL" or "IBM" or "MSFT" as the ticker symbol, and have some fields on my main worksheet populated with numbers/data from the source worksheet. Of course I will want to make calculations on this data - I think I should be able to figure out how to incorporate them into the code after someone can enlighten me as to how to initially retrieve said data from the source worksheet.



Take a look at the correspondence below, or just see the original thread for reference. Biff was helping, but I think it may have just been a "right church..wrong pew" kindof thing. I have worked with pivotables, and I don't think that is the avenue I want to take. And like I said before, this is obviously (DEFINITELY) a solution better provided by a database, but unfortunately the ability to create or have someone create one at this point in time is not an option.



And hell, they have the "Control Toolbox" and "Forms Toolbox" options in Excel, so I figure they must be there for a reason.



If someone could please point me in the right direction, I'd be much obliged.



Thank you very much for your time and efforts in this matter.



Best Regards:



Greg Purnell

Jgpurnell13 - at - Verizon.net





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



HERE IS MY INITIAL POST:





My friend tried to stump me, but he didn't say I couldn't use you guys to find an answer (not to mention if I do find a solution I can bill his firm)...so here it is:

He has this summary data on one worksheet (but with MUCH more data columns/rows):

Symbol 9/14/2006 9/15/2006
ACG -2.22% -2.09%
ADF -9.79% -10.39%
ADRA -0.51% 0.03%
ADRD -0.41% 0.04%
ADRE -0.63% -0.03%
ADRU -0.56% 0.07%
ADX -14.39% -14.35%
AFB 7.38% 7.58%


and this is on the other worksheet:

ACG Enter symbol here

-2.24% Returns the average


Obviously, the formula he has in the lower box (where result populated is -2.24%) is: =AVERAGE(Data!3:3) (keep in mind I hid 100's of columns so avg is incorrect)

Very basic, I know. So I called him after he sent the email to find out why he's making a mountain out of a molehill. Obviously he could
summarize the data on this worksheet simply by calculating the averages on the source worksheet and just using the =Cell() formula to
return the results in a more readable format.


He does, however, (and as I suspected) want to bring in a lot more than just averages (SDs, variances, etc., etc.). Basically, he wants
to type in a symbol and have it return a bunch of data. He has it figured out one way with an array formula, but with over 1000 symbols,
and market data dating back to early 2006, it is obvious he has way too much data. He did, of course, tell me that the array formulae did
take forever, so on to plan B.


I have some ideas, but I have other stuff I need to be working on, so I just figured I'd come to the place where I've always been helped
with Excel (immensly!).


Basically, he just wants to be able to type in a symbol (a market ticker symbol, but not for regular stocks, they deal with closed-end
funds) - and have it return some summary data. As I said before, he's starting with "averages" but wants to apply the formula that I (well,
"we") come up with in order to return other types of data or variations thereof.

I did tell him right off the bat that this is an obvious and definite application for Microsoft Access or SQL Server (or any DB app.), to
which he agreed. But his company is just a 2 man operation, and he doesn't have the time to learn how to develop and implement one (a
db), nor the funds to have somebody else do so.

Also, as a side note - if you have any comments, every time I bring up how he should be doing this stuff on a database, he brings up "Filemaker Pro" - does
anyone professionally use that app.? I've never heard of anyone using it in the tech circles I am in. Is it a "Mom & Pop" type business app, or just a personal db for someone to arrange their CDs/DVDs, or do businesses actually use the software?

Thank you very much in advance all. You guys and gals have been absolutely WONDERFUL with helping me with Excel over the years!! I
really appreciate it.

Best Regards:

Greg Purnell
jgpurnell13 - at - verizon.net





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



BIFF's INITIAL REPLY:



Hmmm.....

Based on your sample data the average for ACG is -2.16%.

If each symbol is listed only once there is no need to use array
formulas.

With your sample data in the range A2:C9

G2 = ACG

This formula will get the average:

=AVERAGE(INDEX(B2:C9,MATCH(G2,A2:A9,0),))

You can use the same basic syntax for other calculations.

=STDEV(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MIN(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MAX(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
etc

Biff



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------





MY REPLY:



Thanks for the reply Biff. The only thing is that he wants to be able to
type the symbol into a cell and hit "enter" or click a button or something
and have all of this data returned.

Also, I shortened the sample data by several columns, and just put 2 columns
for examples' sake.

Any other ideas?

Thanks,

Greg





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------





Well crikey! "He" has to do some work!



Maybe a pivot table but I really can't help you with that 'cause I hate pivot tables!

Biff





------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------