IF statement? Or another solution?

Ø

Ørjan Langbakk

I have a cell, with a value that's negative, until another cell is
changed from "no" to "yes" - what I would like is that the original cell
changes value from <negative value> to <positive value> when I change
the "yes/no" cell.

Quick example:

In column D I have several values, each originally negative (these are
payments not yet received) - and marked in column F (same row) with "no"
- when I receive payment, I change the "no" to a "yes", and manually
changes the corresponding cell in the same row to a positive value.

Is it possible to automate this? So that when I change the value in
column F, row <something>, I get a corresponding result in colum D, row
<same something>?
 
P

Pete_UK

I think you would need another column to do this - insert a new column
E and in E1 enter this formula:

=IF(G1="yes",-D1,D1)

and copy this formula down for as many rows as you have data. You can
hide column D, so that your sheet has the same look as before, but you
would enter Yes or No into column G now instead of F.

Hope this helps.

Pete
 
Ø

Ørjan Langbakk

Den 18.05.2006 02:23, skriblet Pete_UK følgende:
I think you would need another column to do this - insert a new column
E and in E1 enter this formula:

=IF(G1="yes",-D1,D1)

and copy this formula down for as many rows as you have data. You can
hide column D, so that your sheet has the same look as before, but you
would enter Yes or No into column G now instead of F.

Hope this helps.
Click to expand...

Well, no, not really. :) You see, I already have this info. But it
doesn't really do what I want (or, yes, it does, but not to the letter).

First of all, I'd like to be able to avoid another column, but I guess I
can live with that - the problem is, the number that changes from
negative to positive, isn't in _one_ cell - it's in several cells in one
column - and then the formula would have to change to reflect that, and
then I can just as well make the changes manually.

Point is - if I have a value in, say, D11, then I have a corresponding
value in F11, saying "yes" of "no" - and then another value in D12, and
one in F12 saying "yes" or "no".

I would presume it would be possible to write som VBA or something to do
this, but my knowledge of VBA is silch, so any further help would be
appreciated.
 
B

Bob Phillips

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "F:F"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "yes" Then
.Offset(0, -2).Value = Abs(.Offset(0, -2).Value)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Top