SOLVED

SOLVED - Hyperlink function works as Flash Fill but not as Dynamic/spill array

Contributor

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

Logical Check.png

Value if True (creates hyperlink to the mismatch amount on the Tracker Import sheet):

Value if True.png

Value if False (creates hyperlink to the mismatch amount on the Bank Import sheet):

Value if False.png

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!

 

 

 

3 Replies

@Kendethar 

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

best response confirmed by Kendethar (Contributor)
Solution

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 

 

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.

Updated Formula.png

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!