Overview
This solution creates a clickable hyperlink between matching transfer transactions. It tries to automatically match the transactions, but allows you to manually set the match if necessary.
UPDATED 12/5/24
I’ve learned a lot in the past year since this was initially released, so I decided to completely refactor the solution and ended up significantly simplifying it, making it easier to install and use.
There’s also a Google Sheets version available now, available here: Automatic "Transfer" Matcher with Clickable Links (Google Sheets version)
Installation
- Download the Excel solution from here: Excel-Tranfers-Tiller-v2.xlsx
- Move the Transfer Link Ext sheet into your Tiller Excel workbook
- Follow these instructions to relink the formulas from external references to your local workbook.
- Create a new column in your Transactions table called Transfer Link, and enter the following formula into the cells in that column:
=IF(ISNA(VLOOKUP(ROW(),HSTACK(INDEX('Transfer Link Ext'!$K$3#,,6),'Transfer Link Ext'!$S$3#),2,FALSE)),
"",
IF(ISNUMBER(VLOOKUP(ROW(),HSTACK(INDEX('Transfer Link Ext'!$K$3#,,6),'Transfer Link Ext'!$S$3#),2,FALSE)),
HYPERLINK("#Transactions!"&'Transfer Link Ext'!$B$5&VLOOKUP(ROW(),HSTACK(INDEX('Transfer Link Ext'!$K$3#,,6),'Transfer Link Ext'!$S$3#),2,FALSE)&":"&'Transfer Link Ext'!$B$6&VLOOKUP(ROW(),HSTACK(INDEX('Transfer Link Ext'!$K$3#,,6),'Transfer Link Ext'!$S$3#),2,FALSE),"Link"),
HYPERLINK("#'Transfer Link Ext'!E"&COUNTA('Transfer Link Ext'!$E$3:$E$500)+3,'Transfer Link Ext'!$B$4)))
Setup
You should be able to use it right out of the box, but there are some settings on the Transfer Helper Data sheet to customize how it works
-
Days Before and Days After are used to tell the Auto function how far to look for possible transfer matches. You can make these numbers smaller or larger as necessary.
-
Transfer Type Name - set the name of the Type that all of your transfer categories are a part of
-
“Need help” message - this is the message the solution displays when it can’t auto detect the matching transfer
-
Hyperlink Start Column and End Column - when you click the link, it selects a few columns of the row, use these values to define how much of the row is selected
-
Hyperlink friendly name - the text displayed on the hyperlink
-
Opening List Divider and Closing List Divider - in the dropdown list of the Manual Override column, these values are used to help highlight the start of the unmatched and matched parts of the list
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:
Click on the link to take you to the Manual Match 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!
Notes
Anything else you’d like people to know?
This is a first attempt at this, so let me know what does/doesn’t work for you. Thank you!
FAQ
Why might the Auto function fail to find a match? There are a couple of reasons it might fail:
- More than one transfer of the same dollar amount in the defined time period
- No transfers with the same dollar amount in the defined time period
