Lookup in other workbook goes #REF! when closed

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

(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

Thank you for the reply.

Fortunatly, i am sort of setting up the data-flow at the same time that im doing the needed spreadsheets... so i am going to go around this problem by changing the dataflow to fit the spreadsheets in stead


best regards

Kasper