Indirect returning #REF! error

Question

I am using Indirect to retrieve a value from an outside workbook.

My formula which works when I "hard code" the workbook name into it appears as follows:

=+C:\Customers[BCKH38283.xlsx]Sheet1'!$U$6

I don't want the workbook name to be hard coded into the formula so I created a formula that displays a string that looks EXACTLY like that formula and placed it in cell T16.  In cell G16, I then entered the formula:

=indirect(T16)

Excel is giving me a #REF! error.

What am I doing wrong?

Thanks for you help.

Tofer King

Answer 1

I am using Indirect to retrieve a value from an outside workbook.

My formula which works when I "hard code" the workbook name into it appears as follows:

=+C:\Customers[BCKH38283.xlsx]Sheet1'!$U$6

I don't want the workbook name to be hard coded into the formula so I created a formula that displays a string that looks EXACTLY like that formula and placed it in cell T16.  In cell G16, I then entered the formula:

=indirect(T16)

Excel is giving me a #REF! error.

What am I doing wrong?

Thanks for you help.

Tofer King

There is no nice neat elegant way to do this. If you use INDIRECT then the source file(s) must be open which is not desireable (especially if you're linking to many files).

Here's a klunky way...

A2:An = the file names to link to like: BCKH38283

Enter this formula EXACTLY AS SHOWNin B2:

="='C:\Customers["&A2&".xlsx]Sheet1'!$U$6"

Copy down as needed.

The result will be a TEXT STRING that looks just like a formula:

='C:\Customers[BCKH38283.xlsx]Sheet1'!$U$6

Now we're going to convert those TEXT strings into real formulas.

• Select the range of formulas in column B
• Right click>Copy
• Right click>Paste Special>Values>OK
• With the range of formulas in column B still selected
• Goto the Home tab
• In the Editing Group, click on the "binocular" icon
• Replace
• Find what: =
• Replace with: =
• Replace All
• Close the message box and userform

--

Biff

Microsoft Excel MVP

Answer 2

INDIRECT only works if the workbook referred to is open; it'll return #REF if that workbook is closed.