Overview
This is a Google Sheets version of this Excel solution I posted a while ago (đ Automatic "Transfer" Matcher with Clickable Links **UPDATED 12/5/24** - #5 by alan.heatherley).
This solution provides a clickable link in your Transaction sheet that takes you to the other side of the transfer. It attempts to make the link automatically, but you have the option to make the link manually.
Installation
The sheet is available here: Transfer Link Ext - Google Sheets
Copy the Transfer Link Ext sheet into your Tiller sheet, then make a new column on your Transaction Sheet, with this formula in the row 1 cell:
={"Transfer Link";
MAP('Transfer Link Ext'!W3,
LAMBDA(dt,
IF(ISNA(VLOOKUP(ROW(dt),HSTACK(INDIRECT('Transfer Link Ext'!P1),INDIRECT('Transfer Link Ext'!S1)),2,false)),
IFERROR(1/0),
IF(ISNUMBER(VLOOKUP(ROW(dt),HSTACK(INDIRECT('Transfer Link Ext'!P1),INDIRECT('Transfer Link Ext'!S1)),2,false)),
HYPERLINK('Transfer Link Ext'!B12&'Transfer Link Ext'!B1&'Transfer Link Ext'!B13&'Transfer Link Ext'!B2&'Transfer Link Ext'!B14&'Transfer Link Ext'!B7&VLOOKUP(ROW(dt),HSTACK(INDIRECT('Transfer Link Ext'!P1),INDIRECT('Transfer Link Ext'!S1)),2,false)&":"&'Transfer Link Ext'!B8&VLOOKUP(ROW(dt),HSTACK(INDIRECT('Transfer Link Ext'!P1),INDIRECT('Transfer Link Ext'!S1)),2,false),'Transfer Link Ext'!B9),
'Transfer Link Ext'!B6))))
}
Setup
The top 2 entries here are critical for the hyperlinks to work. The Tiller Sheet ID is embedded in the URL for your sheet. While looking at your sheet, copy the highlighted section from your browser bar:
https://docs.google.com/spreadsheets/d/<SHEET ID>/edit
To get the GID, make sure youâre looking at the Transactions tab, and copy the section of the URL after the âgid=â:
https://docs.google.com/spreadsheets/d/<SHEET ID>/edit?gid=<GID>
The Days Before and Days After options tell the auto-matcher how far forward and backward in time to look for a possible match for the transfer. If not enough of your transfers are being auto-matched, try making these numbers smaller.
The Transfer Type Name is the Type from your Categories sheet that your transfer categories belong to. This is how the Transfer Matcher knows which transactions to look at.
âNeed help messageâ - this shows on the transfer when it canât be auto-matched to another transaction
Hyperlink Start Column and Hyperlink End Colulmn - these define how much of the matching transaction row is selected when you click the link
Hyperlink friendly name - the text that appears on the hyperlink
Unfortunately thereâs a high chance of a circular dependency depending on where in the Transaction sheet you put the Transfer Link column, so I couldnât make the Sheet Lookup automatic for the Transaction sheet. Manually set the columns for your Transaction sheet here.
Usage
After everything is setup, you should see some links appear like this:
Clicking the link will take you to the matching transaction.
If you see some with the Need Help message:
Than you need to flip to the Transfer Link Ext sheet and look at the center section:
Incoming transactions are listed in the left-hand dropdown, and outgoing transactions are in the right-hand dropdown.
The dropdown is broken into sections - Unmatched Transfers, Manually Matched transfers, and Auto Matched transfers. You can use this section to manually set any unmatched transfers, as well as override an auto-matched transfer if it matched to the wrong transaction.
Permissions
Is it ok for others to copy, use, and modify your workflow?
Sure!
