Indirect and accessing closed workbooks

[Inta]

Excel 2013 and still indirect wont work with closed books.


I am needing to reference a closed book and have previously used the following:
=IF(E38="","",VLOOKUP(E38,'S:\PB\[2014-04-01-InternalCatalog.xlsx]Catalog'!$A$2:$H$1000,2,FALSE))
Its clunky but it works.


Then I thought I would make it a bit better as updates were painful in having to change many formulas on a page
I modified the formula to:
=IF(E37="","",VLOOKUP(E37,N6&"!$A$2:$H$1000",2,FALSE))
Where N6 = 2014-04-01-InternalCatalog.xlsx]Catalog
this gave me #Value! error


I then again modified the formula to:
=IF(E35="","",VLOOKUP(E35,INDIRECT.EXT(N6&"!$A$2:$H$1000"),2,FALSE))
Using the Indirect function was not an option as it doesn't work on closed books so I installed the indirect.ext add in.
This gave me the #Ref! error


Thinking it may be the cell value causing the error, I substituted the cell for the actual value and tried this:
=IF(E36="","",VLOOKUP(E36,INDIRECT.EXT("'S:\PB\[2014-04-01-InternalCatalog.xlsx]Catalog'!$A$2:$H$1000"),2,FALSE))
This gave me the #N/A error


I am stumped as to what the error is or how to resolve it
All I wish to do is have the filename as a value in a cell so that when the book is updated, I only have to change one cell value not 50
Any and all suggestions will be most welcome.


Thanks,
Inta.

Note: I originally posted this as a reply to the indirect.ext located here
http://www.mrexcel.com/forum/excel-questions/84284-morefunc-indirect-ext.html
But it was a very old thread so I created the new post above as a new thread.

 

[RoryA]

I am needing to reference a closed book and have previously used the following:
=IF(E38="","",VLOOKUP(E38,'S:\PB\[2014-04-01-InternalCatalog.xlsx]Catalog'!$A$2:$H$1000,2,FALSE))
Its clunky but it works.


Then I thought I would make it a bit better as updates were painful in having to change many formulas on a page

Easier to just use the Edit Links dialog on the Data tab.

Thinking it may be the cell value causing the error, I substituted the cell for the actual value and tried this:
=IF(E36="","",VLOOKUP(E36,INDIRECT.EXT("'S:\PB\[2014-04-01-InternalCatalog.xlsx]Catalog'!$A$2:$H$1000"),2,FALSE))
This gave me the #N/A error

That would tend to imply the lookup value isn't found. Does the same formula work if you open the source workbook?