Situation with IF Formula

[Serenity99]

Okay, perhaps there is another way to do this. Here's what I am tryin
to do:

The form is an expense report. At the bottom of the form there is
grand total figure which represents the amount due back to th
employee.


Down the left side is a list of the various expense categories.

Going across, there are columns for Sunday through Saturday (say, A
through G5).

The column to the right of the Sunday through Saturday columns is
column in which the employee says whether or not the expense i
reimbursable to him. (If it is an expense that is not reimbursable t
him, it still has to be entered on the expense report as it is billabl
to clients). If an expense is not reimbursable to him, he puts an x i
the cell alongside the expense.

I want this "x" to change the number in the corresponding cell (any on
of A5 through G5) to a negative figure so it is deducted at the botto
from the amount reimbursable.

However, in writing this I realized this will not work because he ma
have two expenses in the same line that are not reimbursable to him an
only one cell in which to signify whether or not is remburable. Darn!

Any ideas how to do this

 

[Dave Peterson]

I think I'd try to use one row per expense. And just use two columns.

One for reimbursable and one for not.

If you trust the employee to put the X in the correct box, why not just have
them enter the data in correct spot.

 

[Serenity99]

*I think I'd try to use one row per expense. And just use two
columns.

One for reimbursable and one for not.

If you trust the employee to put the X in the correct box, why not
just have
them enter the data in correct spot.

Sounds easy, doesn't it! That's what I thought - until I found they
either forgot to fill in the box at all or filled it in wrongly.

 

[Serenity99]

*I think I'd try to use one row per expense. And just use two
columns.

One for reimbursable and one for not.

If you trust the employee to put the X in the correct box, why not
just have
them enter the data in correct spot.

*

Actually, the problem is not having one column or two, it is the fact
that I need to be able to change the other cell from a positive to a
negative based on whether there is an x in this column.

 

[Dave Peterson]

But if you had two columns, you could tell the users to enter positive values
and you could just subtract it at the end.

You could have an event macro swap the sign of the value whenever there's an X
in an adjacent cell, but maybe you could do what you want with some worksheet
formulas:

=SUMIF(A1:A30,"x",B1:B30)
(sums the X rows)

=SUMIF(A1:A30,"<>x",B1:B30)
(sums the non-x Rows)

But if you want to try the event code. I created a test worksheet. Column A
was were I typed my X's. Column C was where I entered the values.

Then I added this code by right clicking on the worksheet tab and selecting view
code.

(Paste this in)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Intersect(Me.Range("a:a,c:c"), Target)

If myRng Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False

For Each myCell In myRng.Cells
With myCell
'if it's in column C, user typed a number
If .Column = 3 Then
If Application.IsNumber(.Value) Then
If LCase(.Offset(0, -2).Value) = "x" Then
.Value = -Abs(.Value)
Else
.Value = Abs(.Value)
End If
End If
Else
'it has to be in A, user typed an X (or cleared it)
If Application.IsNumber(.Offset(0, 2).Value) Then
If LCase(.Value) = "x" Then
.Offset(0, 2).Value = -Abs(.Offset(0, 2).Value)
Else
.Offset(0, 2).Value = Abs(.Offset(0, 2).Value)
End If
End If
End If
End With
Next myCell

errHandler:
Application.EnableEvents = True

End Sub

===

You'll have to fiddle with these lines:

Set myRng = Intersect(Me.Range("a:a,c:c"), Target)

Use the range you want.

And this line:
If .Column = 3 Then

Change this to the column that gets the values. (Since there were only 2
columns, the other one gets the X's.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm