Found most of the cause of the problem... Replace the formula in File Library D2 with:
=IFERROR(INDEX(Register!E:E,AGGREGATE(15,6,ROW(Register!$T$10:$T$200)/(Register!$T$10:$T$200=$D$1),ROWS(D$2:D2))),"")
adjust the bits in red, but don't go mad... and in F2:
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$26)/($B$2:$B$26=$F$1),ROWS(F$2:F2))),"")
it's still a bit laggy, but vastly improved. the reports sheet is PW protected, so I couldn't troublesheet it.
I'm more than happy to take a look at it, if you wish. But I'd need access to it!!
IMHO, you need to look at Dynamic Named Ranges as a way of improving formula performance. These enable Excel to auto-adjust ranges to suit data length. Also, move away from array formulae where possible. ABSOLUTELY away from ones referencing whole columns.
And to repeat, NEVER use SP with whole columns. it evaluates as an array formula, all 1,048,576 rows, about 5 times for each cell that the formula is in. Also, you have NOW() in your sheet. This is volatile. Every time anything changes, it recalculates. Use whole columns with care and volatile functions as if they were dynamite... with caution.
file returned. Filter at the yellow cells in J. Much faster... but should be much faster still.
Bookmarks