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.
- 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!
