Calculating VWAP via multi sheet indexes

First, let's fix the 200 slope. You might want to apply this technique to all of them in case there is less data than needed for that "size".

Original P5 formula:

=AVERAGE(OFFSET(INDEX(INDIRECT("'" & $A5 & "'!E:E"), MATCH($A$1, INDIRECT("'" & $A5 & "'!A:A"), 0)-199), , ,$P$2, ))

Now let's replace that 199 with a formula that drops the number down when needed:

MIN(MATCH($A$1, INDIRECT("'" & $A5 & "'!A:A"), 0)-1, 199)

=AVERAGE(OFFSET(INDEX(INDIRECT("'" & $A5 & "'!E:E"), MATCH($A$1, INDIRECT("'" & $A5 & "'!A:A"), 0)-MIN(MATCH($A$1, INDIRECT("'" & $A5 & "'!A:A"), 0)-1, 199)), , ,$P$2, ))

Some sheets start on row2, others on row3. Need to all be the same.

The actual question on this thread stumps me for now.

You could use something like (array formula !)

Please Login or Register  to view this content.

I was trying that initially but getting lost in the INDIRECT() addition for the sheet names and the dynamic adjustment of the rows to only include the year denoted in cell A1.

Guys, thanks for the additional comments!

Maybe it will help if I attached my entire spreadsheet to show the full picture. I am not opposed to adding some hard coded inputs to the Parameters sheet seen to help with calculating the VWAP. I will already be updating the dates everyday anyway.

When combining my old spreadsheet with this new one and the query that brings in the stock data, however, I am running into a new problem. After hitting Get Bulk Quotes on the Parameters sheet, my price and returns formulas lose their reference. This is because the query is deleting its old sheet and then recreating a new one. Despite it having the same name, it doesn't pick it up. The Simple Moving Average section formula still works. I think it is a difference between the VLOOKUP formula that I used and the more advanced ones that you generously helped me with. Any ideas on how to fix this?

Maybe after that, I can tackle the remaining cells on the right.

Thanks again for all of the help!

Cell C5 previously read

Please Login or Register  to view this content.

Cell D5 previously read

Please Login or Register  to view this content.

Cell E5 previously read

Please Login or Register  to view this content.

Cell F5 previously read

Please Login or Register  to view this content.

Cell G5 previously read

Please Login or Register  to view this content.

Cell H5 previously read

Please Login or Register  to view this content.

Multiple Stock Quote Downloader edit.xlsm