Linked Transfers

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:

I would love to use something like this. I make use of transfers all the time.
Can you please take a look at the sheet since the queries are getting {#REF} instead of a cell reference? (I think you need a transaction sheet on your template, so the Transfer sheet doesn’t break.)

Woops, I tried converting the template off of using my ‘R’ sheet, which isn’t yet ready for prime-time, and missed one formula. Try again, I think it should all be self-contained now.

Thanks, that worked! Can you take a look at the conditional formatting formula you pasted above? What does the M2 represent? Should that be G?

M is Institution. I just updated the post. Thanks!

That worked, and I like it so far. I do agree with you that the performance might be an issue.
One note, the linking has the year formatted as YY rather than YYYY. Not sure it’s an issue, but it takes some time to get used to looking at it in the drop down list.

Good point, I updated the template and the instructions to use yyyy for year.

Would the unlinked conditional format be dependent on having two separate categories? I use Transfer for both but the format doesn’t seem to change.

The unlinked Transfer Out is looking for a matching Transfer In link on the Transfers sheet, so it doesn’t need a category.
The unlinked Transfer In needs the ‘Transfer In’ category, and if the in and out categories happen to be the same, it also looks for amounts that are greater than 0, and checks if a link was already selected for that transaction

OK, thanks. I missed one thing, I forgot the change the formula to yyyy. Now it works.
I already found some transactions that were miscategorized.

1 Like

One suggestion.
When I click the drop down, it shows the most recent one, and that is perfect for linking. However, there are some transfers that don’t have a corresponding transfer. Would it be possible to have a blocklist for transfers not to include in the linking process? I don’t know if that will slow it down even more, but I’m slowly building up a few transactions that I have to scroll down the list until I select the right link.

Why don’t they have a corresponding transfer? If it’s anything other than temporary (transaction hasn’t shown up yet) I’d think it might be best to put those in a different category. The other category can still be in the ‘Transfer’ group, and hidden from reports/templates, but they are obviously different in some way from the ‘normal’ transfers, so it might make sense to have them separated. Plus, yea, that would probably show things down further, and another category is a quick fix…

1 Like

That’s true, I think it makes sense to create a new category for lost/never going to reconcile transactions.

That or manually create the other side of the transaction. I have to do that all the time for PayPal transactions. It imports the spending transaction, but not the transfer transaction from PayPal, so I end up with one transaction from my bank showing money was transferred to PayPal, and 1 transaction from PayPal showing money was sent to the vendor, but no transaction showing the money was received from the bank. So, I create those manually, so I have something to match to.

I just implemented this and it’s working very well, except for the performance as noted. Thank you!

1 Like

One way to speed it up is to change the conditional formatting once you are done.
I changed the cells from a2:x5555 to a2:x2, then as new transactions come in, it will extend itself, but not to the end of your sheet. You can then change it every so often once you clear it up, or leave it until it gets real slow again.

Good idea, I’ll give it a try. Every little bit helps!