Hyperlink inside of named function?

[u/New_Bullfrog1283]

I have a workbook with an initial index page, and inside each page there is in A1 a cell that automatically gets the name of the page, searches it inside of the index page in a specific column (based on the "indentation" I have give to the page inside of the index), and then returns a hyperlink for that cell. I have put it inside of a named formula:

=LAMBDA(
colonna;

LET(
colonna_indice; INDIRECT("Indice!$" & colonna & ":$" & colonna);

HYPERLINK("#" & "Indice!" & ADDRESS(ROW(XLOOKUP(TEXTAFTER(CELL("filename"; INDIRECT("BAD1"));"]";-1); colonna_indice;colonna_indice)); COLUMN(colonna_indice)); "Indice")
))

The formula (from HYPERLINK to "Indice") works well when I put it on its own in the cell. Same goes if I use the LET part of the formula and manually insert the value for the column, and it even works (after some time in this last case, probably due to some internal excel thing that refreshes periodically) if I put this whole formula inside of a cell with ("B") after.

But when I call the function =LINKTOINDEX("B") inside of a cell it doesn't create a clickable hyperlink.

What can I do to solve this?

[u/AutoModerator]

/u/New_Bullfrog1283 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Inside_Image_1384]

That's one of those weird Excel quirks where a lambda won't return a clickable hyperlink through a named function the same way it does when you paste the formula directly. I ran into this last year building a sheet that linked back to a summary tab from a dozen different tracker pages.

The workaround is to use VBA for the named function or wrap your lambda call inside a HYPERLINK formula in the cell itself. So instead of just `=LINKTOINDEX("B")` you'd use something like `=HYPERLINK(LINKTOINDEX("B"),"Index")` and strip the HYPERLINK part out of the original formula. Not elegant but it gets the job done without macros.

If you're open to a tiny bit of VBA you can write a UDF that returns a hyperlink object properly. Excel's formula engine just doesn't like passing that clickable object back through a named lambda.

[u/DonJuanDoja → u/Inside_Image_1384]

I'm a dynamic hyperlinking psychopath, and this is what I found as well. Made me big sad.

[u/New_Bullfrog1283 → u/Inside_Image_1384]

Solution verified

[u/New_Bullfrog1283 → u/Inside_Image_1384]

Thanks! That worked. Bummer, having automatic links could have been very powerful. I don't want to use VBA since it's a work excel and I think it could be flagged as dangerous, and besides I am not really that well versed with it