#1

I am looking for the syntax of referring to a cell in another worksheet using a Column letter and a calculated function.

I have a sheet called AdminLists containing a list of player names in Column B and their golf handicaps in Column C.
On another worksheet called Scores I can enter MATCH(tblScores[Player],tblPlayers,0)) which correctly returns the row number from the AdminLists sheets, matching the name entered on this row.

What I am trying to get is to recover the handicap for this player name and am using this formula:-
=INDIRECT("$AdminLists.C" & (MATCH(tblScores[Player],tblPlayers,0))) but it returns the #REF! error. Without the Quotes " " the error is #NAME?

Could someone point out what I need to change. Thanks

In fact using the formula builder and selecting a cell on another sheet also gives the #REF! error. Is there a fault with this function?

#2

The syntax, and also the semantics for INDIRECT is very simple. INDIRECT(AddressAsText) will return a valid reference if AddressAsText is a valid Address (either of a single cell or a cell range).
If you have reason to doubt the correctness of the address expression, compose it in steps from its parts in some helper cells. Every subexpression will then show clearly if it is valid and meets the needs.
If you want to use the name of a named range with INDIRECT, it must be treated as text because it otherwise would return content /results from the range.
The usage of ordinary addresses may become ticklish if you used inapt sheet names containing spaces and special characters. Sinply avoid this.

#3

You can delete INDIRECT from the formula to look if resulting text it is a correct address, if not open the formula wizard to verify MATCH result.

#4

Fwiw, the given example works absolutely fine for me once I set up the database ranges tblScores with a Player column and tblPlayers and filled some matching data.

You could attach a sample document where it doesn’t work to your question.

#5

Thanks for the comments and I have, in part, overcome the issue. I was working with a spreadsheet that was given to me, originally Excel and this is where I encountered the issue. I have since started a totally new Libreoffice Calc spreadsheet and have got the INDIRECT expression to work. So there must be something strange happening with the Excel workbook, as it is still not operating correctly with that file.

#6

Further Information

It appears that any Excel Spreadsheet causes a problem with the INDIRECT function, but only when referring to a cell on another sheet. It works fine for a cell on the same sheet, but as soon as you select a cell from another sheet you get the #REF error.

I even created a new sheet in the (previously Excel) spreadsheet and tried to refer to a cell on this, but this also failed.

Can anyone replicate this error and if so could it be fixed?

#7

Did anyone report this as a bug?

#8

I confirm the error reported by Geemacmcm:
If one creates an Excel workbook, e.g. in Excel 2010, and then imports it into LibreOffice, then create INDIRECT() to reference a cell in another sheet of the workbook, this causes a #REF! error.
If one starts with a fresh LibreOffice sheet then this bug does not occur.

LibreOffice (at least the LibreOfficeDev version 7.0.0.0 installed on my computer, possibly some earlier versions, too) provides a very deeply hidden solution to this problem:

  1. Go to menu Tools / Options…
  2. In the dialogue pane expand LibreOffice Calc.
  3. Select Formula
  4. On the right side, in the section “Detailed
    Calculation Settings”, click on
    “Custom (conversion of text to
    numbers and more):”. At the right of
    that click on the box: [ Details… ]. This opens a
    “Detailed Calculation Settings”
    pane.
  5. At the right of “Reference
    syntax for string reference:” select
    in the menu “Use formula syntax”
    Then [OK], [Apply], [OK]

This is how I got it to work after guidance from Miguel, see: https://bugs.documentfoundation.org/show_bug.cgi?id=132519#c7

#9

Importing a document originating from Excel should set the proper “Excel A1” option, see comment at https://bugs.documentfoundation.org/show_bug.cgi?id=132519#c8

Of course, if the string representation is "$AdminLists.C" then that will not work with the Excel A1 notation which would expect "$AdminLists!C" instead, but then again the document likely did not originate in Excel as there the "$AdminLists.C" would not had worked…

#10

I had the same problem. I opened an Excel 97 file using Libre Office Calc 7.6.4.1 (X86_64) [Windows 11].
Excel file has a lookup function to another worksheet (Spec worksheet) in the same file, e.g. in one cell the formula is
=VLOOKUP($B3,INDIRECT(“Spec!”&HLOOKUP(H$1,Spec!$A$2:$CZ$3,2,FALSE)),2,FALSE)
Note the exclamation mark used to refer to worksheet in Excel.
When opened with LibreOffice, the formula in the cell changed to
=VLOOKUP($B3,INDIRECT(“Spec!”&HLOOKUP(H$1,$Spec.$A$2:$CZ$3,2,FALSE())),2,FALSE())
The result is REF! error.
Note that the exclamation mark for the first part of the formula is still there while the exclamation mark for the second part of the formula has changed to a full-stop / period.
This I believe is the source of the error. So I changed the exclamation mark to a full-stop / period.

=VLOOKUP($B3,INDIRECT(“Spec.”&HLOOKUP(H$1,$Spec.$A$2:$CZ$3,2,FALSE())),2,FALSE())

The problem was solved. Hope this is useful if anybody else has a similar problem.

#11

How is set up
Menu/Tools/Options/LibreOffice Calc/Formula - Formula - Formula Syntax
or

image
image1300×814 147 KB

?