Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop entry of data that causes a negative value from a formula
Excel 2002. I tried to use "Validation" on the "Data" menu but that only
works on entered data. I have a formula in a cell and if the answer becomes a negative number I want it to stop the user and "force" them to fix it. I used an IF condition but that only displays a message. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop entry of data that causes a negative value from a formula
Maybe you could fix the formula yourself:
=max(0,yourformula) if the cell had to be non-negative. Or you could put the message in that same cell... =if(yourformula<0,"Please fix cells x, y, z",yourformula) That might be enough to make it so that they can't advance further. Other than that, you could add a worksheet event that popped up a message each time the worksheet recalculated and saw that the cell was negative: Option Explicit Private Sub Worksheet_Calculate() Dim myCell As Range Set myCell = Me.Range("a1") If myCell.Value < 0 Then MsgBox "Hey, " & myCell.Address(0, 0) & " is negative" End If End Sub That won't get them to change it, but it could get irritating after a while. Rightclick on the worksheet tab that should have this behavior. Select view code and paste it into that code window. The back to excel and force a few recalc's (or change the cells to make A1 evaluate as negative). Ps. change the address to what you need. pmms wrote: Excel 2002. I tried to use "Validation" on the "Data" menu but that only works on entered data. I have a formula in a cell and if the answer becomes a negative number I want it to stop the user and "force" them to fix it. I used an IF condition but that only displays a message. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop entry of data that causes a negative value from a formula
HI Pmms:
I had the exact same problem and it turned out to be an easy fix. Example: In cell validation choose CUSTOMALLOWFORMULA (then restrict the answer cell with a formula that restricts any amount less than zero) =a1=0. MOST IMPORTANTLY - *In the Data Validation window select the error alert tab and choose the style STOP. You should be good to go :-) "pmms" wrote: Excel 2002. I tried to use "Validation" on the "Data" menu but that only works on entered data. I have a formula in a cell and if the answer becomes a negative number I want it to stop the user and "force" them to fix it. I used an IF condition but that only displays a message. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop entry of data that causes a negative value from a formula
Pmms
What are you trying to prevent? A1 from becoming negative.........enter =MAX(A3*A4,0) in A1 Or preventing a number in A3 or A4 from turning A1 negative? Gord Dibben MS Excel MVP On Fri, 23 Nov 2007 08:12:00 -0800, Teddy-B wrote: HI Pmms: I had the exact same problem and it turned out to be an easy fix. Example: In cell validation choose CUSTOMALLOWFORMULA (then restrict the answer cell with a formula that restricts any amount less than zero) =a1=0. MOST IMPORTANTLY - *In the Data Validation window select the error alert tab and choose the style STOP. You should be good to go :-) "pmms" wrote: Excel 2002. I tried to use "Validation" on the "Data" menu but that only works on entered data. I have a formula in a cell and if the answer becomes a negative number I want it to stop the user and "force" them to fix it. I used an IF condition but that only displays a message. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula and Data Entry in a Single Cell | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |