H
Harold Good
Hi, here's a challenge beyond the capacity of my thinker!
I have the formulas below in the respective cells:
In cell E3 is: =INDEX(E$101:E$981,MATCH(I3,I$101:I$981,),)
In cell E4 is: =INDEX(E$101:E$981,MATCH(I4,I$101:I$981,),)
These continue in Col E down to Row 96. Col E is where the final calculations reside that I'm interested in.
These formulas work fine, but I'd like to add some functionality, and this is where it gets complicated for me.
In E2 is a date that I enter. From I2:Z2 are also dates one week apart, with the weekly data beneath these headings. So each column represents a weeks worth of data, headed by the Friday's date in Row 2 above each column. Column I is the most recent data, and I add a new column each week, inserting a new column so the most recent data is always in Col I.
The formulas above work fine as long at I'm only focused on Col I (the most recent data). But I would like to add the ability to the above formulas so that I can change the date in E2, and the column in focus will change instead from Col I, to the column with a date heading that matches E2.
E.g. If I change E2 to 23 Jan, I would like the column in focus to change from I (as per the present formula) and headed by 13 Feb, to Col L (headed by 23 Jan in L2). Changing this column of focus, will therefore change the final results that I want to see in Col E. I can easily do this manually by just changing the I to L in the above formulas, but would be nice if I can have it do it automatically, based on the date I type into E2.
I have tried to focus on ways to combine another Match, along with Substitute and Address, or Concatenate, to look for ways to change the I to L in this part of the forumula MATCH(I3,I$101:I$981,),) but I'm not getting anywhere.
Any thoughts on how to proceed with this would be much appreciated.
Thanks!
Harold
I have the formulas below in the respective cells:
In cell E3 is: =INDEX(E$101:E$981,MATCH(I3,I$101:I$981,),)
In cell E4 is: =INDEX(E$101:E$981,MATCH(I4,I$101:I$981,),)
These continue in Col E down to Row 96. Col E is where the final calculations reside that I'm interested in.
These formulas work fine, but I'd like to add some functionality, and this is where it gets complicated for me.
In E2 is a date that I enter. From I2:Z2 are also dates one week apart, with the weekly data beneath these headings. So each column represents a weeks worth of data, headed by the Friday's date in Row 2 above each column. Column I is the most recent data, and I add a new column each week, inserting a new column so the most recent data is always in Col I.
The formulas above work fine as long at I'm only focused on Col I (the most recent data). But I would like to add the ability to the above formulas so that I can change the date in E2, and the column in focus will change instead from Col I, to the column with a date heading that matches E2.
E.g. If I change E2 to 23 Jan, I would like the column in focus to change from I (as per the present formula) and headed by 13 Feb, to Col L (headed by 23 Jan in L2). Changing this column of focus, will therefore change the final results that I want to see in Col E. I can easily do this manually by just changing the I to L in the above formulas, but would be nice if I can have it do it automatically, based on the date I type into E2.
I have tried to focus on ways to combine another Match, along with Substitute and Address, or Concatenate, to look for ways to change the I to L in this part of the forumula MATCH(I3,I$101:I$981,),) but I'm not getting anywhere.
Any thoughts on how to proceed with this would be much appreciated.
Thanks!
Harold
4,MATCH(G1,A1