Hyperlink formula with dynamic sheet references

William_Bockstaele

New Member
Joined
May 27, 2015
Messages
5
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Forum statistics

Threads
1,136,632
Messages
5,676,888
Members
419,657
Latest member
ExcelAl1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top