How to enter a positive number as a negative

[DashboardNovice]

I want to enter any positive number, but when I press Enter, I would like for Excel to convert it to a negative number so that the negative number is stored in the cell.

For example, in cell A1 I would enter 3. But the value of A1 would be -3, so that if I multiply any number against A1, I am multiplying any number against -3, even though I entered positive 3 originally in A1.

I tried custom number format but that didn't work. It only changes what appears on the "surface" and not what is really in the cell.

 

[Hui]

Two questions

Is it just cell A3 or other cells as well ?

What if you want a positive number or will you always want negative numbers ?

 

[Somendra Misra]

Hi,

I think what you are trying to do is not possible without macro. Try below code in Sheet where you want these change. I had assume the column is A and rows where you need to bring this change is 1:1000.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
Application.EnableEvents = False
    a = Target
    Target = -1 * a
Application.EnableEvents = True
End If


End Sub

Regards,

 

[bobhc]

Good day Somendra

Sudipto's suggestion does work

 

[Somendra Misra]

Hi Bob,

See OP requirement

For example, in cell A1 I would enter 3. But the value of A1 would be -3, so that if I multiply any number against A1, I am multiplying any number against -3, even though I entered positive 3 originally in A1.

I tried custom number format but that didn't work. It only changes what appears on the "surface" and not what is really in the cell.

Regards,

 

[sudipto3003]

hi,
i have deleted my previous post as it not fulfil the requirement of dashboard...sorry
we can do one thing here..
type -1 anywhere in excel>copy>now select the required cells>paste special>select n"all" and "multiply">ok

 

[bobhc]

Hi Somendra, on my way to the optometrist right now....

 

[DashboardNovice]

Two questions

Is it just cell A3 or other cells as well ?

What if you want a positive number or will you always want negative numbers ?

Nothing to do with cell A3. I am using A1 as an example. Instead of 3, let's say I want to enter 8. So when I enter 8 in cell A1, Excel would accept it as -8. If I enter -8, excel would accept it as 8. So whatever I enter in cell A1 (just that one cell), Excel will take the opposite and that will be the final value in cell A1.

 

[NARAYANK991]

Hi ,

You can make Misra's code a little more robust by :

Private Sub Worksheet_Change(ByVal Target As Range)
            If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
                   If VBA.IsNumeric(Target) Then
                      Application.EnableEvents = False
                      Target = -(Target)
                      Application.EnableEvents = True
                   End If
            End If
End Sub

Narayan

 

[chssundeep]

I am newbie to excel..........My solution to this is

In B1 enter -1 and in C1 multiply A1XB1 so that you will get -ve values automatically (B1 can be dragged down so that you need not enter -1 manually)

after entering values in A row you can hide A and B rows