Forum Discussion
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 range $I$3 that will list all mismatches (bank transactions that are not in tracker and tracker logs that are not in bank) via spill and a data validation list in range $J$3:$J$1000 to select an action from. In range $K$3, I need a spill formula that will search the import sheets for the amount shown on the mismatch list, for each mismatch, then go to it upon click.
In range $K$3:$K$1000, I currently have individual formulas that will check if the mismatch contains "Bank" and if so will display a hyperlink that extracts all text then reads the amount as a value, then matches it to the Tracker Import sheet. Otherwise, it will display a hyperlink that does the same thing but matches it to the Bank Import sheet.
Logical Test (checks if it contains "Bank"):
Value if True (creates hyperlink to the mismatch amount on the Tracker Import sheet):
Value if False (creates hyperlink to the mismatch amount on the Bank Import sheet):
This works perfectly fine but when I do it as a spill formula, the links either don't work or take me to the incorrect sheet/cell.
Formula (Flash Filled range $K$3:$K$1000 - working):
=IFERROR(IF(ISNUMBER(SEARCH("*Bank*",I3)),HYPERLINK("#'Tracker Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3,FIND(" ",I3)-1)),'Tracker Import'!$D$2:$D$1000,0)+1,4),"View Log"),HYPERLINK("#'Bank Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3,FIND(" ",I3)-1)),'Bank Import'!$F$2:$F$1000,0)+1,6),"View Transaction")),"")
Formula (spill from range $K$3 - not working):
=IFERROR(IF(ISNUMBER(SEARCH("*Bank*",I3:I1000)),HYPERLINK("#'Tracker Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3:I1000,FIND(" ",I3:I1000)-1)),'Tracker Import'!$D$2:$D$1000,0)+1,4),"View Log"),HYPERLINK("#'Bank Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3:I1000,FIND(" ",I3:I1000)-1)),'Bank Import'!$F$2:$F$1000,0)+1,6),"View Transaction")),"")
Notes:
• Range 'Tracker Import'!$D$2:$D$1000 is an Amount column that lists logs from my Spending Tracker. This range is just values.
• Range 'Bank Import'!$F$2:$F$1000 is an Amount column that lists transactions from my bank statement. This range is just values.
• If the Mismatch List says an amount is not found in the Bank, that means it's an existing amount in the Tracker that shouldn't be there, and vis versa.
I would highly appreciate any solution to make my current formula a spill formula. Thank you!
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")))
10 Replies
Updated for when result should be looked for and found in another column:
=IFERROR(FILTER(IF($I$3:$I$1000="","",HYPERLINK( IF($I3="<No Mismatches>","", "#LET(x,ISNUMBER(SEARCH(""Bank"",rc9)), y,VALUE(LEFT(rc9,FIND("" "",rc9)-1)), z,IF(x,'Tracker Import'!r2c4:r1000c4,'Bank Import'!r2c17:r1000c17), n,'Tracker Import'!r2c5:r1000c5, XLOOKUP(y,z,z,XLOOKUP(""*""&ABS(y)&""*"",n,n,,2,1)))"), IF($I3="<No Mismatches>","", IF(ISNUMBER(SEARCH("Bank",$I$3:$I$1000)),"View Log","View Transaction")))),$I$3:$I$1000<>""),"")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.
