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 “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
- Copy the “Transfers” sheet from the link below to your Foundation Template.
- Rename the sheet “Transfers”
Create the link column
- In your “Transactions” sheet, create a new column called “Links”. If you’d like to call it something different, you can, just change the cell L15 on the “Transfers” sheet to the name of the column. You can put it where you like, I put mine to the right of the “Amount” column.
- With the new column created, click on the letter at the top of the column to select the entire column.
- Go to the “Data” menu and choose “Data Validation”.
- In the “Cell range” field, it should currently say “Transactions!G1:G…” (the three dots represents how many rows you have). Replace “G1” with “G2” so you don’t get a drop-down menu in the cell your title is in.
- In the “Criteria” field, leave “List from a range” selected, and enter “Transfers!N6:N”.
- Click “Save” and you should see drop down menus appear in your new column.
Configure the Transfers sheet
- Go to your “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("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 gray 1, Text Color to Dark gray 4, 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 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 In” transactions (same as you choose for cell L12 on the 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 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 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 Links 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 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 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 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).
Is it ok for others to copy, use, and modify your workflow?
Yes, please! Let me know what’s not working, and of any ideas for improvements!
If you said yes above, please make a copy of your workflow and share the copy’s URL: