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 L6 (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 L9, 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).