What syntax to use for referencing a table in an external CALC workbook?

[gtb]

Windows 10

LibreOffice Version: 24.8.4.2 (X86_64) / LibreOffice Community
Build ID: bb3cfa12c7b1bf994ecc5649a80400d06cd71002
CPU threads: 12; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

I want to look up an entry in a table that’s located in another workbook using VLOOKUP. The following two spreadsheets illustrate the problem; screenshots appended below:

Main.ods contains the VLOOKUP formula
Reference.ods contains the table that I want to use for VLOOKUP (as its second argument)

Note: Column B gives the formula results; the text of those formulas is shown in Column C.

Question: Is there a way to do this with named ranges?

Ideally, I’d want to supply a named range as the array argument to VLOOKUP, rather than have to define the table explicitly with cell addresses. (e.g., for simplifying maintenance when there are multiple instances of references to that same table elsewhere in the main spreadsheet, when the dimensions of the reference table should change, etc)

Rows 1-2 in the screenshot of Main.ods show two named ranges: “extReferenceTable1” uses the absolute cell addresses to define the table. “extReferenceTable2” defines the same table, but instead uses a named range for it (defined in Reference.ods). The latter would be the ideal solution…

I assume that the argument for VLOOKUP would need to put the named ranges within the INDIRECT function, but I’ve tried it both ways.

Only one syntax worked at all—entering the full range specification explicitly (external file + sheet name + absolute range reference). Even then, the formula in the main table won’t update (e.g. with F9) if entries in the external table change*; or even by closing & reopening the main spreadshet and allow updates of external links. (*Hence Main.ods shows 1252 rather than 62342.)

Finally, with cell C16 selected, I tried the menu option: Sheet / External Links… and selected that same external table. Click “OK” and suddenly the whole table has been pasted into my document??? I also tried typing the formula into cell C16:

=VLOOKUP(A12,

and then try the menu option Sheet / External Links…, etc When I click “OK”, nothing happens—the formula is still sitting there waiting for the second argument.

So what is the point of Sheet / External Links? I want to link externally, not grab a copy.

[Zizi64]

Please upload real .ods files here.

[gtb]

Main.ods (11.7 KB)
Reference.ods (11.9 KB)

[Villeroy]

Main.ods (17.6 KB)

[gtb]

It seems my issue was with the argument to the INDIRECT function–apparently what I thought were text strings (Cells C1 and C2 in my uploaded Main.ods) were not… It was only by parsing the external reference specification and then assembling the components with text concatenation (per @Villeroy) did the reference resolve properly. It’s still not clear to me why my cells C1 and C2 failed to be recognized as “address as text”, but I’m more than satisfied to have a workaround that solves the problem!

Thank you!