Multiple "Account" Formula

[jothkem]

Hello,

I have tried to come up with a formula for this, but keep stumbling over myself. There are 4 accounts (We will call them "Account1", "Account2", and so on) in which I need to track amounts transferred between them as well have a current total amount of each account visible. I have a month starting figure (A1 - A4 for each account). I have a From Account Column "C" and To Account Column "D", as well as an Amount column "E". Finally there is a Current Account Amount (G1 - G4 for each account). I have the data validation set up for the drop downs in columns C and D, and essentially want everything to update as soon as each holds and account name and an amount is in column E. I would prefer to keep all amounts positive and have the formula automatically subtract and add from the appropriate account. I apologize how convoluted this may seem. I feel like it sounded right in my head. Thanks for any and all help!

 

[DanteAmor]

Could you put an image or upload a file to the cloud with an example?
I suppose that in cells G1 to G4 you want a formula, but I wait for your example to see how you have the accounts in columns C and D

 

[jothkem]

Could you put an image or upload a file to the cloud with an example?
I suppose that in cells G1 to G4 you want a formula, but I wait for your example to see how you have the accounts in columns C and D

Link below. I will update my original post with the new locations of these cells, as I have changed them to closer match the final format. I am more than happy to use a separate data tab to store any needed information. Thanks for your reply!

https://www.dropbox.com/s/hmsgbufdh1fcht8/Account Transfers.xlsx?dl=0

 

[DanteAmor]

But your example is empty.

I will try to understand what you need, use the following formula

[TABLE="class: grid, width: 910"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]K1[/TD]
[TD]=VLOOKUP(J1,$A$1:$B$4,2,0)+SUMIFS($G$9:$H$16,$D$9:$E$16,J1)-SUMIFS($G$9:$H$16,$A$9:$B$16,J1)[/TD]
[/TR]
</tbody>[/TABLE]


See the link:
https://www.dropbox.com/s/253i3wont2zd782/Account Transfers dam.xlsx?dl=0

 

[jothkem]

I apologize. I do see that I sent you the wrong sheet that was just the new format. Despite this, you absolutely nailed it. Thank you very much for your help!

 

[DanteAmor]

I'm glad to help you