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")))
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")))- 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.
