Indirect.ext

[billythefish71]

I’m trying to use the INDIRECT.EXT function to return the contents of a cell within a number of external closed worksheets and I’m only currently to return the correct cell contents when the external worksheets are open.

Currently:
Cell A1 holds a static path that will be constant
Cell B1 holds the year half (e.g. 2011-1) which needs to be changed every six months
Cell C1 holds a salesperson name which relates to a spreadsheet file and workbook name
Cell D1 holds the appropriate cell to reference

So my current test cell contents are:

A1: ‘D:\Commissions\
B1: 2011-1
C1: \[Fredd Bloggs.xls]Salesperson’!
D1: $B$5
F1: =INDIRECT.EXT(A1&B1&C1&D1)

When the file D:\Commissions\2011-1\Fredd Bloggs.xls is open I get a figure returned in cell F1.

When the file is closed cell F1 returns #REF!

Can anyone offer suggestions?

 

[GlennUK]

Can you try it to a folder location that does not contain "-"?

 

[RoryA]

Does it help if you double the quote at the start of cell A1 - i.e. use two single quotes, not one double quote.?

 

[sijpie]

If you want to use INDIRECT.EXT, make sue that the path reads correct. Just type in a cell =A1&B1&C1&D1
Does the path read ok? Does it start with the ' ? (else you need to add another to the start of A1)

for an alternative method:
from http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/

If a workbook that the INDIRECT function is indirectly referencing is closed, the function returns a #REF! error.
To avoid this error you can imbed the indirect function inside the hyper link function. If you need to perform a sum you can insert it into the value function. The cell can be reformatted to eliminate the underline and font color.
========================================
When an Excel workbook is closed, it cannot be referenced by the INDIRECT function, however as Greg states this can be achieved via an acrimonious HYPERLINK function without having to resort to VBA/coding of any kind.
To give an example;-
If the data you wish to reference/lookup is in cell A2 & the INDIRECT range/table reference is in cell B2, then the following formula will correctly return the data in the 2nd column of the lookup table.
=HYPERLINK(VLOOKUP($A2,INDIRECT($B$2),2,0))
The formatting of the cells containing this function can then be changed to get rid of the HYPERLINK formatting (blue font colour/underlining etc.).