Hello,
This is my first post but have enjoyed learning through this forum in the past.
I have data from multiple years all on separate tabs/worksheet in one file. I have this type of file for about 40 different markets. I'd like to create a record book and locate what is the MAX value of each file and have excel return the corresponding date of this Max value. For example the numbers data that I'd like to find the Max is in Column "B" on each worksheet. The Date is on column A of each worksheet. I can find this information if I do a =INDEX(A36:A206,MATCH(MAX(B36:B206),B36:B206,0)) on the same tab or if I am on a "Summary" Sheet and am searching across one worksheet. However, it is not working if I am applying this formula across multiple worksheets:
=INDEX('Market Name 2002':'Market Name 2010'!A36:A206,MATCH(MAX('Market Name 2002':'Market Name 2010'!B36:B206),'Market Name 2002':'Market Name 2010'!B36:B206,0))
This formula is returning back "#NAME?".
Other posts have mentioned I put the data into one worksheet. With 40 such files and approximately 10 worksheets per file, that would be time consumming so I am hoping there is a formula that I can apply to one worksheet for each file when I start my search.
Any help would be appreciated.
Thank You
BT
Bookmarks