LAMBDA malfunction with HYPERLINK

[bgaspers]

I'm running into an odd malfunction using LAMBDA to encode a hyperlink function. This function:

LAMBDA(JumpRange,DisplayText,HYPERLINK(MID(CELL("filename"),FIND("[",CELL("filename")),(FIND("]",CELL("filename")))-FIND("[",CELL("filename")))&"]"&JumpRange,DisplayText))

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?

[#2]

@bgaspers 

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.

[#3]

@bgaspers 

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

= GOTOλ("Target",Text)

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.

[#4]

@Peter Bartholomew 

Yes, actually that's not about lambdas themselves. That's about name manager and environment for the lambdas.

[#5]

@Sergei Baklan 

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.

[#6]

@Peter Bartholomew actually we may consider that as a bug. Perhaps in lambdas environment it will be found another way for deploying functions; or name manager will be improved to meet new realities; or we will live with that.

[#7]

@Peter Bartholomew @Sergei Baklan Thanks for the insights, it probably is the case that HYPERLINK doesn't behave like a "normal" Excel function. I guess this is what happens when you roll new functionality out to the broader community, who then start to uncover the edge cases.

[#8]

thanks for sorting out - i run into the same problem