Howto - Excel IF Statement to dynamically update a cell

[jsemon]

Here's what I have
I have a sheet that I use to keep track of my finances. In this sheet, I have the amount of the payment I will make (I2-I11) and a total of the payments being made (I14). In another column (F2-F11) I have the option to select weather or not the payment has been scheduled.

What I would like to do
I would like to have another cell, say F14... that will tell me the total of payments to be made after I have selected that a payment is scheduled. So for instance, if my total payment to be made is $800, and I schedule the creditor listed on row 2 for a payment of $100, I want cell I14 to display the original $800 and cell F14 to display $700 and update as I make payments.

Is this possible? Can anybody help me out with accomplishing this. I'm not really an excel person so any help is greatly appreciated.

 

[repairman615]

Hello,

Welcome to the Message Board!

Have a look at the below and see if it will help you out.



Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>I</TH><TH>J</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ddd9c3; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Pmts.</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ddd9c3; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Sch.</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">758.11</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Yes</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">35.48</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">No</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">11.25</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Yes</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">125.56</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Yes</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">378.31</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">No</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">44.45</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">No</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">12.89</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">No</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">12.43</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">No</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">56.2</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Yes</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">88.45</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">No</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ddd9c3; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Total Pmts.</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ddd9c3; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Balance Due</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid; TEXT-DECORATION: underline">1523.13</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fac090; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid; TEXT-DECORATION: underline">572.01</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>I14</TH><TD style="TEXT-ALIGN: left">=SUM(I2:I11)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>J14</TH><TD style="TEXT-ALIGN: left">=I14-SUMIF(J2:J11,"Yes",I2:I11)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[jsemon]

Wow! That was perfect... Exactly what I was looking for! Thanks man!