It is not INDIRECT that needs the books to be opened, but the function you use. SUMPRODUCT e.g. will work on closed books even when INDIRECT is used within the function.
As for your formula, perhaps VBA will do the trick, but that is above my pay grade

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post