Entering Negative Numbers by typing Positive Numbers

[Tippy]

Hello,

I am doing by bank check book using Excel. I am tracking my expenses and deposits. Here is my problem: Every time I enter an expense amount, I have to enter a negative sign first. I want to be able to enter a positive number and have it register as a negative number in excel. How can I do this?

Thanks,
Tippy

 

[joy_666]

Hi,

You can use the formula in C2 as =CONCATENATE("-",B2)

I am assuming that you are entering your value in B2 as 200

So the result on C2 would be -200.

Is that ok?

 

[VoG]

Assuming that your expenses always go in the same column try this. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then 'Column D
    Application.EnableEvents = False
    If IsNumeric(Target.Value) Then Target.Value = -Target.Value
    Application.EnableEvents = True
End If
End Sub

Change the 4 to the number of your expenses column.

 

[Tippy]

I am entering the expenses like this:


I start out with a beginning balance of $500 in B2
In Cell A3 - I enter a descriptiion of a gas payment like Exxon
In Cell B3 - I type amount of expense - starting with a negative sign (this is exxon gas payment)

In Cell B5 - I have a Net subtotal of everything in B column which shows my ending balance. In this case my ending balance is $470.

I want to be able to type in 30, instead of -30 in cell B3. I don't want to keep typing in negative sign.

Thanks for your reply but I was hoping to get a different answer - perhaps a vba code to make it do this.

Thanks
Tippy

 

[VoG]

Is it just B3 that has to do this? If not please list all the cells that it should apply to (or give the range).

 

[Weaver]

What I'd do is have 2 columns, one for credits and one for debits. You can type positive numbers in the debit column and use a formula to subtract anything in the debit column and add anything in the credit column.

 

[Tippy]

Thanks so much VoG. It looks like it works. Appreciate your quick reply. Have a wonderful day.

Tippy