Positive or Negative Value in a Cell if a Nearby Cell is One of Two Values

[MntnrMark]

I have a column, say its Column C, that has multiple rows showing either a "P" (for Positive) or "N" (for Negative) impact on endeavors described in the various Rows of Column B.

I then type a numerical value between 1 and 5 in Column D to reflect the severity/lack thereof of the impact.

I want the value in Column D to be a positive value if "P" shows up to the left in Column C, or a negative value (meaning it displays as -1, -2, -3, etc.) if "N" shows up to the left in Column C.

What's the easiest way to do that? I did it but in a very cumbersome way and would like to simplify the method.

Thanks.

 

[DRSteele]

The use of Conditional Formatting will serve the purpose. I set up a CF rule to have the cell display as a number format that has a dash in front of it if the cell in the Impact column is an "N".

The format chosen is for Number, where a custom format like this is used: "-"0 So just to be clear, when you type numbers in the cells in ColumnD, the Conditional Formatting rule will be applied if the corresponding cell in ColumnC is an "N".

Note that the numbers you see are not negatives, they just appear to be negatives - so don't rely on them for any calculations.

MrExcel posts19.xlsx
	C	D
2	IMPACT	SEVERITY
3	N	-1
4	P	3
5	N	-5
6	N	-4
7	P	2
8	P	2
9	N	-1
10	P	3
11	P	3
12	P	4
Sheet12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:D12	Expression	=C3="N"	text	NO

 

[MntnrMark]

Thanks, DRSteele. I'm sorry - should have been more clear; I need the numbers in Column D to be used in subsequent calculations, also.

 

[DRSteele]

In that case you will need a helper column.

 

[Joe4]

In that case you will need a helper column.

Not necessarily, if you use VBA.

Which value are you entering first? The letter in column C or the number in column D?
Will it always be in the same order?
Can you change the letter in columns C after both column have already been populated?

 

[jtakw]

Thanks, DRSteele. I'm sorry - should have been more clear; I need the numbers in Column D to be used in subsequent calculations, also.

Hi,

Or, you can use Column C value of "N" as a condition to change those values to an actual negative in your formulas for your subsequent calculations.

 

[MntnrMark]

Not necessarily, if you use VBA.

Which value are you entering first? The letter in column C or the number in column D?
Will it always be in the same order?
Can you change the letter in columns C after both column have already been populated?

I enter either a "P" or an "N" in Column C first, so if its a "P" then Column D needs to be a positive 1, 2, 3, 4 or 5 (I enter that number manually); if its an "N" that is entered in Column C, Column D needs to display as -1, -2, -3, -4 or -5 (which I enter manually as a 1, 2, 3, 4 or 5) . . . AND . . . be usable in subsequent calculations as either a positive or a negative number.

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code" and paste this code in the resulting VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   Check to see if any updates in column D
    Set rng = Intersect(Target, Range("D:D"))
    
'   Exit if no updates to column D
    If rng Is Nothing Then Exit Sub
    
'   Loop through cells updated in column D
    Application.EnableEvents = False
    For Each cell In rng
        If IsNumeric(cell) Then
            Select Case UCase(cell.Offset(0, -1))
                Case "P"
                    cell = Abs(cell)
                Case "N"
                    cell = Abs(cell) * -1
                Case Else
                    cell.ClearContents
                    MsgBox "You must populate column D with a P or N first!"
            End Select
        Else
            cell.ClearContents
            MsgBox "Please only enter numbers in column D!"
        End If
    Next cell
    Application.EnableEvents = True
    
End Sub

 

[MntnrMark]

Right-click on the sheet tab name at the bottom of the screen, select "View Code" and paste this code in the resulting VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   Check to see if any updates in column D
    Set rng = Intersect(Target, Range("D:D"))
   
'   Exit if no updates to column D
    If rng Is Nothing Then Exit Sub
   
'   Loop through cells updated in column D
    Application.EnableEvents = False
    For Each cell In rng
        If IsNumeric(cell) Then
            Select Case UCase(cell.Offset(0, -1))
                Case "P"
                    cell = Abs(cell)
                Case "N"
                    cell = Abs(cell) * -1
                Case Else
                    cell.ClearContents
                    MsgBox "You must populate column D with a P or N first!"
            End Select
        Else
            cell.ClearContents
            MsgBox "Please only enter numbers in column D!"
        End If
    Next cell
    Application.EnableEvents = True
   
End Sub

Thanks, Joe! One last thing - my example in the original question used Column C as where the "P" or "N" was placed, and Column D as where the number 1, 2, 3, 4 or 5 was keyed in.

How would I need to change the above VBA code to make Column I the column where I enter the "P" or the "N" and Columns K, M, O and Q be the columns where the same action as in your VBA code for Column D above would occur? In other words, I want to put "P" or "N" in Column I, then the numbers in Columns K, M, O and Q to change to negative in there is an "N" in Column I. (I tried manipulating your code above, but being largely a novice with VBA code but couldn't get it work.)

Thanks.

 

[Joe4]

Thanks, Joe! One last thing - my example in the original question used Column C as where the "P" or "N" was placed, and Column D as where the number 1, 2, 3, 4 or 5 was keyed in.

How would I need to change the above VBA code to make Column I the column where I enter the "P" or the "N" and Columns K, M, O and Q be the columns where the same action as in your VBA code for Column D above would occur? In other words, I want to put "P" or "N" in Column I, then the numbers in Columns K, M, O and Q to change to negative in there is an "N" in Column I. (I tried manipulating your code above, but being largely a novice with VBA code but couldn't get it work.)

Thanks.

When posting your questions, it is usually best to post the actual question you have, and not a "simplified" version of it, unless you are really comfortable manipulating code yourself. No need to simplify it on our account.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim col As Long
    
'   Only run if a single cell is updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if update in columns K, M, O, or Q
    col = Target.Column
    If (col = 11) Or (col = 13) Or (col = 15) Or (col = 17) Then
        Application.EnableEvents = False
'       Check to see if entry is numeric
        If IsNumeric(Target.Value) Then
'           Check entry in column I
            Select Case Cells(Target.Row, "I")
                Case "P"
                    Target = Abs(Target)
                Case "N"
                    Target = Abs(Target) * -1
                Case Else
                    Target.ClearContents
                    MsgBox "You must populate column I with a P or N first!"
            End Select
        Else
            Target.ClearContents
            MsgBox "Please only enter numbers in column K, M, O, and Q!"
        End If
        Application.EnableEvents = True
    End If
    
End Sub