INDIRECT External Reference must be open rule, Workaround.

OX Driver

New Member
Joined
May 7, 2003
Messages
41
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,
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

awalle

Board Regular
Joined
Jul 12, 2006
Messages
56
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
May 7, 2003
Messages
41
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.
 

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top