Balance tracking sheet

[alonso805]

Hey everyone,

I just wanted to post a question for those Excel user with more experience than me. I work at a small gym where everything is still recorded with pen and paper. I am trying to automate a lot of these task by making user friendly sheets for our staff who aren't too computer savvy. With that said, here is what I am trying to do. Hopefully someone here will be able to help me figure this out.

At the gym, we have a physical balance sheet that we used to keep track of "store/member credit", which is what I am trying to automate with the least amount of user input.

The fields are as followed:
A -Members
B- Charge amount
C- Previous Bal
D- New balance.

The only input I want the other users to modify is the "Charge Amount" which will either be a credit or debit to the member's balance. I would like the spreadsheet to calculate the "New balance" , which is D= B+C, and also keep record of the prev/old balance. The formula for "New balance" is easy, the problem I am running into is automating the previous balance.

For example, lets say a member has a previous balance of -$5.00 and they add another DBT for -$2.00. The new balance will be -$7.00, but I need it to become the "previous Bal" also for future transactions. dont reference the orignal -$5. As it stands, the user inputs the "Charge (-$2)" which is added to the "previous Bal (-$5)" to calculate the "new Bal (-$7"). They also have update the "previous Bal (-$5)" with the "new balance (-$7)" so future transactions are calculated on the new Prev balance(-$7), and not the old previous Bal(-$5). I hope that makes sense to someone out there. I'm not sure if its something as simple as a formula or if VBA needs to be used. Hoping someone can point me in the right direction

Thank you
AlonzoMember Balance.png

[bebo021999]

With name running down and charge/balcance running accross, this is a simple version.
You can lock other cells with password, and release charge columns only for user's inputing.

[Roel Jongman]

Hi Alonso805,

Welcome, a general rule of Excel forum is that you always upload a sample file of what you already have and for more complicated questions to result sheet of how you like it to look. That is also part of the forumrules
In this case it is a simple 4 column inputsheet so not much trouble to create a file myself. but in most cases it also helps avoid confusion on what you need.

I have used an excel Table that will extend automatischly if you type a membername under it, also the formulas for the balance calc are automaticly copied to the new table line.
so alle the worker does in input the name and the charge.
I used a sumif function for both balances where the prev. balance formule stops counting on the line before the current one and the new balance is a sumif including current line. the "if part" is that the formula only sums the amount of the specified member in column A.

Also I included a slicer that might be helpfull for workers to show all the transactions of 1 member. a slicer is basicly a nicer formatted filter.



Edit: you could consider adding a date column so it is easier to track when which charge was made. Also a description might have added value to know why the charge was made.

[alonso805]

My apologies everyone, I did not see an option to attach a file to my original post. Here is the current file I am working on.

[bebo021999]

Insert a "starting balance" column B and input a "very first" starting balance for each employee.
Then in D2:

=SUMIF($E$1:E2,E2,$B$1:B2)+SUMIF($E$1:E2,E2,$C$1:C2)

Make sure that each employee has only one "starting balance" in column B.

[alonso805]

Hey Roel,

Thank you for responding. The sheet that you attached is actually better for my applications since it allows me to track individual transactions for each member. I wanted to see if you could clarify. After inputting one or two new transaction, the new balance column is generating a "inconsistent calculation formula error". The original two rows that you entered aren't expiring the same issue, but the subsequent ones are. It seems that new balance from the previous row is being brought down to the new entry. If I click on the "resolve" it corrects the issue, but I wanted to know if there was a way I could fix the error from happening in the first place to minimize employee input? Any help would be appreciated and thank you again

Alonzo