Indirect Function Causing #Ref! Error

[Jak7217]

The following formula pulls data from the specified workbook, even when the workbook isn't open:
='C:\Users\Username\Documents\Report\[Test Workbook - 6-11-2019.xlsx]Sheet1'!A1

I added an indirect reference to the formula and it pulls data from the workbook... the only problem is said workbook now has to be open in order to avoid the #Ref ! error. Any ideas at how I can get this to pull automatically without needing to open workbooks?

=INDIRECT("'C:\Users\Username\Documents\Report\[Test Workbook - "&D4&".xlsx]Sheet1'!A1") (where D4 is the date 6-11-2019)

Thanks!

 

[DSCfromCFA]

G'day Jak,

From Excel Help:

[h=2]Example – Referencing a closed workbook with INDIRECT[/h]In the following example, an INDIRECT function is attempting to reference a workbook that’s closed, causing a #REF! error.

Solution
Open the referenced workbook. You'll encounter the same error if you reference a closed workbook with a dynamic array function.

So it looks like it can't be done.

shane