INDIRECT always returns an error on reference to a closed workbook.

There are workarounds involving macros, where VBA will open the file or use other somewhat more esoteric techniques to retrieve data from a closed workbook. One such workaround is to use something like INDIRECT.EXT, a plug-in with a VBA user-defined function. Another workaround is using VBA to build the cell reference rather than using INDIRECT; if the value in A2 changes, the code creates a hard-coded cell reference.

I can help out with a VBA solution if you would like to go that direction.