Purpose: to key numbers in all cells as positive, however, one column should always be a true negative (many rows in all columns,it'd easier to just key as is & allow the format to automatically convert any number in that column into a negative) I could simply use the minus key each time, but when one is keying many numbers in a limited time frame it would be wonderful not to have the extra step (hitting minus key)
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Forgot to clarify Re: Change Positive to Negative
- Thread starter J1406
- Start date
If I understand you correctly, you want to key values into a column (say A) but have those values displayed as negative in another column (say B)
Whilst you could format column A as follows:
[RED](#,##0.00);#,##0.00_);0_)
which will show positive values as (123.45) and negative values as 123.45 it may be easier to include the following formula in column B
=-An (where n is the row number)
or =An*-1
HTH
I've since noticed that this post is a duplicate
_________________
BigC
Carp diem - opportunity knocks so often that it has raw knuckles!
This message was edited by BigC on 2002-10-31 00:46
Whilst you could format column A as follows:
[RED](#,##0.00);#,##0.00_);0_)
which will show positive values as (123.45) and negative values as 123.45 it may be easier to include the following formula in column B
=-An (where n is the row number)
or =An*-1
HTH
I've since noticed that this post is a duplicate
_________________
BigC
Carp diem - opportunity knocks so often that it has raw knuckles!
This message was edited by BigC on 2002-10-31 00:46
Upvote
0
NateO
Legend
- Joined
- Feb 17, 2002
- Messages
- 9,700
Howdy J, I think you'll want VBA for such a concept. Here's a worksheet procedure:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
'change both letters in [a:a] to the appropriate column letter
Application.EnableEvents = False
If Not Intersect(Target, [a:a]) Is Nothing Then _
Target = -Target
Application.EnableEvents = True
End Sub</pre><pre></pre>
Right click on the appropriate worksheet tab, click view code and paste the code above. Change the column letter as mentioned and see if this helps.
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-10-31 13:25
Private Sub Worksheet_Change(ByVal Target As Range)
'change both letters in [a:a] to the appropriate column letter
Application.EnableEvents = False
If Not Intersect(Target, [a:a]) Is Nothing Then _
Target = -Target
Application.EnableEvents = True
End Sub</pre><pre></pre>
Right click on the appropriate worksheet tab, click view code and paste the code above. Change the column letter as mentioned and see if this helps.
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-10-31 13:25
Upvote
0
