I agree with
@arthurbr...I would use either Power Query or VBA to pull all of this information into a summary sheet. If you want to consider a more labor intensive approach, you could consider something like what is shown below. I note that your summary block for any given date sheet draws from one of three column arrays (shown in red cells), so I've created a template of sorts that reorganizes your output to more efficiently utilize these column arrays. The row numbers that need to be accessed are shown in yellow, green, and orange. The sheet that needs to be accessed in shown as a single input in blue (cell B3), which is filled down column B. Then a volatile INDIRECT(ADDRESS formula constructs the appropriate sheet and cell address coordinates to pull in information from the desired target cell. There are three versions of this INDIRECT(ADDRESS formula since each has to reference the red column array that applies to its sub-block. Finally a larger contiguous range that contains the three sub-blocks of interest is transformed using the CHOOSECOLS function to keep only the desired columns and reorder them (see the pink output section).
Depending on how often you need to perform this summary operation, you could either use the existing template and extract any day of interest, or repeat the template block many times down the page to build day-by-day outputs that would align with each other in the pink output portion of the sheet.
For reference, a small snipped of my test worksheet named "1" looks like this...although I copied the formula that returns the internal Excel sheet name and cell address throughout the range A1:BG13.
And the summary sheet template looks like this:
If you are unfamiliar with the XL2BB add-in used on this Board, you can read more about it at the link in my signature block. A nice feature of the add-in is that you can click on the clipboard icon in the upper left of a posted snippet, which copies the snippet contents and formulas to your clipboard. Then navigate to your worksheet, select the same cell as that shown in the snippet (cell A1 in this case), and then paste. Using this method, you should be able to quickly construct the same example in your workbook.
