Excel YTD

Microsoft Office excel 2007 home & stude...
July 20, 2010 at 19:51:22
Specs: Windows 7
Hours Current Year to Date
Regular $880.00 keeps adding

Ok, so I cannot get this working. I want each time I change current $880.00 it will add in Year to date...so this pay stub is $880, next one is $900...and so on...please help he with the formula in excel.

See More: Excel YTD

July 21, 2010 at 04:03:25

That isn't much information to work on.

If you mean that you have $800 in one cell, say B2,
and you want to replace it with the next pay stub, say $750, but add the existing $800 automatically:
That cannot be done in one cell.

If you have the first pay in B2, the next in B3 and so on, then you could have the year to date in column C.
Put this in C2:
Note the $ signs
Drag the formula down alongside each new pay entry.

If you need more help, please provide more information about where you are entering the data, how many entries you have or plan to have and whether you want one cell to show year to date.
The more information you provide, the more likely you are to get a good solution.


Report •

July 21, 2010 at 06:38:54
Thank you! You answered my questions...it cannot be done in the same cell.

Report •

July 21, 2010 at 07:25:26

I didn't expect that you wanted to put the result in the same cell, so my answer was limited to what you can do with standard formulas, and standard data entry into a cell.

If you went with some Visual Basic as a macro, you could use a popup box to ask for the new data, then the macro reads the existing data and then places the sum of the old and new values in the cell.

This code uses the Change Selection event.
If the user selects the required cell (G1 in this example), an input box appears asking the user to enter the new pay stub value:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$G$1" Then
    'disable events
    Application.EnableEvents = False
    Dim dblInp As Double
    'ask user for value to add
    dblInp = InputBox("Enter the latest pay stub amount", _
            "Cell G1 Update")
    Target.Value = Target.Value + CDbl(dblInp)
End If
'reenable events
Application.EnableEvents = True
Exit Sub
'error handler
'reenable events
Application.EnableEvents = True
End Sub

To use this right-click on the Worksheet name tab and select View Code.
Enter the code in the large Visual Basic window.
Click Save from the Visual Basic menu bar.
Use Alt+f11 to return to the main Excel window, and test it by clicking on Cell G1


Report •

Related Solutions

July 22, 2010 at 07:23:48
WOW! Thank you...I will try it...don't quite understand it but I sure will try it. Thank you!

Report •

Ask Question