Syntax for INDIRECT to access another sheet

asked 2019-11-04 23:51:44 +0100

Geemacmcm gravatar image

updated 2019-11-05 17:03:50 +0100

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?

edit retag flag offensive close merge delete

Comments

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.

Lupp gravatar imageLupp ( 2019-11-05 01:44:10 +0100 )edit

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.

m.a.riosv gravatar imagem.a.riosv ( 2019-11-05 09:33:31 +0100 )edit

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.

erAck gravatar imageerAck ( 2019-11-05 16:50:54 +0100 )edit

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.

Geemacmcm gravatar imageGeemacmcm ( 2019-11-06 22:38:28 +0100 )edit

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?

Geemacmcm gravatar imageGeemacmcm ( 2019-11-14 18:37:15 +0100 )edit