Ask Your Question
0

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

asked 2021-01-13 13:29:52 +0100

alekc gravatar image

updated 2021-01-13 13:30:33 +0100

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!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2021-01-13 13:59:34 +0100

Opaque gravatar image

updated 2021-01-13 14:18:49 +0100

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.

edit flag offensive delete link more

Comments

Perfect answer. Thank you!

alekc gravatar imagealekc ( 2021-01-13 14:14:43 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-01-13 13:29:52 +0100

Seen: 11 times

Last updated: Jan 13