Author Topic: Excel Banking Summary Sheet (Read 10201 times)

kyle_engineer · « **on:** August 29, 2011, 04:02:36 PM »

Here is what I did to emulate what you said you would want...

Column A Has the current total - the code for that cell is just

Code: [Select]

=G65536
Cell G3 (being the first sum cell) is

Code: [Select]

=SUM(E3,-F3)
All of the following G cells are the G4 pasted all the way down, but it's

Code: [Select]

=SUM(g3,(SUM(E4,-F4)))
That's all I did to get the above. It's very simple, and crude, but I think it achieves what you want. Usually you don't need to tell it so skip a blank cell (at least for math), because it counts it as a "0" and doesn't do anything to the equation.

If this works, you're totally welcome, if not, I'm frequently in the chat and you can always post here.

Also, anyone feel free to correct me if this isn't optimal.

Sincerely,
kyle_engineer

patio · « **Reply #1 on:** August 30, 2011, 05:42:55 AM »

Who is this for ? ?

cstaniszewski · « **Reply #2 on:** August 30, 2011, 12:58:39 PM »

It's for me. I am trying to get the "balance" cell to automatically update from the "balance" column. The code I currently have in it is =IF(AND(ISBLANK(E3),ISBLANK(D3)),"",E3+D3). The function is dragged down to a certain point, and I want the balance cell to show the balance at the top of the page also. So if I withdrawal a certain amount from say C11, the cell next to it will display what the remaining balance is. The "balance" column will only display a number if there is a deposit or withdrawal made. And I need that same thing shown at the top of the page. I hope that makes sense.

kyle_engineer · « **Reply #3 on:** August 30, 2011, 02:30:50 PM »

patio: Yeah it's for cstaniszewski.

khulekani: Totally the wrong thread for that. Please read this http://www.computerhope.com/forum/index.php/topic,33330.0.html as requested by the admin.

cstanszewski: Does this help at all? Do you not want the balance to continue down like that? You can also take a screen shot (ctrl+Print Screen [upper-right on keyboard]) to show us what you've got and better explain what you're going for.

Salmon Trout · « **Reply #4 on:** August 30, 2011, 02:41:02 PM »

Quote from: kyle_engineer on August 30, 2011, 02:30:50 PM

patio: Yeah it's for cstaniszewski.

Where did you get the original question from? He has only made one post, which was after you started this thread.

cstaniszewski · « **Reply #5 on:** August 30, 2011, 03:00:27 PM »

It works, but I don't like the numbers going all the way down. It'll confuse me every once and a while.

[regaining space - attachment deleted by admin]

Salmon Trout · « **Reply #6 on:** August 30, 2011, 03:44:02 PM »

Quote from: Salmon Trout on August 30, 2011, 02:41:02 PM

Where did you get the original question from? He has only made one post, which was after you started this thread.

What is the answer to this?

cstaniszewski · « **Reply #7 on:** August 30, 2011, 03:49:47 PM »

He used a different formula than what I had. Though, I don't want the balance to show in all the cells in beyond what has been utilized. And, I also want the cell labeled "balance" at the top (cell that is highlighted in red) to show the final balance in the "balance" column.

Salmon Trout · « **Reply #8 on:** August 30, 2011, 03:51:35 PM »

Quote from: cstaniszewski on August 30, 2011, 03:49:47 PM

He used a different formula than what I had. Though, I don't want the balance to show in all the cells in beyond what has been utilized. And, I also want the cell labeled "balance" at the top (cell that is highlighted in red) to show the final balance in the "balance" column.

How did he know your question before you posted it? What's going on here?

cstaniszewski · « **Reply #9 on:** August 30, 2011, 03:54:44 PM »

Lol, we chatted before the forum was created. And he wanted to show me one of the solutions he came up with.

Salmon Trout · « **Reply #10 on:** August 30, 2011, 03:59:15 PM »

Personally I don't really think that threads should be done like that. It dilutes the value of CH as a resource for others. It's not just a chatroom for you and the other guy.

cstaniszewski · « **Reply #11 on:** August 30, 2011, 04:04:00 PM »

What?!

truenorth · « **Reply #12 on:** August 30, 2011, 04:27:17 PM »

I am absolutely positive that Salmon Trout can fully explain the observation he has made. However to give him a rest i'll venture an explanation of what he is trying to advise you and hopefully it shall be close to the mark.
A primary goal of the CH forums (second only to trying to assist a presenter of an issue) is to add to the knowledge enhancement of as many CH members (and indeed guests) on topics discussed. In this case you answered (except to your chat partner) an unknown question or request for help. Therefore as Patio wondered where did this obviously an answer come from (as it certainly was NOT a request for assistance). Normally answers are only given in the forum to questions. You were depriving all but the chat participant the benefit of what the question was. An alternative if you wanted to keep your exchange private between just the 2 of you you could have used the PM route. However let me be quick to say we don't encourage that either for essentially the same reason. Hope this answers your "what". truenorth

cstaniszewski · « **Reply #13 on:** August 30, 2011, 04:40:23 PM »

I can somewhat understand that. But, that was not entirely true. He posted this so I can get more input. I also restated the question, though it was tough to explain, so that anyone else could help. I am looking for a solution to a problem, not a problem to a solution. So I will re-ask the question again. I am trying to get the cell B1 to display my current balance from the "balance" column. The formula that I am using in the "balance" column is as follows... =IF(AND(ISBLANK(E4),ISBLANK(D4)),"",F3-E4+D4). So, as an example from my post with my picture attached, 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 see What kyle_engineer did. But I don't want the numbers to show unless there is a deposit or withdrawal.

kyle_engineer · « **Reply #14 on:** August 31, 2011, 01:20:50 PM »

Salmon Trout: I understand where you're coming from.

As I'm sure you noticed, I'm a bit new to this, but it makes total sense now with truenorth's clarification. In the future I will not post things in this way, as I can see that it would create a bit of confusion on the forums (especially if many people were to post like that).

truenorth: That makes total sense, and as said above, my apologies.

cstaniszewski: All he's saying is that to any random person who decided to check the thread before it was explained in these recent posts would have been confused as to the origin of the whole thing (as I imagine both Salmon Trout and patio were. Anyway, no harm, no foul.

I'll see what I can come up with and let you know.

Of course, if anyone else reading this post has the solution, please don't hesitate to post it.

Sincerely,
kyle_engineer

soybean · « **Reply #15 on:** September 01, 2011, 07:52:32 AM »

Quote from: cstaniszewski on August 30, 2011, 04:40:23 PM

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 · « **Reply #16 on:** September 01, 2011, 11:39:51 AM »

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

kyle_engineer · « **Reply #17 on:** September 01, 2011, 05:51:26 PM »

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 · « **Reply #18 on:** September 01, 2011, 06:16:14 PM »

Quote from: kyle_engineer on September 01, 2011, 05:51:26 PM

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 · « **Reply #19 on:** September 01, 2011, 08:47:26 PM »

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 · « **Reply #20 on:** September 02, 2011, 12:06:14 PM »

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

patio · « **Reply #21 on:** September 02, 2011, 05:37:31 PM »

HuH ? ?

soybean · « **Reply #22 on:** September 02, 2011, 06:05:13 PM »

Quote from: patio on September 02, 2011, 05:37:31 PM

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.

Computer Hope Forum

News:

Author Topic: Excel Banking Summary Sheet (Read 10201 times)

kyle_engineer

patio

cstaniszewski

kyle_engineer

Salmon Trout

cstaniszewski

Salmon Trout

cstaniszewski

Salmon Trout

cstaniszewski

Salmon Trout

cstaniszewski

truenorth

cstaniszewski

kyle_engineer

soybean

cstaniszewski

kyle_engineer

soybean

kyle_engineer

cstaniszewski

patio

soybean