Excel Banking Summary Sheet

[soybean]

I would want cell B1 to display what is being shown in cell F5. But, if I make a deposit or withdrawal in either cell D6 or E6, which would cause the cell F6 to show the new balance, I want the cell B1 to automatically update to the new balance (or automatically go from F5 to F6).

I can't give you a solution in Excel.  I will say that I believe Microsoft Access would be better suited to the application you want to develop.  With Access, I believe a Query or Report could be formatted to display what you want, including the Balance at the top of the report.

[cstaniszewski]

Okay. I'd have to learn Access, but I will also try that.

[kyle_engineer]

OK... here we go. I think should do alright.




Now I'll go 1 cell at a time...

A2:

Code: [Select]

=LOOKUP(9.99999999999999E+307,G:G)
This will return the value in the last cell in the G column.

The "E" and "F" columns are simple straight numbers. The only snag that I was coming across, is that in order to make the math work properly, you have to manually add the "-" (minus) symbol to the "withdrawal" column. I wanted to find a way around that, but I couldn't find anything.

G2:

Code: [Select]

=IF(AND(ISBLANK(E2),ISBLANK(F2)),"",SUM(E2,F2))
This code only applies to G2, although it's almost the same as the rest of the G column, which is:

Code: [Select]

=IF(AND(ISBLANK(E3),ISBLANK(F3)),"",SUM(E3,F3,G2))All it is is adding the previous G cell to the SUM at the end.

Then on the formatting, I just made columns E through G money format, and then I used conditional formatting to make it all red when negative values occur.

Conditional Formatting:
Format>Conditional Formatting
Cell Value Is - less than or equal to - 0

Then format it however you want the negatives to show.

If this works for what you want, then that's great. Otherwise let me know...

Sincerely,
kyle_engineer

[soybean]

The only snag that I was coming across, is that in order to make the math work properly, you have to manually add the "-" (minus) symbol to the "withdrawal" column. I wanted to find a way around that, but I couldn't find anything.

Try putting a minus sign in front of the cell reference for column F in your formula, as follows:

Code: [Select]

=IF(AND(ISBLANK(E3),ISBLANK(F3)),"",SUM(E3,-F3,G2))Then, you won't need to enter the numbers in column F as negative numbers.   

[kyle_engineer]

LOL! Thanks. I just realized that that would totally work. I was using the SUMIF function, and it didn't work with that. So there it is.

Code: [Select]

=IF(AND(ISBLANK(E3),ISBLANK(F3)),"",SUM(E3,-F3,G2))
is all that is needed to repeat the G column all the way down... Just copy and paste all the way and you're golden! Thanks soybean!

[cstaniszewski]

It works GREAT!! Thank you guys SOOOO much. That is exactly what I was looking for.

[patio]

HuH ? ?

[soybean]

HuH ? ?

This thread got off to a confusing start, as you know.  But now, he's saying the formulas posted by kyle_engineer, along with the change I suggested in reply #18, makes the spreadsheet work the way he wants.