If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Data Validation
I have a column (D) to enter a dollar amount of a transaction. All
transactions are classifed in column A as either cost or revenue. I want to make sure that cells classified as cost in column A have a negative value and cells classified as revenue in column A have a positive value. Any help is appreciated. Thanks. |
#2
|
|||
|
|||
Conditional Data Validation
=OR(AND($A2="Cost",$D20),AND($A2="Revenue",$D20) )
-- __________________________________ HTH Bob "evoxfan" wrote in message news I have a column (D) to enter a dollar amount of a transaction. All transactions are classifed in column A as either cost or revenue. I want to make sure that cells classified as cost in column A have a negative value and cells classified as revenue in column A have a positive value. Any help is appreciated. Thanks. |
#3
|
|||
|
|||
Conditional Data Validation
You could do it with Conditional Formatting. Select D1 and go to
Format Conditional Formatting. Condition 1: =AND(A1="Cost",D10) Condition 2: =AND(A1="Revenue",D10) Make the pattern "Red" for both, that way if negative numbers are entered next to "Revenue", or positive numbers next to "Cost", they will highlighted in red. Fill down the cells as needed to copy the conditional formatting all the way down to the end of your data. --JP On Sep 18, 3:24*pm, evoxfan wrote: I have a column (D) to enter a dollar amount of a transaction. *All transactions are classifed in column A as either cost or revenue. *I want to make sure that cells classified as cost in column A have a negative value and cells classified as revenue in column A have a positive value. Any help is appreciated. Thanks. |
#4
|
|||
|
|||
Conditional Data Validation
So as not to annoy users with messages use event code.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 And Target.Offset(0, -3).Value = "cost" Then On Error GoTo endit Application.EnableEvents = False Target.Value = Target.Value * -1 End If endit: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that module. Alt + q to return to the Excel window. Enter all numbers in column D as positive and those with "cost" in column A will turn negative. Gord Dibben MS Excel MVP On Thu, 18 Sep 2008 12:24:01 -0700, evoxfan wrote: I have a column (D) to enter a dollar amount of a transaction. All transactions are classifed in column A as either cost or revenue. I want to make sure that cells classified as cost in column A have a negative value and cells classified as revenue in column A have a positive value. Any help is appreciated. Thanks. |
#5
|
|||
|
|||
Conditional Data Validation
I would suggest this revised edition if you want to add more "cost" and
"revenue" cells to column A Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo endit Application.EnableEvents = False If Target.Column = 4 And Target.Offset(0, -3).Value = "cost" Then Target.Value = Target.Value * -1 End If endit: Application.EnableEvents = True End Sub Gord On Thu, 18 Sep 2008 15:57:13 -0700, Gord Dibben gorddibbATshawDOTca wrote: So as not to annoy users with messages use event code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 And Target.Offset(0, -3).Value = "cost" Then On Error GoTo endit Application.EnableEvents = False Target.Value = Target.Value * -1 End If endit: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that module. Alt + q to return to the Excel window. Enter all numbers in column D as positive and those with "cost" in column A will turn negative. Gord Dibben MS Excel MVP On Thu, 18 Sep 2008 12:24:01 -0700, evoxfan wrote: I have a column (D) to enter a dollar amount of a transaction. All transactions are classifed in column A as either cost or revenue. I want to make sure that cells classified as cost in column A have a negative value and cells classified as revenue in column A have a positive value. Any help is appreciated. Thanks. |
Thread Tools | |
Display Modes | |
|
|