Converting Text String into a Formula using INDIRECT

[Jtm_Preston]

Hello. I am trying to point a cell towards another cell in a different file, but make this dynamic using CONCATANATE to build up the file path & then INDIRECT to complete the task.

The formula I have is . . .

="=('"&N3&"/["&O3&"]Dashboard'!"&P1&M3&")"

This formulas produces the below text string in cell N6. I then use the formula =INDIRECT(N6) with the hope that it populated the value, as per the below text string, however I'm only getting the error #REF!

=('https://abc5.database.net/sites/24ABC01/bus_man/Finance/[2024 Finance Tracker (Team One).xlsx]Dashboard'!B6)

 

[jkpieterse]

The other file must be open for INDIRECT to work with an external reference.

 

[Jtm_Preston]

The other file must be open for INDIRECT to work with an external reference.

So what I'm trying to achieve is not possible. Thank you for you reply. Saves me wasting my time.

 

[jkpieterse]

It is probably better to use Data, Get Data, From File, From Folder to get information from multiple workbooks