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?