🏆 Linked Transfers

What happens if you do?

The link doesn’t show up as linked and the formatting doesn’t change. I had one record as whatever the transactions showed up as, and then a manual transaction where I just wrote “transfer.” It did show up in the drop down for me to pick, but when I went to the Transfer sheet, the “transfer” was there without the link.

Hmm. I was thinking maybe there would be an issue if “Transfer” was the name of your “Transfer In” and “Transfer Out” categories, but looking through the formulas in that template, I can’t see where the description would make a difference. All I can say is to look at columns F (ReconcileIndex) and N (Links to Show) to see if anything looks out of the ordinary there, since that’s what is being matched.

I was able to replicate the behavior you’re describing if I set the transactions category to “Transfer” but have the “Transfer Out” category set to “Transfer Out” and the “Transfer In” category set to “Transfer In”. Doesn’t seem to care what the description is though, as it shouldn’t (not filtering by description anywhere). Are you sure you have the transaction categorized properly?

It’s now working. :slight_smile: I did have it all set in the same category, so that wasn’t it. Could have been a fluke since I redid it now and all is good.

1 Like

As with the Tag Manager recently, we just published this Linked Transfers template into the Tiller Community Solutions Add-on for ease of discovery, trial, and maintenance.

We also awarded @jpfieber $200 for sharing his template through the add-on.

1 Like

@jpfieber ,
Nice job with this sheet! It’s clever way to keep track of transfers.

My initial approach was the same one you described. Using the tag “Match” next to year transfer. That was better, but still lacking info on which transaction when with which other transaction.

My next advance was to add a counter to the Notes column starting with #1. Then #2, etc. Both transactions with #1 would be searchable by searching #1.

I think the next level of doing it is your Linked Transfer sheet. I’ll give it a try.

What do you do if you have a transfer that is also a split transaction? Say $100 out, but then it goes to $50 in one transaction and $50 in another? I suppose you can divide that $100 into two $50 transactions?

Jon

I hope it works for you, it has been serving me well. The way I handle transfers, there would never be a need to do a split. For me, money leaves an account and it arrives at an account. What happens to it after it arrives is a separate transaction. I do splits on Expense transactions, and maybe it’s possible to split an Income transaction (I’ve never done that), but I don’t see a reason to split a Transfer transaction, it should just be moving money from one account to another.

1 Like

This is really great! Is there a way to handle multiple transfer categories? I have credit card payments categorized differently than bank to bank transfers, but would love to be able to link them as well.

Hmm, I’ll have to give that some thought, not sure how difficult it would be to include multiple ‘Transfer In’ or ‘Transfer Out’ categories.

The conditional formating needs to be updated to match the sheet name that gets added. In the formula above you have the sheet name as Transfers but it gets auto added from the community app as Linked Transfers.

Here is an example of how I had to update my conditional formating so that it would work:

=VLOOKUP((Text($B2, “yyyy-mm-dd”) & “" & Text(ABS($E2),“$0.00”) & "” & LEFT($C2,20) & “" & $I2 & IF($L2<>“”,"” & $L2)),INDIRECT(“Linked Transfers!$G3:$I”),3,FALSE)=“”

Also note that I had to change some columns based on the notes but that was accounted for in the description under the conditional formating formula. The different sheet name and the column names in the linked transfer sheet were not mentioned.


Also I believe these instructions are wrong:

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.

It should read:
Optional Transfer Out ‘Linked’ Formatting Rule

  • To make it easier to see your linked “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:
    =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 Transfers sheet) replacing “Transfer” with the name of the category you use for your “Transfer Out” transactions (same as you choose for cell M6 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.

One last thing… I believe the Unlinked Out formating should be: Light yellow 3, Text Color to Dark yellow 3, and Italics

I completely forgot about updating the documentation when we added the template to the gallery. I just updated everything, thanks for pointing this out!

Glad I could help out. This is a great addition to tiller. Thanks for your time and work to create this.

It seems that the installation instructions are out of date. I installed the template from the community solutions sidebar, and it seems to have automatically configured the sheet, including the linked transfers column, data validation, and more.

Also, I am getting the following error when hovering over linked transactions in the linked transfers column. “Invalid: Input must fall within specified range.”

image

On an unrelated note, it looks as if the template only allows selecting one side of the transfer, such that the other side’s linked transfer column is blank? Also, there is no way to “go to matching transfer” as there is in Quicken, correct?

image

That “error” with regards to data validation is supposed to happen. Basically, once you link a transfer that whole text is removed from the query that creates the list of transfers to link.

1 Like

I’m not quite sure what you mean by “data validation.” Can you be more specific? Are you saying that the error message is a side effect of how this workflow functions? Or is it a feature that shows that the transfer is not (fully) linked? Does the opposite transaction also need to be explicitly linked to the source transaction?

As you suggest, it is a side-effect of how it works. If you open the ‘Settings’ section of the sheet by clicking the + above column L, you’ll see an option for “Filter Used Links”. If you uncheck that, the error will go away on the cells in your Transactions sheet. The penalty, though, is that from now on when you click the dropdown to choose a Transfer to link, you’ll see every transfer, including those already used. I find this to make it more difficult to create the links, so I prefer to remove already used links from the list, but the side-effect is that Data Validation (the feature that provides you the drop down menu in the cell) can’t match what’s in the cell with the range it gets the transfers from, since it gets removed the moment you choose it.

1 Like

Thanks for the sheet, it’s been helpful. I could use some help on the script directions. For example, the script for “Optional Transfer In ‘Unlinked’ Formatting Rule” is as follows.
=AND($D2=“Linked Transfers”,$E2>0,$G2=“”)

The directions say to replace “Transfer” with the name of the category you use for your “Transfer In” transactions. Should that read to replace “Linked Transfers” instead? In other words, if my category transfer in category is labeled “Transfer In”, would the formatting rule be =AND($D2=“Transfer In”,$E2>0,$G2=“”)

Thanks for the help.

Woops, when we added this template to the gallery we changed the name of it, and updated the documentation, and some things got changed that shouldn’t have. I just fixed the few that I saw, including what you’re asking about. The formula should be =AND($D2="Linked Transfers",$E2>0,$G2="") , and you replace ‘Transfer’ with the name of your Transfer In category, so your example should be correct: =AND($D2="Transfer In",$E2>0,$G2="")

1 Like

That worked. The other thing I changed was for the ‘optional transfer out’ rule I replaced ‘transfer’ with ‘transfer out’ rather than ‘transfer in’. Thanks so much, this is a very useful tool for me.