Overview
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.
Note: This has been available on Google Sheets for a while, I finally got around to creating an Excel version as well.
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.
Installation
- Download the Linked Transfers workbook.
- Follow these instructions. Note you wonât have to âEdit Linkâ and âChange Sourceâ to get this sheet to work.
- Create a new column in your Transactions sheet called âLinked Transfersâ. It should be somewhere to the right of your âAmountâ column.
Configure the Transfers sheet
Go to your âLinked Transfersâ sheet and in cell M6 (youâll need to expand the âSettingsâ section by clicking the â+â above column N) 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 M9, 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
Iâve stopped using this as it was a big performance hit. If youâre interested, you can see my recommendations on the Google Sheets version.
To Use
On your Transactions sheet, look for one of your âTransfer Inâ transactions. 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. 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 M3 on the Linked Transfers sheet to FALSE, so chosen items wonât be removed, and the red triangle wonât show up. In my experience, itâs best to leave it set to TRUE, at least 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 changing to FALSE 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.
