The problem is:

I cannot have a dynamic links to other sheets in the same workbook. With
"Dynamic" I mean that I want the link to work even when I change name on the
Sheet or even on the workbook.

My workbook is called HL.xls.

=HYPERLINK("[HL.xls]Sheet2!$A$1";"ClickHere")
This link works to get to Sheet2. But if Sheet2 is renamed then the link
doesn't work anymore.

=HYPERLINK(CONCATENATE("""";CONCATENATE("[";MID(CELL("filename";Sheet2!$A$1);FIND("[";CELL("filename";Sheet2!$A$1))+1;256));"!$A$1";"" "");"ClickHere")
This has a dynamic destination (i.e. is changed when sheet or workbook is
renamed), but now the link doesn't work anymore.

I have also tried in two steps so that the middle section is presented in
cell A10 and then have the following formula:
=HYPERLINK(A10;"ClickHere")

Can someone figure out why it doesn't work?