auto filled cross reference

[RobLET]

Hello,

i am searching the internet to solve my problem but can't find what i am doing wrong.

i want to extract a value from another excelfile of which the name of the file is determined by a value in my excell sheet.
I found the code, but the value is only updating when the other file is open, other wise displays error " #REF! "

i tried following codes

=INDIRECT("'"&$P$1&"\[KW"&D7&".xlsx]Weekfiche'!$L$44")
P1 is the URL to the main map where the other excel files are, D7 is a number ( the files have name KW1,KW2,KW3,...),
Weekfiche is the tab.
L44 is the cell of the value that i need.

=INDIRECT(O8) with O8 : ="'"&$P$1&"\[KW"&D8&".xlsx]Weekfiche'!$L$44"

the strange thing: the next fomula is working ( but not automaticly filled in)
i want it automaticly because D7 is also automaticly generated

='C:\test\[KW1.xlsx]Weekfiche'!$L$44

C:\test: the url (P1)
1: D7 has value one on this moment.

i checked updating and all that sort but can't find the solution


i have the feeling this is a bug and i can't change anything about it.