Forum Discussion

Kendethar's avatar
Kendethar
Iron Contributor
Aug 14, 2022
Solved

SOLVED - Hyperlink function works as Flash Fill but not as Dynamic/spill array

Hello all,   I have tracker logs that I'm comparing to my bank and have solved all the issues so far, except for how to spill my Find & Go To hyperlink function.   I have a complex formula in ran...
BI & Data Analysis
excel
Formulas and Functions
Macros and VBA
  • lori_m's avatar
    lori_m
    Aug 15, 2022

    After further investigation, the basic problem with extending the formula given in the post to a dynamic array seems to be that HYPERLINK doesn't accept arrays in both arguments simultaneously.

     

    Instead maybe try making a single HYPERLINK formula that uses relative referencing,

    =IF(I3:I1000="","",HYPERLINK(
LET(x,ISNUMBER(SEARCH("Bank",@I3:I1000)),
    y,VALUE(LEFT(@I3:I1000,FIND(" ",@I3:I1000)-1)),
    z,IF(x,'Tracker Import'!$D$2:$D$1000,'Bank Import'!$F$2:$F$1000),
    IF(x,"#'Tracker Import'!","#'Bank Import'!")&ADDRESS(MATCH(y,z,0)+1,IF(x,4,6))),
IF(ISNUMBER(SEARCH("Bank",I3:I1000)),"View Log","View Transaction")))

    (note this may require clicking "No" in the compatibility checker after entry)
    Another possibility that avoids the use of @ could be:

    =IF(I3:I1000="","",HYPERLINK(
"#LET(x,ISNUMBER(SEARCH(""Bank"",rc9)),
      y,VALUE(LEFT(rc9,FIND("" "",rc9)-1)),
      z,IF(x,'Tracker Import'!r2c4:r1000c4,'Bank Import'!r2c6:r1000c6),
      XLOOKUP(y,z,z))",
IF(ISNUMBER(SEARCH("Bank",I3:I1000)),"View Log","View Transaction")))
m_tarler's avatar
m_tarler
Bronze Contributor
May 07, 2025

So that evaluation of A1 and RC formatting is interesting.   Your example worked but other attempts didn't so need to play some more with that.  But the part that is really baffling me is:

and how the link of the Hyperlink is "#LET.... " and all of that is inside the quotes.  I get if it was "#" & LET( .....) but to have HYPERLINK take the text "#LET(....)" and evaluate the TEXT as actual functions is really interesting.  This almost seems like a throwback to the EVAL() function....  Are there any other functions that will do this?  What is that advantage of doing it this way?  Or should I say, is there any advantage other than the trick of it evaluating the RC format?

lori_m's avatar
lori_m
Iron Contributor
May 07, 2025
  • Are there any other functions that will do this? 

Not that I know of... you may be able to type similar formulas into the goto (F5) dialog

  • What is that advantage of doing it this way?

To be honest I wouldn't recommend this RC technique any longer, I think the INDEX approach would work better here as it can be applied to dynamic arrays generally and also applies to the web version. I posted a general method based on your formula in the other thread utilising ROW().

  • Or should I say, is there any advantage other than the trick of it evaluating the RC format?

I remember posting something related for running vba scripts from hyperlinks,  https://stackoverflow.com/a/32665442. - though I mainly do Python these days. And I do still find the HYPERLINK formula functionality useful for the ability to provide formulaic comments not just links, I recently used hyperlink formulas to annotate a large accounting report.

Resources