There's no truly dynamic way of doing this using formulae alone I'm afraid - you would need to use some XLM/VBA type approach to generate the requisite listing(s) dynamically.
I would say that a basic UDF would probably suffice though you would be obliged to make it Volatile.
Assuming you the Function will reside within the same workbook as the data:
Called along the lines of:
Obviously the above is very basic but gives you one possible route - there are plenty of alternatives but the above is pretty straightforward I think.
Whichever way you go you're looking at a Volatile approach I'm afraid.
Another alternative would be to keep a Table detailing results by sheet and then use basic SUMIF calls to calculate final numbers based off the single contiguous table.
Bookmarks