How do I make a column formatted for only Negative Numbers?

[Guest]

I'm trying to add-subtract various numbers. One column will be nothing but
withdrawals from an account, so I want every number to be negative, so that
if I do calculations with that column, they will always show as deductions.
Is there a way I can set up the cells in that column so that any number I
type in will show as a negative number, ie. a -xx? I can simply type in a
hyphen before, I know, but I like to type fast without even thinking about
that extra and somewhat difficult (for me) key-stroke. Thanks.

dc

 

[David Biddulph]

One option once you've entered all the numbers as positive is to put -1 in a
spare cell, select and copy, the select your column, edit/ paste special/
multiply.

If you just want that column to display as if they were negative numbers,
you could format as "-"General, and then when you want to do calculations
from that column you could subtract instead of adding.

 

[Stan Brown]

Mon, 3 Sep 2007 01:50:07 -0700 from DeeDeeCee

I'm trying to add-subtract various numbers. One column will be nothing but
withdrawals from an account, so I want every number to be negative, so that
if I do calculations with that column, they will always show as deductions.
Is there a way I can set up the cells in that column so that any number I
type in will show as a negative number, ie. a -xx?

Yes, with custom formatting, but I recommend against it. When you see
a column of negative numbers, sooner or later you'll type a minus
sign in front of one of them and then your computation will give a
wrong result.

 

[Gord Dibben]

I know I'm a day late here but if you're still watching............

You could use event code to change the typed numbers to real negatives as you
enter them.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'change a number to negative
If Target.Column <> 2 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
If Not Left(Target.Value, 1) = "-" Then
Application.EnableEvents = False
With Target
.Value = .Value * -1
End With
End If
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

As written it operates on column 2(B) only. Adjust to suit column number.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks to all 3; I got useful info from each.

ddc