Having the contains of a cell always be negative

[BW]

I have a worksheet that has several cells that when data is entered will
always need to be negative. How can I enter the number without having to put
the minus sign in and have it come out as a negative.

 

[tim m]

Try this, hilight all the cells or column you want this for, right click,

'Format cells'----'custom' in the box where it says 'type' type in -######

 

[BW]

Tim M

This solved my problem, thank you very much for your quick response.

 

[BW]

This ended up working for the most part. The one problem that I ran into was
using this did get the number to enter as a negative, but when you do a sum
it is still considered as a postivie number. Are they any thoughts on how to
make it sum as if it is a negative number.

 

[Jim Thomlinson]

You problem is that the numbers only look negative. They are stored as
positive or negative so your sums are incorrect. You can us an array formula
to coerce the numbers prior to adding them...

=SUM(-ABS(A1:A10))

***NOTE that this is an array formula and as such you can not just commit it
with <Enter>. you need to use Ctrl + Shift + <Enter> *********

 

[Gord Dibben]

Changing a format does not change the underlying value of the cell.

You could use event code to do it in place as you enter a number.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
With Target
.Value = .Value * -1
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP