Forum Discussion
SOLVED - Hyperlink function works as Flash Fill but not as Dynamic/spill array
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")))
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")))
lori_m these options are fascinating to me even though I can't get them to work. I tried multiple variations of using the '@' symbol but don't seem to have the right combination to make it work in my case. As for the second option I'm fascinated with the use of the #LET inside the quotes. Is HYPERLINK 'evaluating' that expresssion? Again I wasn't able to get it to work in my case but again really interesting and would love some more insight into what you are doing here.
I did get HYPERLINK to output an array of links with 'friendly' names. I posted the solution in an earlier thread where you and other were toiling with the use of INDEX( .... , XMATCH( array, array) ) as my solution was a variation on that technique. ( hyperlink-function-do-not-support-dynamic-arrays ). Basically I added the friendly name as a second column to the array that the INDEX returns then used TAKE to only return that friendly name column.
m_tarler​ in response to your questions...
i) Is HYPERLINK 'evaluating' that expression?
I think Excel desktop version tries to first evaluate the hyperlinks as A1-references and then as RC-references if that fails. This applies to Hyperlinks generally not just HYPERLINK function:
e.g. type: A1 in a cell then press Ctrl+K and enter as Address: #indirect(rc) to link to that cell.
ii) … the right combination to make it work in my case.
I replied to the other thread with some suggestions, another based on your suggestion could be:
=TAKE(HYPERLINK(HSTACK("#"&ADDRESS(ROW(),COLUMN(A1#)),B1#)),,-1)
I find this all pretty confusing too - my understanding is it is related to function input types. With many functions that take scalar-type parameters we can insert the @ operator but with ROW that takes a reference-type param, the input needs to be omitted.
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?
- 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.