Follow hyperlink function to hidden sheet

[Swayzy]

Hi

After browsing a bit on the topic I cant find any information if it is possible to follow hyperlinks to hidden sheets through the hyperlink function? I have found several ways with the normal link option but the name in my function is a value found by formulas it would require some extra steps to add the hardcoded links with code and they would need to be updated so it would be a fair bit more hassle. I am much open to a custom function, the only requirement is that I can use a formula inside the cell to find the value to display then I can hardcode the address (a plus if the sheet name can be referenced).

 

[Yongle]

1. What is the formula that you are currently using?
- ensure that you provide a formula that works for you when the sheet is not hidden
- provide details what is in each of the cells referenced by that formula

2. Is your problem ...
- getting the "Friendy name" to display ?
OR
- getting the hyperlink address correct ?
OR
- making the hyperlink work ?
OR
- something else ?

 

[Swayzy]

Ok, so I have setup an overview sheet that through alot of indirect formulas finds a value on a different sheet. This formula is what is in the friendly name argument and thus what is displayed and what I would click on. The address is made through a formula like this "#"&A$11$&"!A1". The problem is then that if I hide the sheet the hyperlink doesnt work anymore, which it shouldnt. So I looked at solutions that use VBA and worksheet hyperlinkfollow events to grab the subaddress and then show the sheet and go there. But my problem is that clicking the hyperlink function compared to a hardcoded hyperlink dosent seem to trigger those codes.

 

[Yongle]

My Question:
What is the formula that you are currently using?
- ensure that you provide a formula that works for you when the sheet is not hidden
- provide details what is in each of the cells referenced by that formula

Your reply:
"#"&A$11$&"!A1"

Is that string correct?
It is not a formula

What is in A11?

 

[Swayzy]

In A11 I would have the sheet name to create the hyperlink address. The problem I want to know is if the hyperlink function can trigger a VBA event or if I can create a custom function like the hyperlink function that can hyperlink to hiddensheets by clicking on the displayed value. My formulas in the hyperlink works as they should.

 

[Yongle]

A UDF is a custom function used as a formula which behaves exactly like a formula in EVERY respect
- it returns a value to a cell (nothing else)
- it cannot be used to hide/unhide , select, format ... etc

 

[Yongle]

The problem I want to know is if the hyperlink function can trigger a VBA event ?

FollowHyperlink event is triggered ONLY by inserted hyperlinks
- it is NOT triggered by clicking on links created with formula =HYPERLINK

 

[s_himself]

FollowHyperlink event is triggered ONLY by inserted hyperlinks
- it is NOT triggered by clicking on links created with formula =HYPERLINK

How can you make a =Hyperlink formula work with hidden sheets?

I have a =hyperlink formula that shows different value (hyperlink) to different hidden sheets. This work as long as the sheets are visible. But I would like to make the hyperlink work with the sheets hidden as well. Is it possible?

I'm testing two different formulas.

One formula is a IF formula (in cell B5) that shows a hyperlink depending on value in another cell (E2). It goes something like this; IF E2=X then show hyperlink A, IF E2=Y, show hyperlink B and so on.. up to 5 defferent hyperlinks. (to 5 hidden sheets)

The other formula is a =hyperlink that is build with a vlookup like this: Excel formula: Build hyperlink with VLOOKUP | Exceljet

They all work as they should, as long as the sheets are visible. But as soon as I hide them, they stop working.

So... Depending on what text value E2 is, I would like to have function, VBA or not, to unhide, switch to hidden sheet when hyperlink is clicked on. A button is OK as well.
On the hidden sheets I have hyperlinks back to the main sheet.

 

[mookyon]

Hi,

Did anyone solved this issue: unhiding a hidden worksheet when hyperlinked by the HYPERLINK FUNCTION?

Rgds,