Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Need formula for how to create a month-to-date

0

 I have created a report in Excel that has the following:

 1st Column    ----   2nd Column    ----    3rd Column

  Menu Items  ----    Daily                ----     MTD

  Food                      $50.00                      $1,500.00

  Liquor                   $125.00                      $2,500.00

  Beer                      $500.00                     $1,500.00

   Wine                     $250.00                     $5,000.00

  My issue is:  I enjoy making forms and reports.  I am uneducated as far as using formulas.  I was able to google and get some answers that helped me with the Daily, but am lost on how to create a formula for MTD.

  The 2nd Column is entered daily.  Then the next day, I click on Clear Daily Contents and start anew.  I would like the MTD Column to be a constant running total and then, at the end of the month, have a Clear MTD Contents.

Answer
Discuss

Discussion

Where is the data for the MTD column located and how does it look? You should probablye be able to use a SUMIF() or SUMIFS() function depending on how the source data is setup.
don (rep: 1989) Oct 14, '17 at 12:39 pm
I can see where you get your calories lol:
If you wish to clear the daily column daily and retain the monthly total no worksheet function can do the job. You will need VBA. But that system would be very error prone because you never know what's included in the total.The better way would be to record the daily sums somewhere. Consider a column for every day, all of them hidden exept fro the current one. And/or a separate worksheet.
The other thing to consider is the definition of the month which becomes relevant as soon as you have more than one. You can solve that problem by having a date on your worksheet somewhere in some form. But before you can reference that date you got to know where it is, in which cell. And in which cell is the MTD total?
In other words, your current problem isn't with formulas. It's with worksheet and system design. When you finish, attach the worksheet with your question.
Variatus (rep: 4889) Oct 14, '17 at 8:56 pm
Add to Discussion

Answers

0

The problem is that Formulas can't do circular references and the formula would have to be MTD = MTD+Daily

VBA to the rescue. In that worksheet's code pane

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Count <> 1 Then Exit Sub
   If Not Intersect(Target, Range("B:B")) Is Nothing Then SumMTD Target
End Sub

Private Sub SumMTD(Target As Range)
   With Target
      .Offset(0, 1) = .Offset(0, 1) + .Value
   End With
End Sub
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login