Hyperlink formula with dynamic sheet references

[William_Bockstaele]

Hello,
I hope someone has experience with my next issue.
To put your into my problem I shortly describe the situation I`m having:
I have a multisheet xls file, most sheets are named as a company. I have foreseen a macro controlled by a button to go from such a sheet to an analysis sheet. This sheet then shows what effect there are for the selected account on the source sheet.
Works fine.
But now they want a button to go back from the "Analysis" sheet to the original sheet (one of the company sheets) they were originally navigating from.
My thoughts were to use hyperlink in combination with Cell("address") formula. The cell formula would return me the address of the source cell they selected. with the hyperlink formula I could go back to it as long as they don`t enter any cell on the Analysis sheet. (this sheet is cell protected by the way).

I reproduced the issue in a test file (hypetest.xlsx).
If I use the formula with fixed reference to the sheet, it works : =HYPERLINK("#'test'!$B$8";"Test0")
If I apply some dynamics to reproduce the same, it does not work:

=HYPERLINK(CONCATENATE($E$8;MID(CELL("address");(FIND("]";CELL("address");1)+1);FIND("!";CELL("address");1)-(FIND("]";CELL("address");1)+1));"'";RIGHT(CELL("address");LEN(CELL("address"))-FIND("!";CELL("address");1)+1);LEFT($E$8;1));"Test1")

The result of this dynamics is the same as the fixed reference though.

I also tried the same without concatenate but using & to make the reference, but not working either:

=HYPERLINK($E$8&MID(CELL("address");(FIND("]";CELL("address");1)+1);FIND("!";CELL("address");1)-(FIND("]";CELL("address");1)+1))&"'"&RIGHT(CELL("address");LEN(CELL("address"))-FIND("!";CELL("address");1)+1)&LEFT($E$8;1);"Test3")

Cell E8 contains the value "#'
The result of CELL("address") is :[Hypetest.xlsx]test!$B$7

If I look on internet xls experienced comment I see a lot of hyperlink formulas being dynamic, but haven`t found one using CELL("address") formula inside. Is that not allowed in the Hyperlink formula.

I need to have it working in such that the user doesn't`t need to think on what sheet he was on before, so I can`t use a fixed reference to a sheet. I have to make the sheet from user viewpoint "As comfortable" as can be.

Is there anyone who can help, having some ideas or experienced likewise problems?

Thanks ahead.