Default to minus figure

[Jackewing98]

Hello

I am trying to automatically default some cells to a minus figure. I have seen you could do an ABS formula but wondered if there was an easier way? I only want to do some cells in the column as the others are positive figures.

Thanks

 

[admiral100]

Hi,

Perhaps this ?

=TEXT(A1,"-####0")*1

 

[Joe4]

If you want something automated, you could do a VBA approach, but we would need more information, such as:
- Would this run on existing data, or should data automatically updated as it is being entered?
- What range of data would this apply to (certain columns or rows)?
- What is the logic for determining which entries to change and which to leave alone?

 

[Jackewing98]

Hi

It is for a mortgage comparison calculator I put together. At the moment if there is an incentive such as Cashback you would put this in the "Other Fees" box e.g. -£500, however this means the Adviser has to remember to put a minus sign in. I was hoping to add in another row labelled "Cashback" which when a figure is input it would automatically be treated as a negative.

Book1
	B	C
3
4	Repayment Mortgages
5
6	Option One
7	Loan Amount (inc any fees added to loan)
8	Other Fees (not added to loan)
9	Term of Mortgage (in years)
10	Initial Interest Rate
11	Term of Initial Interest Rate (in months)
12	Monthly Mortgage Payment	#NUM!
13	Amortised Balance at end of Initial Interest Rate Period	#N/A
14
15	Option Two
16	Loan Amount (inc any fees added to loan)
17	Other Fees (not added to loan)
18	Term of Mortgage (in years)
19	Initial Interest Rate
20	Term of Initial Interest Rate (in months)
21	Monthly Mortgage Payment	#NUM!
22	Amortised Balance at end of Initial Interest Rate Period	#N/A
23
24
25	Option One Total Cost Over Intial Rate Period	#NUM!
26	Option Two Total Cost Over Initial Rate period	#NUM!
27	Difference	#NUM!
Repayment

Thanks
Stephen

 

[Joe4]

OK. Here is code that will automatically change a positive entry made in column C to negative if column B in that row is "Other Fees".
This code must be placed in the Sheet module to work correctly. To do this, go to the sheet you want it to apply on, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run if a single cell is updated
    If Target.Count > 1 Then Exit Sub

'   Look for an amount entered in column C
    If Target.Column = 3 Then
'       Check to see if entry in column B of that row is "Other Fees" and amount > 0
        If (Target.Offset(0, -1) = "Other Fees") And (Target > 0) Then
            Application.EnableEvents = False
            Target = Target * -1
            Application.EnableEvents = True
        End If
    End If
    
End Sub