Lookup in other workbook goes #REF! when closed

[Leandrial]

Hi


im trying to do a lookup in a list found in other workbook.


The workbookname is a variable, in this example "Test3720.xlsm"

When i write the function with the workbookname "hardtyped", it work fine, with the searched woorkbook both opened and closed... the code goes like this

=VLOOKUP(C2;'C:\Test\Test3720.xlsm'!lNettoPriser;2;FALSE)

But when i try to replace the workbookname with a variable found in a cell (the "Test3720" part of the string), like this

=VLOOKUP(C2;INDIRECT("'C:\Test\" & C4 & ".xlsm'!lNettoPriser");2;FALSE)

(the value of cell C4 is ofc "Test3720"), then it all of a sudden works fine when the workbook "Test3720.xlsm" is open... but when closed the return of my function turns to #REF !



Why is that?? Any1 that knows? And anything i can do to make the function also work so it can search data in closed workbooks??


Best regards


Kasper

 

[Smitty]

INDIRECT doesn't work on closed workbooks, but you can use INDIRECT.EXT, which is part of the MOREFUNC add-in.

HTH

 

[Leandrial]

Thank you Smitty


Im going work around this with a change in the dataflow, so to speak... im gonna put the needed data in a spreadsheet that will need to be open anyway



Best regards


Kasper

 

[RoryA]

You really ought to know them by now, but please read the forum rules on cross-posting and follow them in future. Thank you.