How to format reference to external worksheet?

[montyj]

I am trying to reference an external workbook/sheet/cell but need to use (I think!) INDIRECT() to pull in the proper cell value.

Here is the formula that works using direct cell reference:

=‘file:///path/file.ods’#$‘Jan-08-2021’.N2

That references the correct file, sheet, and cell and returns the contents of cell N2 properly.

However, the sheet names, in text form, vary in column “A”, so I tried to use INDIRECT() for the sheet name:

=‘file:///path/file.ods’#$(INDIRECT($A3&.N2))

This does not work. I have tried different ways to format using INDIRECT, but get various errors depending on how I use the #, $, ", and ’ formatting characters. In this example I get:

ERR: 509

I believe it is just a formatting issue, but perhaps something more?

Thanks!

Monty

[anon73440385]

Hello,

function INDIRECT() requires a text string which is convertible to a valid reference (i.e. a text string looking like a valid reference).

1. Type =$A3&.N2 into a cell and check what that yields (probably Err:501 - invalid character). Type =$A3 & ".N2" into a cell and you should get a valid reference (as text)
2. INDIRECT() returns a reference, hence everything must be part of the argument.

Therefore use:

=INDIRECT("'file:///path/file.ods'#$'" & $A3 & "'.N2")

Ref.: LibreOffice help - Function INDIRECT

Hope that helps.

[montyj]

Opaque,

Yes, that did it! Thank you. I had tried something similar before but got the single & double quotes wrong. Works great!