In Google Sheets, I have a sheet that looks like this that I cannot modify:
My goal is to create, through formulas, a sheet that looks like this:
Where that "summary" sheet displays the value for each row labelled "TOTAL" as well as the date.
The "Total" column in the summary sheet I got with no problems using (where
Data is the name of the original sheet):
But I can't figure out the "Date" column, because the date is in a different row than the word "TOTAL" (it's above it).
So my question is: How can I extract that date above and to the left of each cell containing "TOTAL"? Ideally I'm trying to do it with an array formula (to keep the summary sheet simple), and with no blank rows in the resulting array (to keep the Date column lined up with the Total column without much hassle). I'd prefer not to write a script.
The only thing I've thought of to try so far is a bunch of convoluted formulas and temporary columns involving
address, and it was neither convenient nor correct. It was only a mess and a failure.
I've put together a sample document here (contains "Data" and "Summary" sheets).