INDIRECT External Reference must be open rule, Workaround.

[OX Driver]

In the help files I find the following rule:

“If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.”

This explains why the following formula is only working when the file the INDIRECT is looking for is open.

I have the following formula in one workbook.

=INDIRECT(CONCATENATE(" '\\Trc-3\SOBER_MRP\Varna Inventory\Part History\[",B18,"]Sheet1'!$J$100"),TRUE)

In B18 of that same workbook is a part number. Each part number has it own file named after it.
Does anyone know a better way to get an external Reference even when the file is closed?

Excel 2002, Windows 2000,

 

[awalle]

There is a way to get information from a closed file, without open it, but, unfortunately is using a Macro, suppose you want to gather information from a file at "C:\My Documents" named "a.xls", and you want the data over the cell A5 at Sheet1, to write this data at A1 cell in the current open file.

First create a macro. (I'am Thinking you know how, am I right?)
On Workbook_Open() use the following function

ActiveSheet.Range("A1:A1").Clear
With ActiveSheet.Range("A1:A1")
.FormulaArray = "='C:\My Documents\[a.xls]Sheet1'!A5"
.Value = .Value
End With


Now, everytime you open the file will update the information without having the file A.XLS (because of the sample) Open.


Hope This information can help you.
AWalle

 

[Richard Schollar]

Hi

You could also download the morefunc add-in (see Recommended Links and Downloads section) and then use the INDIRECT.EXT function (which works with closed external references).

Best regards

Richard

 

[OX Driver]

Update
I got it to work using MoreFunc’s INDIRECT.EXT.

I would still like to find a way to do it without the plugin but at least this works.

 

[OX Driver]

Thanks for the help fellas I didn’t get any email notifications of our posts so I didn’t see them till after my last post. I’ll try doing it without the plug-in using a macro.