Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement? Or another solution?
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? -- mvh Ørjan Langbakk http://www.bergenpchjelp.no |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement? Or another solution?
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement? Or another solution?
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. 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. -- mvh Ørjan Langbakk http://www.bergenpchjelp.no |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement? Or another solution?
'-----------------------------------------------------------------
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) "Ørjan Langbakk" wrote in message ... 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? -- mvh Ørjan Langbakk http://www.bergenpchjelp.no |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SET statement tutorial | Excel Discussion (Misc queries) | |||
If statement | Excel Discussion (Misc queries) | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
Generating excel combinations | Excel Discussion (Misc queries) | |||
What statement to use? | Excel Worksheet Functions |