I have Worksheet 1 in Workbook 1.

In cell D2, I have a reference to cell $G$5 in Worksheet 2 in Workbook 2.

This works: when I open Worksheet 1, I am asked if I want to resolve
references to external Worksheets.

Or, if I alrady have Worksheet 2 open, the resolution is automatic.

HOWEVER...
In Worksheet 1 I want to put the name of the external Worksheet in column A.
Then in cell D2, I want to use a formula that will resolve to cell $G$5 in
whichever Worksheet is listed in cell A2.

I have been able to construct a formula in cell D2 of Worksheet 1 using the
"INDIRECT" function that looks up the name of the worksheet in column A and
retrieves the value from the named worksheet, but ONLY if the named worksheet
is already open.

How can I do this WITHOUT first opening the referenced spreadsheets?