Indirect Function Causing #Ref! Error

Jak7217

New Member
Joined
Jan 11, 2019
Messages
13
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

Board Regular
Joined
Feb 27, 2018
Messages
132
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,085,019
Messages
5,381,244
Members
401,724
Latest member
Soly_Man

Some videos you may like

This Week's Hot Topics

Top