software

I have a cell, with a value that's negative, until another cell is
changed from quot;noquot; to quot;yesquot; - what I would like is that the original cell
changes value from lt;negative valuegt; to lt;positive valuegt; when I change
the quot;yes/noquot; 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 quot;noquot;
- when I receive payment, I change the quot;noquot; to a quot;yesquot;, 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 lt;somethinggt;, I get a corresponding result in colum D, row
lt;same somethinggt;?

--
mvh
Ørjan Langbakk
www.bergenpchjelp.no

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

=IF(G1=quot;yesquot;,-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.

PeteDen 18.05.2006 02:23, skriblet Pete_UK følgende:
gt; I think you would need another column to do this - insert a new column
gt; E and in E1 enter this formula:
gt;
gt; =IF(G1=quot;yesquot;,-D1,D1)
gt;
gt; and copy this formula down for as many rows as you have data. You can
gt; hide column D, so that your sheet has the same look as before, but you
gt; would enter Yes or No into column G now instead of F.
gt;
gt; Hope this helps.

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 quot;yesquot; of quot;noquot; - and then another value in D12, and
one in F12 saying quot;yesquot; or quot;noquot;.

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.

--
mvh
Ørjan Langbakk
www.bergenpchjelp.no

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = quot;yesquot; 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)

quot;Ørjan Langbakkquot; gt; wrote in message
...
gt; I have a cell, with a value that's negative, until another cell is
gt; changed from quot;noquot; to quot;yesquot; - what I would like is that the original cell
gt; changes value from lt;negative valuegt; to lt;positive valuegt; when I change
gt; the quot;yes/noquot; cell.
gt;
gt; Quick example:
gt;
gt; In column D I have several values, each originally negative (these are
gt; payments not yet received) - and marked in column F (same row) with quot;noquot;
gt; - when I receive payment, I change the quot;noquot; to a quot;yesquot;, and manually
gt; changes the corresponding cell in the same row to a positive value.
gt;
gt; Is it possible to automate this? So that when I change the value in
gt; column F, row lt;somethinggt;, I get a corresponding result in colum D, row
gt; lt;same somethinggt;?
gt;
gt; --
gt; mvh
gt; Ørjan Langbakk
gt; www.bergenpchjelp.no