Using VBA to Add Daily Change Orders to Budget

[wjmay1961]

I'm looking for some VBA help,

I have date values in Column A, Planned Budget Spend to Date in Column B (all on sheet1), and I would like to sum the daily change orders on sheet2 and add pass those values to Column C on sheet1 and sum those to the corresponding row (matching date).

[TABLE="width: 339"]
<colgroup><col><col><col></colgroup><tbody>[TR]

<colgroup><col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="mso-width-source:userset;mso-width-alt:4315;width:89pt" width="118"> <col style="mso-width-source:userset;mso-width-alt:5156;width:106pt" width="141"> </colgroup><tbody>
[TD="class: xl72, width: 80"]Date[/TD]
[TD="class: xl71, width: 118"]Cumm Plan $
[/TD]
[TD="class: xl73, width: 141"]Cumm. Plan + Change Orders
[/TD]

[TD="class: xl68, align: right"]03/01/13[/TD]
[TD="class: xl69, align: right"]$1,102,041 [/TD]
[TD="class: xl70, align: right"]$1,602,041 [/TD]

[TD="class: xl68, align: right"]03/02/13[/TD]
[TD="class: xl69, align: right"]$2,204,082 [/TD]
[TD="class: xl70, align: right"]$2,954,082 [/TD]

[TD="class: xl68, align: right"]03/03/13[/TD]
[TD="class: xl69, align: right"]$3,306,122 [/TD]
[TD="class: xl70, align: right"]$4,056,122 [/TD]

[TD="class: xl68, align: right"]03/04/13[/TD]
[TD="class: xl69, align: right"]$4,408,163 [/TD]
[TD="class: xl70, align: right"]$5,158,163 [/TD]

</tbody>

[/TR]
[TR]

[/TR]
[TR]

[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[justwitt]

Are you sure you need to use VBA for this.

If I understand correctly you just want to be able to show the Daily change order values on the first tab corresponding to the date in column A on the first tab. I would think that could be accomplished with a SumIf function.

If your tab name for the Daily Change Orders is "Daily Change Orders" and your dates are in column A and your sum is in column B on that tab then on your first tab in column C you would want (in row 2 for instance)
=Sumif('Daily Change Orders'!$A:$A, A2, 'Daily Change Orders'!$B:$B)

That would give that dates Daily change orders, if you want the value to be cumulative (year to date for instance) you could change it to

=Sumif('Daily Change Orders'!$A:$A, "<=" & A2, 'Daily Change Orders'!$B:$B)

 

[wjmay1961]

Thank you for your response, however there is more to this worksheet then what I posted. The Change notices on the project can exceed 500 in a month and each individual item and its value is logged on sheet 2. I have coding that runs the most current duration with the planned budget. It then looks at sheet2 and sums the daily totals and places them in Column D based on the matching day. Column C runs a cumulative daily total which sums the previous days+current change orders and all previous ones.. Also I have a graph which is using the offset feature to update based on the last row in the data set.

 

[wjmay1961]

I have included the spreadsheet showing the formulas

<tbody>
[TD="class: xl76"]Date[/TD]
[TD="class: xl75, width: 120"]Cummulative Plan $[/TD]
[TD="class: xl77, width: 170"]Cumm. Plan + Change Orders[/TD]
[TD="class: xl74, width: 202"]Daily AWR Values[/TD]

[TD="class: xl71"]41334[/TD]
[TD="class: xl72"]1102040.82[/TD]
[TD="class: xl73"]=SUM($G$13:G13)+D13[/TD]
[TD="class: xl69"]=VLOOKUP(C13,AWR_Log,2,FALSE)[/TD]

[TD="class: xl71"]41335[/TD]
[TD="class: xl72"]2204081.64[/TD]
[TD="class: xl73"]=SUM($G$13:G14)+D14[/TD]
[TD="class: xl69"]=VLOOKUP(C14,AWR_Log,2,FALSE)[/TD]

[TD="class: xl71"]41336[/TD]
[TD="class: xl72"]3306122.46[/TD]
[TD="class: xl73"]=SUM($G$13:G15)+D15[/TD]
[TD="class: xl69"]=VLOOKUP(C15,AWR_Log,2,FALSE)[/TD]

[TD="class: xl71"]41337[/TD]
[TD="class: xl72"]4408163.28[/TD]
[TD="class: xl73"]=SUM($G$13:G16)+D16[/TD]
[TD="class: xl69"]=VLOOKUP(C16,AWR_Log,2,FALSE)[/TD]

[TD="class: xl71"]41338[/TD]
[TD="class: xl72"]5510204.1[/TD]
[TD="class: xl73"]=SUM($G$13:G17)+D17[/TD]
[TD="class: xl69"]=VLOOKUP(C17,AWR_Log,2,FALSE)[/TD]

[TD="class: xl71"]41339[/TD]
[TD="class: xl72"]6612244.92[/TD]
[TD="class: xl73"]=SUM($G$13:G18)+D18[/TD]
[TD="class: xl69"]=VLOOKUP(C18,AWR_Log,2,FALSE)[/TD]

[TD="class: xl71"]41340[/TD]
[TD="class: xl72"]7714285.74[/TD]
[TD="class: xl73"]=SUM($G$13:G19)+D19[/TD]
[TD="class: xl69"]=VLOOKUP(C19,AWR_Log,2,FALSE)[/TD]

[TD="class: xl71"]41341[/TD]
[TD="class: xl72"]8816326.56[/TD]
[TD="class: xl73"]=SUM($G$13:G20)+D20[/TD]
[TD="class: xl69"]=VLOOKUP(C20,AWR_Log,2,FALSE)[/TD]

[TD="class: xl71"]41342[/TD]
[TD="class: xl72"]9918367.38[/TD]
[TD="class: xl73"]=SUM($G$13:G21)+D21[/TD]
[TD="class: xl69"]=VLOOKUP(C21,AWR_Log,2,FALSE)[/TD]

[TD="class: xl71"]41343[/TD]
[TD="class: xl72"]11020408.2[/TD]
[TD="class: xl73"]=SUM($G$13:G22)+D22[/TD]
[TD="class: xl69"]=VLOOKUP(C22,AWR_Log,2,FALSE)[/TD]

</tbody>