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")))
Can you attach a copy of the source data - removing any sensitive info ?
The attachment illustrates a conditional hyperlink DA formula for a simplified setup...
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?
- Terrific lori_m ! This made my day !
Wonder how HYPERLINK() adapts to spill capability with RC reference style, but does not seem to work, atleast for me, when I use the regular A1 reference style.- Yeah, that was the issue I initially posted about because the way I wrote the formula should've technically worked (A1 syntax and the math) but didn't for whatever reason. The way Excel's C++ code was written for the =HYPERLINK() function I'd imagine.
This is genius, thank you! Works perfectly. I'm not too familiar with the =LET() function yet with how new it is. So, working on that skill still but I can see it has many applications.
A few small things:
1st, if the user clicks a null hyperlink outside of the listed "View Transaction/Log"s, Excel returns a "Reference isn't valid." error, which was an OCD thing for me. Therefore, I added a =FILTER() function to narrow down the spill, per the mismatch range that's not empty.
2nd, I forgot to mention that my mismatch range will display <No Mismatches> in range $I$3, when that is true.
If this is true, range $K$3 will return "View Transaction" and if the user clicks it, Excel returns the "Reference isn't valid." error. So I added an =IF() function to the link_location and [friendly_name] arguments to check if $I$3 has that text.
3rd, I changed the relative references to absolute, still works fine.
4th, I didn't integrate an =IFERROR() function like I usually do for sheet cleanliness since everything is essentially covered.
Formula (spill from range $K$3 - tweak of lori_m's):
=FILTER( IF($I$3:$I$1000="","",HYPERLINK( IF($I$3="<No Mismatches>","", "#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($I$3="<No Mismatches>","", IF(ISNUMBER(SEARCH("Bank",$I$3:$I$1000)),"View Log","View Transaction")))),$I$3:$I$1000<>"")Thanks again and keep up the awesome work!
