Hi, I am trying to build a bank account ledger in excel that allows a transaction to be split across multiple (budget) categories. My goal is to be able to report how much of the base transaction split remains to be allocated.
I have no problem achieving this using a horizontal table, but I cannot figure out how to achieve this using a vertical table (as per a normal ledger).
In the attached screenshot, E2 is trying to calculate the balance of the split that remains to be allocated for the base transaction shown in G2 based on rows allocated with "split" in column C. The split amount is placed in either column F & G. (Split amounts do not affect the account balance, only the base transaction affects the account balance)
The issue I have is working out how to get Excel to calculate in columns until there is a change in another column (in this case column C to return the split remaining to be allocated.
Hopefully someone much smarter than me can teach me how to resolve this task. I would be grateful for any help as I am truly stuck. Thank you.
I have no problem achieving this using a horizontal table, but I cannot figure out how to achieve this using a vertical table (as per a normal ledger).
In the attached screenshot, E2 is trying to calculate the balance of the split that remains to be allocated for the base transaction shown in G2 based on rows allocated with "split" in column C. The split amount is placed in either column F & G. (Split amounts do not affect the account balance, only the base transaction affects the account balance)
The issue I have is working out how to get Excel to calculate in columns until there is a change in another column (in this case column C to return the split remaining to be allocated.
Hopefully someone much smarter than me can teach me how to resolve this task. I would be grateful for any help as I am truly stuck. Thank you.