What is the goal of your workflow? What problem does it solve, or how does it help you?
I’ve always wanted to be able to ‘link’ two transactions. Most notably, I like the idea for Transfers. I categorize all of my Transfers into “Transfer Out” (money leaves one account) and “Transfer In” (money arrives in another account). I used to use the tag “Match” next to each of the two when I found them both, which is useful when first reconciling, but after that, there is no way to see both transactions side by side, so if things get out of sync, going back through everything is a pain.
The method I devised to link two transfers is to make the “Transfer Out” the “Master” (since technically it always happens first) that the “Transfer In” gets linked to. I generate a unique “key” based off the “Transfer Out” (a string of date, amount, description, institution and optionally Transaction ID), which gets entered in the “Transfer In” transaction on the Transactions sheet, using a drop-down menu, which makes it a part of that transaction. Once the two are linked this way, I then have a “Linked Transfers” sheet that shows a list of all linked (or unlinked) transfers.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
Installation
Install the template from the Tiller Community Solutions Add-on
Configure the Transfers sheet
- Go to your “Linked Transfers” sheet and in cell L9 (you’ll need to expand the “Settings” section by clicking the “+” above column K) select which category you use for your “Transfer Out” transactions. In my case, I have a category called “Transfer Out” that I choose. If you don’t separate yours into “In” and “Out”, you could just select “Transfer” if that’s what you use.
- In cell L12, select which category you use for your “Transfer In” transactions. In my case, I have a category called "Transfer In’ that I choose. If you don’t separate yours into “In” and “Out”, you could just select “Transfer” if that’s what you use. If your “Out” and “In” transactions are in the same category, the sheet will use positive and negative values to determine which is “Out” and which is “In”.
Optional Conditional Formatting Rules
For each rule, do the following:
- On your transactions sheet, go to the “Format” menu and choose “Conditional Formatting”.
- Click “Add another rule”.
- In the “Apply to range” field, enter A2:W, change the W to the last column you have (the right most one).
Optional Transfer Out ‘Unlinked’ Formatting Rule
- To make it easier to see any unlinked “Transfer Out” transactions, you can create a Conditional Formatting rule.
- In the “Format Rules” section, choose “Custom formula is”, and in the field below it enter:
=VLOOKUP((Text($B2, "yyyy-mm-dd") & "_" & Text(ABS($E2),"$0.00") & "_" & LEFT($C2,20) & "_" & $M2 & IF($P2<>"","_" & $P2)),INDIRECT("Linked Transfers!$F3:$H"),3,FALSE)="" - Column B is Date, C is Description, D is Category, E is Amount, G is your new column for Links, M is Institution and P is “Transaction ID” (update any of your column letters if they are different). Set Fill Color to Light yellow 3, Text Color to Dark yellow 3, and Italics.
Optional Transfer In ‘Unlinked’ Formatting Rule
- To make it easier to see your “Transfer In” transactions when linking them, you can create a Conditional Formatting rule.
- In the “Format Rules” section, choose “Custom formula is”, and in the field below it enter:
=AND($D2="Transfer",$E2>0,$G2="") - Replace “Transfer” with the name of the category you use for your “Transfer In” transactions (same as you choose for cell L12 on the Linked Transfers sheet), and where column D is Category, E is Amount, and G is your new column for Links. Set Fill Color to Light yellow 3, Text Color to Dark yellow 3, and Italics.
Optional Transfer Out ‘Linked’ Formatting Rule
- To make it easier to see your linked “Transfer In” transactions, you can create a Conditional Formatting rule.
- In the “Format Rules” section, choose “Custom formula is”, and in the field below it enter:
=AND($D2="Transfer",$E2<0) - Replace “Transfer” with the name of the category you use for your “Transfer Out” transactions (same as you choose for cell L12 on the Linked Transfers sheet) replacing “Transfer” with the name of the category you use for your “Transfer Out” transactions (same as you choose for cell L12 on the Linked Transfers sheet), and where column D is Category and E is Amount. Set Fill Color to light gray 1, Text Color to Dark gray 4.
Optional Transfer In ‘Linked’ Formatting Rule
- To make it easier to see your linked “Transfer In” transactions, you can create a Conditional Formatting rule.
- In the “Format Rules” section, choose “Custom formula is”, and in the field below it enter:
=AND($D2="Transfer",$E2>0,$G2<>"") - Replace “Transfer” with the name of the category you use for your “Transfer In” transactions (same as you choose for cell L12 on the Linked Transfers sheet) replacing “Transfer” with the name of the category you use for your “Transfer In” transactions (same as you choose for cell L12 on the Transfers sheet), and where column D is Category, E is Amount, and G is your new column for Links. Set Fill Color to light gray 1, Text Color to Dark gray 4.
If they aren’t in this order, rearrange the rules so they are (the unlinked must be above the linked for things to work right):
- Unlinked Transfer Out
- Unlinked Transfer In
- Linked Transfer Out
- Linked Transfer In
To use:
On your Transactions sheet, look for one of your “Transfer In” transactions, which will be in yellow if you applied the suggested conditional formatting. Click the dropdown in your new Linked Transfers column and you should see a list of “Transfer Out” transactions. Choose the one that matches the current transaction. In a minute or so the formatting should change for both of the transactions so they are gray. That’s all! Go to your Linked Transfers sheet and you’ll see a list of “Transfer Out” transactions on the left side. The right side should now have one transaction linked to the one you chose on the left.
Keep doing this for all your transactions. By default, once you choose a “Transfer Out” from the dropdown, it will disappear from the list, making it easier to start at the top, and keep moving down through the list of transfer transactions. Note that a red ‘error’ triangle will appear in the cell as well, warning you that the value that you had chosen no longer exists in the data validation list. If this bothers you, you can change cell L6 on the Linked Transfers sheet by unchecking it, so chosen items won’t be removed, and the red triangle won’t show up. In my experience, it’s best to leave it checked until you get through your initial linking (having them filtered saves a LOT of scrolling). Then moving forward, since most new transfers will be at the top of the list, they won’t be hard to find, so unchecking it is up to you.
Anything else you’d like people to know?
There is a down side to using this. It is very processor intensive, and will slow down your template. I’m hoping someone with better spreadsheet skills than I might know how to make it more efficient. I believe the worst part is column H on the Linked Transfers sheet, where it has to find the matching “Transfer In” for the queried “Transfer Out” transactions, so it’s running thousands of ‘Filters’, which seem quicker than the original Vlookups. The conditional formatting, especially the Vlookup, also take a toll (so if things get unbearable, start by removing these).