Long time viewer, first time poster here and I am trying to make a rolling 3 month using an averageifs formula. I want this formula to be dynamic within multiple worksheets.
Currently, I have 3 data tabs all built and formatted the same. In column C (C11-C20) I have the name of different sports. In D8:O8 I have the dates. I also have a summary tab that captures the basic detail. The way I have it set up, you can enter a date in C7 and a state name (name of the worksheet/tab) in C8 and the formula will pull the 3 month rolling average. The formula I currently have that seems to be pulling correctly is:
=IFERROR(AVERAGEIFS(INDIRECT("'"&$C$8&"'!"&"$D"&ROW($D11)&":$AS"&ROW($D11)),INDIRECT("'"&$C$8&"'!"&"$D"&ROW($D$8)&":$AS"&ROW($D$8)),">="&EDATE($C$7,-2),INDIRECT("'"&$C$8&"'!"&"$D"&ROW($D$8)&":$AS"&ROW($D$8)),"<="&$C$7),"")
I am trying to make my formula more dynamic - the averageifs formula right now is calculating based on what is in the corresponding row (i.e. basketball is row 11 on the summary tab and the data tabs). Since I am making a summary tab - I dont need to capture every single metric on my data tabs (i.e. what if basketball was in row 20). I believe I need to incorporate some sort of match function within my first indirect, but I havent had any luck.
I realize the easiest way to do this is map the corresponding row to my formula, however please note that this is just dummy data. The file I am dealing with has 2000 rows on the data tab and I cant map each formula individually.
I have attached a copy. Thanks in advance
[ATTACH=CONFIG]58380[/ATTACH]