Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then else needed in Excel 2007
Ok, here's a reall good one: Here is what I need:
Cell C3 may or may not contain text. If it does, then I want cell C4's numeric data (which is currently a positive number) to be a negative number instead. Example: C3=CR C4=100.00 I want cell C4 to be -100.00 instead of 100.00 Does this make sense? Any Excel heads out there that can help will be GREATLY appreciated. CG Right now I have -- CLG |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then else needed in Excel 2007
Right click sheet tabview codeinsert this.
Private Sub Worksheet_Change(ByVal Target As Range) 'On Error Resume Next If Target.Address < Range("c3").Address Then Exit Sub If Not IsNumeric(Target) Then If IsNumeric(Target.Offset(1)) Then Target.Offset(1) = -Abs(Target.Offset(1)) End If End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Legal Learning" wrote in message ... Ok, here's a reall good one: Here is what I need: Cell C3 may or may not contain text. If it does, then I want cell C4's numeric data (which is currently a positive number) to be a negative number instead. Example: C3=CR C4=100.00 I want cell C4 to be -100.00 instead of 100.00 Does this make sense? Any Excel heads out there that can help will be GREATLY appreciated. CG Right now I have -- CLG |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then else needed in Excel 2007
A formula cannot change the value in another cell
You could put this into C5: =IF(ISTEXT(C3),-C4,C4) The code below may help. Copy it; right click the worksheet tab and select View Code; paste the subroutine But it has no way of knowing it the value has been changed previously Sub Worksheet_change(ByVal Target As Range) If Target.Address < Range("C3").Address Then Exit Sub If WorksheetFunction.IsText(Target) Then Range("c4") = Range("C4") * -1 Else Range("c4") = Range("C4") End If End Sub best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Legal Learning" wrote in message ... Ok, here's a reall good one: Here is what I need: Cell C3 may or may not contain text. If it does, then I want cell C4's numeric data (which is currently a positive number) to be a negative number instead. Example: C3=CR C4=100.00 I want cell C4 to be -100.00 instead of 100.00 Does this make sense? Any Excel heads out there that can help will be GREATLY appreciated. CG Right now I have -- CLG |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then else needed in Excel 2007
--If you have a formula in C4 which returns 100; then you can modify the
formula to validate the entry in C3 and return a positive or negative number.... --If there is no formula in C4; then you would need to make use of the worksheet event to do this.Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("C3")) Is Nothing Then Application.EnableEvents = False Range("C4") = Abs(Range("C4")) If IsNumeric(Range("C3")) = False Then Range("C4") = -(Abs(Range("C4"))) Application.EnableEvents = True End If End Sub If this post helps click Yes --------------- Jacob Skaria "Legal Learning" wrote: Ok, here's a reall good one: Here is what I need: Cell C3 may or may not contain text. If it does, then I want cell C4's numeric data (which is currently a positive number) to be a negative number instead. Example: C3=CR C4=100.00 I want cell C4 to be -100.00 instead of 100.00 Does this make sense? Any Excel heads out there that can help will be GREATLY appreciated. CG Right now I have -- CLG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent help needed!!! Saving in Excel 2007 | Excel Discussion (Misc queries) | |||
Testers needed for code page : create and mail PDF files in Excel 2007 with the MS PDF add-in | Excel Discussion (Misc queries) | |||
Excel 2007 Right Hand Mouse button problem - Help Needed | Excel Discussion (Misc queries) | |||
Help needed with creating a Formula in Excel 2007 please ? | Excel Worksheet Functions | |||
lower case to Proper help needed in Excel Office 2007 | Excel Worksheet Functions |