How can I do this?

[Confused Man]

Hello all,

I just want to start by thanking everyone on this board for all the
wonderful suggestions you give... you guys are life savers!

OK, now to my question... I have a sheet that looks like this...

A B C D E
1 Date Stock #1 Stock #2 Stock #3 ..... Stock #20
2 2/1/05 $$ $$ $$ $$
3 2/2/05 $$ $$ $$ $$
4 2/3/05 $$ $$ $$ $$
etc........

I want to set up a sheet inside this workbook that tells me what the highest
price for stock 1,2,3...20 was AND on what date it occured. Is this
possible? For simplicity's sake lets call the sheet with all the stocks on
it Sheet1 and the sheet I am creating Sheet2.

Thank you all so much,
Confused Man

 

[Bernard Liengme]

The max value: =MAX(Sheet1!B:B)
The date of the max value:
=INDEX(Sheet1!A:A,MATCH(MAX(Sheet1!B:B),Sheet1!B:B))
best wishes

 

[Confused Man]

Bernard,

Thanks for reply... a couple of follow ups if you don't mind... if I'm on
Sheet2 putting these formulas in; I would put the stock in column A, Date in
column B. and Max value in column C... all the way down for all my stocks
right?

but for the Index formula A:A and B:B do I need to put any numbers next to
the column, or just A:A and B:B?

Thanks so much,
Confused Man

 

[Bernard Liengme]

Just type (or copy & paste) the formula as posted. Using something like A:A
means A1:A65536. If your have a list of known length you could use A1:A200

 

[Confused Man]

Bernard,

I'd like to thank you for all your help, I took your suggestion and simply
copy and pasted the =Index formula into my Sheet2... unfortunately everytime
I do I only get the date for the last data entered... for example today is
8/9/2005, whenever I use this formual that is the only date I get... if I
were to enter a value for tomorrow that =index date would change to tomorrows
date... but that is not what I want.

Any thoughts?

Thanks so much,
Confused Man

 

[Confused Man]

Bernard,

I hope I didn't confuse you in my last question, if you need any
clarification I'll be glad to try again. I'm desparate please help!

Thanks again,
Confused Man