EXCEL HTPERLINK関数の参照先自動変換について

Question

sheet1のA1に下記を入力します。

=HYPERLINK("#sheet5!A1","etst")

シートタブから手動でsheet5をテストと変更します。
その際に=HYPERLINK("#sheet5!A1","etst")が
=HYPERLINK("#テスト!A1","etst")と自動で変わってほしいのですが何か方法はないのでしょうか？

ちなみに
sheet2のA1に下記を入力します。
=RIGHT(CELL("filename",sheet2!A2),LEN(CELL("filename",sheet2!A2))-FIND("]",CELL("filename",sheet2!A2)))
シート名がセルに表示されます。

シートタブから手動でsheet2をテストと変更します。
そしたら
=RIGHT(CELL("filename",sheet2!A2),LEN(CELL("filename",sheet2!A2))-FIND("]",CELL("filename",sheet2!A2)))
が
=RIGHT(CELL("filename",テスト!A2),LEN(CELL("filename",テスト!A2))-FIND("]",CELL("filename",テスト!A2)))
に変わっているのは確認しているのですが

何故、ハイパーリンクではならないのでしょうか？
また、どうしたら前者を自動更新できるようにできるでしょうか？

最終的には、

=HYPERLINK("#sheet2!A1",RIGHT(CELL("filename",sheet2!A2),LEN(CELL("filename",sheet2!A2))-FIND("]",CELL("filename",sheet2!A2))))

という風にしたいのです。
目次というシートがあってそこのセルに入力したいのです。

シート名が変更されても、表示テキストも参照先シートも動的に更新されるようにしたいのです。

VBAでシート名が変更されたらというイベントをやろうとしたのですが、自分にはレベルが高いです。
再計算のイベントも、すべてのセルを入力したら動いてしまうし。。。

じゃあ、とあるセルにCELL関数でシート名表示させておいてchangeイベントでやろうとしたのですが、そもそもVBAでやる必要があるのかという疑念にかられこの方法を思いたのですが、ハイパーリンクで参照先が自動更新されません。

ハイパーリンクの自動更新がクリアできればもう目標まであと少しなのです。
知恵をお貸しください。

Answer 1

はじめまして

なぜ、ハイパーリンクの参照先が変わらないのかには、回答ができませんが、
=CELL の参照先が自動で変わるのは、自分自身のファイルのタグを見に行ってくれているからではないかと思います。

お悩みの、ハイパーリンクの自動変更ですが、以下のようにしてみてはどうでしょうか？

=HYPERLINK(CELL("address",Sheet5!A1),"etst")

はたして、思われてる結果が出るかどうかは、保障できませんが、ご参考にどうぞ。