Nov 12 2021 05:50 PM
Nov 12 2021 05:50 PM
I'm running into an odd malfunction using LAMBDA to encode a hyperlink function. This function:
should hyperlink to the specified location and show the entered text in the cell. Well, it shows the entered text, but it doesn't execute the hyperlink.
Yet, if I use this in a cell, say =(LambdaAbove)("A7",A7), this works fine (as it did when I tested it). Note: JumpRange needs to be enclosed in quotes, as I'm normally using this with named ranges that need to be concatenated as a string.
Any ideas why this isn't working in Name Manager, but works when entered in a cell?
Nov 13 2021 02:07 AM - edited Nov 13 2021 04:35 AM
Have no exact answer, but thank you for interesting question. I think that Name Manager drops the link, doesn't matter do we have lambda or not. You may try to name =HYPERLINK(), result will be the same.
Nov 13 2021 12:22 PM
I played with the formula to get a form I have become accustomed to
= LAMBDA(JumpRange,DisplayText, LET( filename, CELL("filename"), start, FIND("[",filename), end, FIND("]",filename) + 1, docName, MID(filename,start,end-start-1), HYPERLINK(docName&JumpRange, DisplayText) ) )
and then uploaded it to a named Lambda function, to give the worksheet formula
As was pointed out, the result is not a hyperlink. If a hyperlink is also introduced via the Insert menu, it will determine the link destination. The GOTOλ function only sets the visible text.. My thought is that the hyperlinks do not really form part of Excel but, rather, connects to some external web functionality.
Nov 15 2021 12:45 AM
The interesting thing is that, in the past, I used Name Manager to access the calculation engine because it was more effective than the worksheet. It could access the EVALUATE macro command and perform array calculation flawlessly without having implicit intersection cripple the calculation.
In this situation the position is reversed; hyperlinks on the sheet work whereas they fail as Named formulas. My partially thought through idea was that hyperlink processing is not a function of the Excel calculation engine but something separate linked to document interfaces.
Nov 15 2021 08:49 AM