Hi,
This is my first time posting here. I learned about this forum from http://reddit.com/r/excel.
I've attached a simplified version of my workbook as an example. There are two sheets: "Dailies" and "Calc". On the "Dailies" sheet I enter the date and some data values every MondayFriday. I've included 2 "Value" columns as an example.
I want the "Calc" sheet to automatically update its calculations based on the latest entry on the "Dailies" sheet.
One of my calculations is the "52Week High" which calculates the highest data value in the last year's worth of data, looking back from the most recent data entry. My problem is calculating the corresponding date that the data value peaked on.
I have been using INDEXMATCH functions for these calculations. However, when I try to calculate the corresponding date of the "52week high/low" values, the function returns #REF!.
I'm assuming this is because I can't nest INDEX functions, but I don't actually know.
Please let me know if this question is clear and/or you need more information. Any insight would be greatly appreciated.
Thanks!
edit: I've edited the post above to make the issue clearer.
This is my first time posting here. I learned about this forum from http://reddit.com/r/excel.
I've attached a simplified version of my workbook as an example. There are two sheets: "Dailies" and "Calc". On the "Dailies" sheet I enter the date and some data values every MondayFriday. I've included 2 "Value" columns as an example.
I want the "Calc" sheet to automatically update its calculations based on the latest entry on the "Dailies" sheet.
One of my calculations is the "52Week High" which calculates the highest data value in the last year's worth of data, looking back from the most recent data entry. My problem is calculating the corresponding date that the data value peaked on.
I have been using INDEXMATCH functions for these calculations. However, when I try to calculate the corresponding date of the "52week high/low" values, the function returns #REF!.
I'm assuming this is because I can't nest INDEX functions, but I don't actually know.
Please let me know if this question is clear and/or you need more information. Any insight would be greatly appreciated.
Thanks!
edit: I've edited the post above to make the issue clearer.
Attachments

73.3 KB Views: 11
Last edited: