[Dropped] Automatic SUM balance per row Count

[dpaulson]

Hi group!

I'm pretty much a noob here, so please bear with me.

I'm trying to manually enter old bank statements on a spreadsheet with four columns.. Column A = DATE, Column B = Description, Column C = Transaction Amount, and Column D = Balance.

If starting amount/balance = $4,000.00 deposit for example on row 1, If I add a withdrawal amount of $200.00- on the next row C2, I'd like the balance to be auto calculated to show D2 = D1+C2 and so that all I have to do is enter the transaction amount, and the balance (Column D) will update accordingly on every subsequent row of data entry in column C.

Again, Balance D5 would equal D4 + C5.

If anyone knows how to write a formula to have this re-occurring, that would be most helpful!

Thanks in advance.
Dave

[RoryOF]

Just write it in the second cell from top in Balance column, select it, and pull down by the little black dot in bottom right corner.

I use a similar method, on a monthly sheet, with twelve sheets to the year, carrying balance from end of previous month to start of next month.

Play with some simple figures until you are sure you have it setup the way you wish.

[John_Ha]

I'm trying to manually enter old bank statements on a spreadsheet

I had to do this once for about 100 statements and I found that I could scan the statements, OCR them, and put the OCR'd figures straight into the spreadsheet. There were no errors - easily checked by summing the values and getting the same balance.

Also, if you can get on-line access, just download the statements of copy and paste the figures.

[RoryOF]

A cautionary note: OCR can often (depending on quality of the originals) read 0 (zero) as O (letter Oh) and 1 (One) as l (lower case L) or Capital i, so one needs to check the input and the resulting calculations carefully to be sure none of these characters has been misread.

[MrProgrammer]

Hi, and welcome to the forum.

If starting amount/balance = $4,000.00 deposit for example on row 1 …

Do not use row 1 for data. Use that row for column labels. Begin your data in row 2. Read section 0. Principal Components in Ten concepts that every Calc user should know.

and the balance (Column D) will update accordingly on every subsequent row of data entry in column C.

In D1 put the text Balance. In D2 put your beginning balance. In D3 put =IF(COUNT(C3);C3+D2;""). Fill down. The formula means:
"If the cell to the left has a number, the formula's value is the cell to the left plus the cell above, otherwise the value is the empty string."
[Tutorial] How do I specify the formula for a column?

If you need any additional assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[dpaulson]

Mr. Programmer, you are a blessing!

Thank you so much as I would never have figured out that tid bit of code!

Kind regards,
Dave

[dpaulson]

Well, it worked for the 3rd row, yet as soon as I placed the next transaction amount to C4, it didn't automatically update the balance.

Is there a way to have it continuously update the balance per row without having to add that formula each time?

Thanks again!

[MrProgrammer]

Is there a way to have it continuously update the balance per row without having to add that formula each time?

Asking this question tells me that you have not understood the material in the tutorial How do I specify the formula for a column? which addresses that very subject.