Create dynamic file reference

[Bernmeister]

(originally posted on StackOverflow)

Is is possible to create a file link using the value from another cell? For example, if I ultimately want a file link

'file:///Data/AAA.ods'#Sheet1.A10

(which pulls in data from another file/sheet/cell), but instead of typing out the whole thing (because I have lots of entries) I instead have a formula which would function as

'file:///Data/[A2].ods'#Sheet1.A10

where cell A2 contains the value AAA.

Thanks in advance,

Bernmeister.

[mariosv]

=INDIRECT("‘file:///Data/"&A2&".ods’#Sheet1.A10")

Be aware that INDIRECT() is a volatile function using it extensively can make a bit slower the spreadsheet.

[Bernmeister]

Thanks! Have started rolling into my spreadsheet - noticed it is slightly slower but that’s fine given the time saved for me when I have to continually edit.