This may simply be a convoluted duplicate of an existing question, and I have been able to find one or two issues that match what I'm seeing here, but I am very curious to see if the cause is just user error on my part or if this is something I am just not aware of.
I have a worksheet titled Sheet1 with 999 rows. On this sheet in cells A3, B3, and C3 down are Year values (2021), Month numbers (1, 2, 3, etc.), and Quarter (Q1, Q2, etc.). In cell D, I have the following long but simple formula:
=SUMPRODUCT((MONTH(INDIRECT("TestSheet ("&C2&" "&A2&")!$C$3:$C$999"))=B2)*(YEAR(INDIRECT("TestSheet ("&C2&" "&A2&")!$C$3:$C$999"))=A2)*(INDIRECT("TestSheet ("&C2&" "&A2&")!$E$3:$E$999")))
What this formula does is sum the monetary values from sheets with variable names by month (INDIRECT is used to refer to sheets with the name "TestSheet (x y)", where x is the Quarter value and y is the year (ie: "TestSheet (Q1 2021)").
This formula functions as intended, and correctly sums the values on the variable sheet names when those sheets exist. The issue I'm running into is that it seems to take a length of time for Sheets to identify that a sheet exists, even when "On change and every minute" is selected under the Calculation tab under Spreadsheet Settings. From testing on a fresh workbook in which this formula is the only one present and there are no other formulas present that could be slowing down the recalculation, the cell values do not appear to update at all even an hour after the relevant cells have been updated with data.
Is there any indication based on what I have so far that stands out as a possible cause? Any suggestions would be appreciated.
