Formula or Macro

[data808]

Hello,
I have this spreadsheet that I use for cashiering and it has columns for taking money. The columns are in this order:

Amount Due
Check
Charge
Cash Given
Change

I would like these columns to add everything up so the Change column tells me how much to give back to the customer. The thing is, when the customer writes a check or charges, I want the Change column to say 0 because when the customer pays by check or charge, there is never change to give back. Oh and in the Check and Charge column, I place an "x" in it when it is used. So when the "x" is placed in that column that's when I want the Change column to input a 0.

Thanks for your help.

 

[Trebor76]

Hi data808,

Assuming the headings are in Row 1, try the following formula in cell E2:

=IF(LEN(D2)>0,D2-A2,0)

HTH

Robert

 

[data808]

Hey Robert,

thanks for the reply. I tried the formula but it seems to just show the word VALUE# and the cell freezes up.

Amount Due = column E
Check = column F
Charge = column G
Cash Given = column H
Change = column I
Date Stamp = column J

Sorry I forgot to mention that I have a protection on the spreadsheet and a macro that does this date stamp of the current date. Heres the macro thats running:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
If Not IsDate(.Value) Then
ActiveSheet.Unprotect Password:="judiciary"
.NumberFormat = "mmm dd yyyy"
.Value = Date
ActiveSheet.Protect Password:="judiciary"
'DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End With
Application.EnableEvents = True
End If
End With
End Sub

 

[Trebor76]

The formula logic is correct, just needs adjusting for different columns i.e. put the following formula...

=IF(LEN(H2)>0,H2-E2,0)

...into cell I2 (as long as the data is in Row 2 - if not change to suit).

Robert

 

[data808]

Thanks Robert. It works perfectly. I was wondering if you knew anything about that macro I posted about the date stamp? Right now its set up so if column A is filled with text then the date stamp is filled into column J. Do you know how to make it so that if I enter text or numbers in column A, B, C, or D then the date stamp will be entered in column J?

Thanks for all your help on this.

 

[Trebor76]

You don't need a macro to do that.

Put this formula...

=IF(LEN(A2)+LEN(B2)+LEN(C2)+LEN(D2)=0,"",NOW())

...into cell J2, format the cell as required (i.e. "mmm dd yyyy") and then fill down as required.

If you really want to use the macro, try changing this line...

If Not Intersect(Range("A:A"), .Cells) Is Nothing Then

...to this:

If Not Intersect(Range("A:D"), .Cells) Is Nothing Then

HTH

Robert

 

[data808]

I changed the line to this

If Not Intersect(Range("A:D"), .Cells) Is Nothing Then

but it seems this line is messing it up

With .Offset(0, 9)

its making the date go into different cells further down because its positioning itself 9 cells down from where the text is entered.

 

[Trebor76]

See if changing this line...

With .Offset(0, 9)

...to this:

With Range("J" & Target.Row)

does the trick.

HTH

Robert

 

[data808]

Hey Robert,
thanks so much for the help. Everything works perfect.

I was wondering if you knew how to make the date turn red if its in the wrong sheet tab? I have this other spreadsheet we use as a phone log and I have 12 sheet tabs. Each represent the months of the year. I named them like this:

Jan, Feb, Mar, Apr, etc...

I have the same date stamp macro going for this spreadsheet and my staff needs to click on the correct tab each month when answering phone calls as the month changes. Sometimes they forget to change to the new month and the date stamp macro still works but I have to go back and copy and paste all the calls in the current month. I figured if the date stamp were to fill in the cell with red color text to let the clerk know that its the wrong month they are in, then this will be like an alarm for them to change tabs to the current month.

Thanks for you help. You have been a life saver.

 

[Trebor76]

I'm not a fan of having separate tabs. It's much easier to have all the data in one tab with a month column that can be filtered on.

That said, try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("A:D"), .Cells) Is Nothing Then
            Application.EnableEvents = False
            With Range("J" & Target.Row)
                If Not IsDate(.Value) Then
                    ActiveSheet.Unprotect Password:="judiciary"
                    .NumberFormat = "mmm dd yyyy"
                    .Value = Date
                    If ActiveSheet.Name <> Format(.Value, "mmm") Then
                        .Font.Color = RGB(255, 0, 0) 'Red
                    Else
                        .Font.Color = RGB(0, 0, 0) 'Black
                    End If
                    ActiveSheet.Protect Password:="judiciary"
                    'DrawingObjects:=True, Contents:=True, Scenarios:=True
                End If
            End With
            Application.EnableEvents = True
        End If
    End With
End Sub

Robert