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.
