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.
- What order should the Excel Transactions tab columns be in?
- Mine starting as col A are: Date, Description, Category, Amount, Group, Tags âŚ
- 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
My Conditional Formatting 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.
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.