When trying to use the hyperlink function to set up a hyperlink to a cell in another sheet, I find the following works: hyperlink("#sheetname.A1",“text”).
I can’t find where this way of referencing a cell is documented, but it is the only thing that works for me. For example hyperlink($sheetname.A1, “text”) does not work.
Maybe find it in example given in help for Hyperlink Function, Spreadsheet Functions
Or use the function wizard
Because this is no direct part of Calc or Excel. If you read the doc you find
HYPERLINK("URL" [; CellValue])
and URL is the link your browser uses. The part after the # is to locate a place (anchor) inside the file. If the link starts with # you have a link inside your file.
.
See:
$SheetName.A1 is a reference to that cell and returns its content (or formula result). If that result is (e.g.) the string #Sheet105.ZZ24, and the cell addressed this way actually exists, your HYPERLINK() will point exactly there, and you can jump there activating it.