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

# Installation

Hereâ€™s a quick video I recorded to walk through installation and usage of this solution: https://www.youtube.com/watch?v=naurGlkSOd8

There are three main steps to creating this solution and the order of the steps is important.

2. Add two columns to the Transactions table on the Transactions sheet
A. `Transfer ID` column - add this column to the far right of the table and enter the following formula into the first cell:
``````=IFERROR(
IF(VLOOKUP([@Category],HSTACK(Categories[Category],Categories[Group]),2,FALSE)=xfer_group_name,
CONCAT([@Amount],IF([@Amount]>=0," to "," from "),[@Account]," ",YEAR([@Date]),MONTH([@Date]),DAY([@Date])," (",RIGHT([@[Transaction ID]],xfer_transID_chars),")"),
""),
""
)
``````

B. `Transfer Link` column - I added this column between the Amount and Account columns, but you can insert it anywhere you want. Add the following formula to the top cell:

``````=IF([@[Transfer ID]]="",
"",
IFERROR(
MATCH(
VLOOKUP([@[Transfer ID]],
HSTACK(
xfer_manual_override[Transfer ID],
xfer_manual_override[Matched With]),
2,FALSE),
[Transfer ID],0)+1,
xfer_hyper_start_col)&":"&
MATCH(
VLOOKUP([@[Transfer ID]],
HSTACK(
xfer_manual_override[Transfer ID],
xfer_manual_override[Matched With]),
2,FALSE),
[Transfer ID],0)+1,
xfer_hyper_end_col),
xfer_hyper_friendly),
IFERROR(
MATCH(
VLOOKUP([@[Transfer ID]],
HSTACK(
xfer_manual_override[Matched With],
xfer_manual_override[Transfer ID]),
2,FALSE),
[Transfer ID],0)+1,
xfer_hyper_start_col)&":"&
MATCH(
VLOOKUP([@[Transfer ID]],
HSTACK(
xfer_manual_override[Matched With],
xfer_manual_override[Transfer ID]),
2,FALSE),
[Transfer ID],0)+1,
xfer_hyper_end_col),
xfer_hyper_friendly),
IFERROR(
MATCH(
VLOOKUP([@[Transfer ID]],
HSTACK(
INDEX('Transfer Helper Data'!\$O\$2#,,6),
INDIRECT("'Transfer Helper Data'!\$U2:\$U"&ROWS('Transfer Helper Data'!\$O\$2#)+1)),
2,FALSE),
[Transfer ID],0)+1,
xfer_hyper_start_col)&":"&
MATCH(
VLOOKUP([@[Transfer ID]],
HSTACK(
INDEX('Transfer Helper Data'!\$O\$2#,,6),
INDIRECT("'Transfer Helper Data'!\$U2:\$U"&ROWS('Transfer Helper Data'!\$O\$2#)+1)),
2,FALSE),
[Transfer ID],0)+1,
xfer_hyper_end_col),
xfer_hyper_friendly),
)
)
)
``````

# 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 Group Name - set the name of the Group 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

• 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

• Chars of TransID to include - this defines how many characters of the Transaction ID are used to make the Transfer ID unique

# Usage

After setting up the Transaction table columns and importing the Transfer Helper Data sheet, you should see the Transfer Link column populated with links. If the Auto function identified a matching transfer, clicking the link will take you to that row. If the Auto function couldnâ€™t find a matching transfer, clicking the link will take you to the Transfer Helper Data sheet where you can manually select a matching transfer from the drop down list. Once the matching transfer is selected, the hyperlink on the Transaction table will update to use the manual value.

# 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

Interesting! Will you be releasing a Google Sheets version?

1 Like

Iâ€™d love to! Iâ€™m going to let a few people try this one out and see if thereâ€™s any major problems first.

I love the work you do, @jpfieber!

-Alan

4 Likes

This is so cool, @alan.heatherley.

Transfer workflows are important with Tiller to balance accounts and avoid skewing cashflow calculations. Depending on transaction volume, it can sometimes be hard to identify and review transfer pairs.

Youâ€™ve done some incredible work taking a fundamental, everyday Tiller workflow and cleverly linking data effortlessly and automatically.

Iâ€™m particularly impressed with the little extras youâ€™ve implemented in the UX:

• Ability to force manual matches
• Configuration for the match-lookup window
• Matching at the â€śTransfersâ€ť GROUP level (not just the category level)
• Very cool data-validation dividers in the manual-matching table

I did bump into one minor problem installing the â€śTransfer Helper Dataâ€ť worksheet into my test sheet which I mentioned to you in a DMâ€¦ but I think youâ€™re already looking into that.

Through our Builder Rewards Program, we offer cash awards for novel and high-utility template submissions that address core workflows. Tiller is excited to award you \$500.

Thanks again for creating and sharing this workflow.

Hi @randy! Thatâ€™s so kind of you all! I really appreciate the recognition and award.

I also made a quick video walkthrough for installing and using this solution. Take a look here on Youtube and let me know if you have any issues, questions or suggestions.

Thanks!

-Alan

Thanks so much for making such a great installation and usage video, @alan.heatherley. Setup look pretty straightforward. Itâ€™s fun to see the solution in action and also how to resolve some of the exceptions (like multiple transactions with the same amount).

Donâ€™t miss this cool workflow, folks.

@alan.heatherley - Looking forward to the hashed out Google sheets version of this! I have about 300 transfers that I need to link. This was something that Mint (kinda) did automatically, which I took for granted
Anxiously waiting