Linked Transfers

Has anyone been successful in applying this process in Excel? I have tried unsuccessfully today and am wondering if the original rules have not been corrected based on the on-going dialog. My columns are the default Excell Tiller and I did change the cell references. I could never get the Linked Transfer column to properly work in the transaction tab. I became more confused as I read all the suggested formula corrections. Thank you

I just updated the documentation with the error @jhylton pointed out, so I think it’s now correct (mixed up some of my ‘ins’ and ‘outs’). Are you just having problems with the conditional formatting (which by the way, I stopped using to reduce the performance hit), or are you not able to link the transfers?

Thank you. Yes, was trying to use the conditional formatting adapted to Excel. I stopped trying and am now just trying to follow in non-conditional format option. I do get the transfer link produced in cell O2 but when pasting directly my Linked Transfers column, it doesn’t really find the correct precedents on Transaction tab. Kept moving the Linked Transfers column to the right until the pated formula looked at both Date and Description columns. But it also looks at a other cells not related to either transaction. I will have to take a stab at this another day. Thanks for your help.

Thanks for the response Joseph,

I was trying to use the conditional formatting but had problems. Therefore moved on to the alternate method. My issue is linking to the transfers. I gat a formula in column O of your spreadsheet tab but when pasting same formula into the Transactions tab the field appears blank but there is a formula in it. If I use formulas>trace precedents or dependents. The links are not associated with either transfer in or out transaction.

I will look at this again during the week as time permits.

Thanks again

Dan

Hi Joseph,

I have looked at the formula and have a few questions.

  1. What order should the Excel Transactions tab columns be in?
  2. Mine starting as col A are: Date, Description, Category, Amount, Group, Tags …
  3. The Linked Transfers Tab Transactions matrix does reflect the correct columns association with Transactions column titles1. When I paste the formula from Tab Linked Transfers Cell O2 into Tab Transactions column Z same rows as either the Transfer In or Out transaction, its just blank. If I trace Precedents, none are linked to either transaction.

Any suggestions?

Thanks

Dan

There is no formula to paste into the Transactions sheet. When you installed the template, a “Linked Transfers” column should have been created that uses data validation to create a dropdown menu of the contents of 'Linked Transfers'!$O$2:$O. This is where you choose the transaction you want to link.

I’m confused now if we’re talking about the Sheets version or the Excel version. This is the discussion for the Sheets version.

Hello @jpfieber - This will be Awesome once I get it working right.
@randy - I guess it didn’t take me long to ask for help. LOL
Any help is appreciated! Thank you in advance!

If I Understand Correctly - How This Should Work - My Conditional Formatting is Wrong

  • UNLINKED (In & Out) Transactions Should Be Yellow
  • LINKED (In & Out) Transactions Should Be Gray

Before a Transaction is Linked

After a Transaction IS Linked

MY QUESTIONS

  • Did I understand Correctly?
  • After a transaction is linked
  • Shouldn’t the Transfer Out Linked Transfer Column also Auto Populate Info

My Helper Data & Settings

Linked TSF - Settings

My Conditional Formatting Rules
Linked TSF - Rules

(Unlinked OUT - 2)

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

(Unlinked IN - 3)

=AND($D2=“Transfer In”,$E2>0,$K2=“”)

(Linked OUT - 4)

=AND($D2=“Transfer Out”,$E2<0)

(Linked IN - 5)

=AND($D2=“Transfer In”,$E2>0,$K2<>“”)

When I go to link a transfer, it doesn’t complete the process. I just get an “input must fall in specified range” error. My categories are “Transfer In” and “Transfer Out” and they are marked correctly in the settings.

After I link a transfer it doesn’t complete the process. I get an “input must fall within specific range” error. I use “transfer out” and “transfer in” as the categories and they are selected properly in the categories.

This, unfortunately, is the expected behavior. By default the item you choose from the list disappears from the list, causing the error. To avoid the error, you can change cell L6 on the Linked Transfers sheet by unchecking it, so chosen items won’t be removed, and the red triangle won’t show up. The result though it that every transfer will appear in the list when you choose it, which for me is annoying, so I put up/ignore the error instead.

Hi @jpfieber – quick workaround question for you. I want to add a few columns to the transactions worksheet–to the left of the ‘linked transfers’ column. The linked transfers will lose all of the links if I do this. Adding cols to the right of the ‘linked transfers’ column does not break it. Thanks, -Kyle

Expand the “Helper Data” section and look at R16, this is the cell that looks for the column your “Linked Transfers” are in. The formula starts looking for a “Linked Transfers” header starting with the “Date” column, across all other columns, so I’m not sure how adding columns to either side would make a difference. Here’s a different formula that does the same thing you could try. Paste this into R16 and see if it behaves any different:

=IFERROR(CHAR(64+MATCH(Q16,'Transactions'!$1:$1,0)))
1 Like

Thanks again @jpfieber for creating this addition. I’ve been using it successfully for some time and I have a question:

I’ve entered a Transfer and it has been successfully linked. Now, I decide I want to use some other categories for the transactions. What do I need to do?

Thanks, Kent

Let me rephrase my question - Do I need to do anything with the “Linked Transactions” sheet or the Links column on the Transactions sheet?

I haven’t tried switching mine, but I’d think you’d need to change the categories you want to use in the settings area of the Linked Transactions sheet, and then change the categories of all the transactions (search/replace?) on the Transactions sheet.

Not sure what the issue is. I followed the instructions the same way the creator did, creating Transfer In and Transfer Out categories. I linked the Transfer In transaction with the corresponding Transfer Out one. It was supposed to automatically populate the Transfer Out transaction in the Linked Transfer column. It didn’t, and I got the error message that others mentioned: “Invalid: Input must fall within specified range.”

Supposedly that is a function of leaving cell checked for “Filter Used Links” on the Linked Transfers sheet. So I unchecked it and tried again. Now no error message, but it still does not automatically populate the other transaction. What is the issue?

Where are you seeing that error? It’s a data validation error, so it would either be in column M of Linked Transfers, or the “Linked Transfers” column on your Transactions sheet. In any case, that would happen if you entered something that wasn’t in the dropdown list.

Not the case. It was from the dropdown list. ANYTHING from the dropdown list yields that error (I tried). Still has that error.

Screenshot 2023-12-18 at 11.57.16 AM

The error message goes away if I don’t filter used transfers, but whether or not that is checked, it does not automatically populate the corresponding transfer out.

Ahh, yes. This is “as designed”, and explained in the documentation. If you choose to hide the already chosen transfers, which makes it easier to use, the cell can’t find the one you chose in the list once it’s been selected, so it shows an error. No way around this other than to keep all the transfers in the list.