Saved Bookmarks
| 1. |
Solve : Excel Banking Summary Sheet? |
|
Answer» 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. Okay. I'd have to learn Access, but I will also try that. 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_engineerQuote 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. 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!It works GREAT!! Thank you GUYS SOOOO much. That is exactly what I was looking for.HuH ? ?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. |
|