Link to external data using cell in current sheet as reference for sheet name

[alekc]

Hello,

I’d like to link to a cell in an external calc document by using the content of a cell in the current document as reference for the sheet name in the external one.

Here’s what the direct link would look like:

file:///C:/Users/x/Documents/External.ods'#$'Sheet 1'.P35

I’d like ‘Sheet 1’ to be replaced by the value in cell A1 in the current sheet (not the external one).

I’ve tried using INDIRECT, but can’t get it to work the way I hoped it might.

=INDIRECT(""&'file:///C:/Users/x/Documents/External.ods'&#$A1&".P35")

I’d appreciate any pointers to the correct way of writing this reference.

Thank you!

[anon73440385]

Hello,

type the argument of your INDIRECT formula (="&'file:///C:/Users/x/Documents/External.ods'&#$A1&".P35") into a cell and you will immediately see why this won’t work. It does not create a valid reference, hence it won’t work in function INDIRECT(). And that’s my advice: Whenever you intend to use function INDIRECT() put the text into a cell, and only if it looks like a valid reference, put an INDIRECT(....) around the text

Having said this:

=INDIRECT("'file:///C:/Users/x/Documents/External.ods'#$'" & $A1 & "'.P35")

should work.

[alekc]

Perfect answer. Thank you!