Pulling out specific information

[confused man]

Hello all,

I have a rather sizable spreadsheet that I am working on, it is keeping
track of my open and closed stock positions, as well as other things my
group wants me to keep track of. anyway I have stocks listed across the top
of the screen from column B-DR and I have dates listed in column A going
straight down.

Now I open a separate worksheet (within the same file) and I know how to
type =max(XXX:YYY) for each stock to get its highest price and =min(XXX:YYY)
to get its lowest price. But I want it to give me the highest price for each
stock along with the date that price occured. Is this possible??

Thanks so much,
Confused Man

 

[confused man]

Also if possible, can I have it set up so it constantly refers back to that
worksheet, so if for example tomorrow one of the stocks hits a new high or
low, it will automatically update? do will I need to continually put in the
equation?

Thanks again!
Confused Man

 

[Frank Kabel]

Hi
if on your second sheet column A contains the stock name try the
following in B1:
=INDEX('sheet1'!$A$1:$A$1000,MATCH(MAX(OFFSET('sheet1'1$A$1:$A$1000,0,M
ATCH(A1,$A$1:$DR$1,0))),OFFSET('sheet1'1$A$1:$A$1000,0,MATCH(A1,$A$1:$D
R$1,0)),0))