I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any idea
on how to do this?

Any insight would be appreciated.

Thanks!

Just use the decimal or whole number options with a very small negative
number and the biggest negative number you will allow.

Or custom with =A1<0

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Courtney" wrote in message
...
I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as
negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any
idea
on how to do this?

Any insight would be appreciated.

Thanks!

In the cells they will be entering this negative number:
Data--Validation
Allow: Decimal
Data: Less than or equal to
Maximum: 0

NOTE: You can change DATA to Less than as needed.

--
John C


"Courtney" wrote:

I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any idea
on how to do this?

Any insight would be appreciated.

Thanks!

Thanks for the reply - that works if they enter a negative number, but can
you also make it to where if they enter it as a positive, instead of getting
an error message it automatically converts it to a negative number on what
they entered. Ex. If they entered an expense as $600 it would convert to
($600).

Thanks

"John C" wrote:

In the cells they will be entering this negative number:
Data--Validation
Allow: Decimal
Data: Less than or equal to
Maximum: 0

NOTE: You can change DATA to Less than as needed.

--
John C


"Courtney" wrote:

I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any idea
on how to do this?

Any insight would be appreciated.

Thanks!

May I suggest a different approach.
Assumptions, columns B, D, and F have revenue, columns C, E, and G have
expenses.
Select all cells (that will have a revenue or expense entered).
Data--Validation, Decimal, Greater than or Equal to, 0
Select the expense cells, go to Menu: Format--Cells--Number--Custom
I used the following:
[Red](#,##0.00)
A format for negative numbers is not needed, as we don't allow entry of
negative numbers.

Assuming Row 2 is the first set of data of expenses and revenues, my total
formula entered in cell H2 is as follows:
=SUM(B2,D2,F2)+SUM(C2,E2,G2)*-1

Then Edit--Fill--Down as needed.
--
John C


"Courtney" wrote:

Thanks for the reply - that works if they enter a negative number, but can
you also make it to where if they enter it as a positive, instead of getting
an error message it automatically converts it to a negative number on what
they entered. Ex. If they entered an expense as $600 it would convert to
($600).

Thanks

"John C" wrote:

In the cells they will be entering this negative number:
Data--Validation
Allow: Decimal
Data: Less than or equal to
Maximum: 0

NOTE: You can change DATA to Less than as needed.

--
John C


"Courtney" wrote:

I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any idea
on how to do this?

Any insight would be appreciated.

Thanks!

Thanks - I think this would be the same effect of changing all my
totals/formulas to automatically subtract expenses entered as positive
numbers - that might be the answer - was trying to look for an automatic way
to convert to a negative number to prevent some re-work, but might be
unavoidable. Appreciate the help though.

"John C" wrote:

May I suggest a different approach.
Assumptions, columns B, D, and F have revenue, columns C, E, and G have
expenses.
Select all cells (that will have a revenue or expense entered).
Data--Validation, Decimal, Greater than or Equal to, 0
Select the expense cells, go to Menu: Format--Cells--Number--Custom
I used the following:
[Red](#,##0.00)
A format for negative numbers is not needed, as we don't allow entry of
negative numbers.

Assuming Row 2 is the first set of data of expenses and revenues, my total
formula entered in cell H2 is as follows:
=SUM(B2,D2,F2)+SUM(C2,E2,G2)*-1

Then Edit--Fill--Down as needed.
--
John C


"Courtney" wrote:

Thanks for the reply - that works if they enter a negative number, but can
you also make it to where if they enter it as a positive, instead of getting
an error message it automatically converts it to a negative number on what
they entered. Ex. If they entered an expense as $600 it would convert to
($600).

Thanks

"John C" wrote:

In the cells they will be entering this negative number:
Data--Validation
Allow: Decimal
Data: Less than or equal to
Maximum: 0

NOTE: You can change DATA to Less than as needed.

--
John C


"Courtney" wrote:

I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any idea
on how to do this?

Any insight would be appreciated.

Thanks!

If you use datavalidationallowcustom and

=A1<0

then they can't type in a positive number, otherwise you would need
an event macro that executes when pressing enter

Replace A1 with the cell you want to validate

--


Regards,


Peo Sjoblom

"Courtney" wrote in message
...
I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as
negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any
idea
on how to do this?

Any insight would be appreciated.

Thanks!

How would you build the event macro? I think that is what I'm looking for -
I would like them to be able to enter either a positive or negative number
but in the end, convert all expenses to negatives to make it foolproof....

"Peo Sjoblom" wrote:

If you use datavalidationallowcustom and

=A1<0

then they can't type in a positive number, otherwise you would need
an event macro that executes when pressing enter

Replace A1 with the cell you want to validate

--


Regards,


Peo Sjoblom

"Courtney" wrote in message
...
I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as
negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any
idea
on how to do this?

Any insight would be appreciated.

Thanks!

This code will change positves to negatives upon entry.

If negative is entered it remains negative.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A100"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Left(cell.Value, 1) < "-" Then
cell.Value = cell.Value * -1
End If
Next cell
End If

ws_exit:
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. Edit the range to suit.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Wed, 16 Jul 2008 09:43:01 -0700, Courtney
wrote:

How would you build the event macro? I think that is what I'm looking for -
I would like them to be able to enter either a positive or negative number
but in the end, convert all expenses to negatives to make it foolproof....

"Peo Sjoblom" wrote:

If you use datavalidationallowcustom and

=A1<0

then they can't type in a positive number, otherwise you would need
an event macro that executes when pressing enter

Replace A1 with the cell you want to validate

--


Regards,


Peo Sjoblom

"Courtney" wrote in message
...
I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as
negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any
idea
on how to do this?

Any insight would be appreciated.

Thanks!

Thank you so much Gord - a couple of questions - sorry if they are
remedial...haven't done Macros in a while....

If I only want certain cells on certain worksheets to change to negative
numbers (or remain negative if entered that way) would I just change the
worksheet name and the range? Should I set up these prior to entering the
formula you have below?

Thanks for your help on this - just want to make sure I understand the code
below...




"Gord Dibben" wrote:

This code will change positves to negatives upon entry.

If negative is entered it remains negative.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A100"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Left(cell.Value, 1) < "-" Then
cell.Value = cell.Value * -1
End If
Next cell
End If

ws_exit:
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. Edit the range to suit.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Wed, 16 Jul 2008 09:43:01 -0700, Courtney
wrote:

How would you build the event macro? I think that is what I'm looking for -
I would like them to be able to enter either a positive or negative number
but in the end, convert all expenses to negatives to make it foolproof....

"Peo Sjoblom" wrote:

If you use datavalidationallowcustom and

=A1<0

then they can't type in a positive number, otherwise you would need
an event macro that executes when pressing enter

Replace A1 with the cell you want to validate

--


Regards,


Peo Sjoblom

"Courtney" wrote in message
...
I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as
negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any
idea
on how to do this?

Any insight would be appreciated.

Thanks!

First of all............what I posted is event code........not a formula

Second of all............what you do depends upon a few things.

Which cells? Which worksheets.......some or all? Different cells on each
worksheet?

Assuming the certain cells on certain worksheets are different you would copy
the code to each worksheet module and edit the WS_RANGE to suit like

Const WS_RANGE As String = "A1,B2,C3,D4,E5,F6"

Or............"A1:J10", "K11:T20", "U21:AD30"

If something else, please post back with a few more details on "certain"


Gord



On Wed, 16 Jul 2008 13:46:03 -0700, Courtney
wrote:

Thank you so much Gord - a couple of questions - sorry if they are
remedial...haven't done Macros in a while....

If I only want certain cells on certain worksheets to change to negative
numbers (or remain negative if entered that way) would I just change the
worksheet name and the range? Should I set up these prior to entering the
formula you have below?

Thanks for your help on this - just want to make sure I understand the code
below...




"Gord Dibben" wrote:

This code will change positves to negatives upon entry.

If negative is entered it remains negative.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A100"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Left(cell.Value, 1) < "-" Then
cell.Value = cell.Value * -1
End If
Next cell
End If

ws_exit:
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. Edit the range to suit.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Wed, 16 Jul 2008 09:43:01 -0700, Courtney
wrote:

How would you build the event macro? I think that is what I'm looking for -
I would like them to be able to enter either a positive or negative number
but in the end, convert all expenses to negatives to make it foolproof....

"Peo Sjoblom" wrote:

If you use datavalidationallowcustom and

=A1<0

then they can't type in a positive number, otherwise you would need
an event macro that executes when pressing enter

Replace A1 with the cell you want to validate

--


Regards,


Peo Sjoblom

"Courtney" wrote in message
...
I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as
negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any
idea
on how to do this?

Any insight would be appreciated.

Thanks!

To summarize:

To allow only negative numbers in the expense section:

1. Select the cells where you want to allow only negative numbers.
2. Go to the Data tab in the ribbon and click on Data Validation.
3. In the Data Validation dialog box, select "Custom" from the Allow drop-down list.
4. In the Formula box, enter the following formula: =A1<0 (assuming A1 is the first cell in the selected range).
5. Click OK to close the dialog box.

To automatically convert positive numbers to negative numbers:

1. Select the cells where you want to allow both positive and negative numbers.
2. In the formula bar, enter the following formula: =IF(A1<0,A1,-A1) (assuming A1 is the first cell in the selected range).
3. Press Enter to apply the formula to the cell.
4. Copy the formula to the rest of the cells in the range.