構文

=HYPERLINK(URL, [リンクラベル])

• HYPERLINK - ドキュメント エディタ ヘルプ(Google 公式)
  https://support.google.com/docs/answer/3093313?hl=ja

外部URLに飛ばす

=HYPERLINK("https://www.google.com","Link")

新しいウィンドウで「https://google.com 」が開かれます。

シートに飛ばす

=HYPERLINK("#gid=1234567890","Link")

gidに指定したシートに移動します。
gidに関しては対象シートを開いたときのURLのところに記載されています。

シートのセルに飛ばす

=HYPERLINK("#gid=1234567890&range=C8","Link")

gidで指定したシート内のrangeで指定したセルに移動します。

シートのセルに飛ばし、範囲選択されている状態にする

=HYPERLINK("#gid=1234567890&range=C8:E10","Link")

gidで指定したシート内のrangeで指定した範囲の左上の位置に移動し、指定した範囲を選択状態にします。
上記の場合C8のところに飛び、C8:E10が選択されている状態になります。

シートの値が記載されているところに飛ばす

=HYPERLINK("#gid=1234567890&range=B"&match("検索値",'シート名'!B:B,0),"Link")

少し応用、gidで指定したシートの特定列から、"検索値"を探し出し、その記述が書かれているところに飛ばす。
上記の場合、対象シートのB列から検索値を探し出しそこに飛ぶ。

なお、「'シート名'」のシートはgidで指定しているシートのシート名を入れてやる必要がある。
gidとシート名が混在するためちょっと面倒。
※次項に記載している方法を使えば多少スマートになる。

gidではなくシート名でリンクを作りたい場合

「メニュー＞ツール＞スクリプトエディタ」でスクリプトエディタを開きに以下の記述を追加したうえで

function getSheetId(sheet_name) {
    var sheet_id = SpreadsheetApp.getActive().getSheetByName(sheet_name).getSheetId();
    return sheet_id;
}

以下のようにスプレッドシートに関数を記述すれば可能になります。

=HYPERLINK("#gid="&getSheetId("シート名"),"Link")

補足

他、良さそうなものがあれば追記していきます。