Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function help
Normally column C in my workeet is an eqaution eqault to
a minus B, where A is an equation and B is a value. Occasionally, I have a user who wants to override the eqaution in C with a vlaue. In that instance, he wants to work backwards and replace the usual eqaution for A with A = column B + column C. Is there a function that will be able to tell cell A5 that cell C5 is not an eqaution anymore but, rather, a diectly entered value, a value that might even be entered as zero? If not, is there another easy way to do this? I suppose I could add an extra column with a yes/no for each row, as to whether there is an over-ride, but I would like something simpler. Thx, G |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function help
Hi
a repost from the workshett.functions NG ------------ Hi first a warning up-front: I wouldn't recommend doing this!. The solution below involves using VBA and processing the worksheet change event. enter the following code in your worksheet module (right-click on the tab name, choose 'code' and paste the procedure rom below) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("C:C")) Is Nothing Then Exit Sub On Error GoTo CleanUp: With Target If Not .HasFormula Then Application.EnableEvents = False .Offset(0, -2).FormulaR1C1 = "=R[0]C[1]+R[0]C[2]" End If End With CleanUp: Application.EnableEvents = True End Sub this will check column C and if a manual entry (tha´t is: no formula) is detected the procedure will insert a formula in column A ------------- Frank Grace wrote: Normally column C in my workeet is an eqaution eqault to a minus B, where A is an equation and B is a value. Occasionally, I have a user who wants to override the eqaution in C with a vlaue. In that instance, he wants to work backwards and replace the usual eqaution for A with A = column B + column C. Is there a function that will be able to tell cell A5 that cell C5 is not an eqaution anymore but, rather, a diectly entered value, a value that might even be entered as zero? If not, is there another easy way to do this? I suppose I could add an extra column with a yes/no for each row, as to whether there is an over-ride, but I would like something simpler. Thx, G |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function help
Hi Grace, a non-programming solution would be to use
another column for the optional data value. Then if you want that value to appear in the C column you can use a conditional formula using the IF function. In cell C5 for example, =IF(ISBLANK(D5),formulaForC,D5) In the A column use the same type of conditional formula. Best regards, John -----Original Message----- Normally column C in my workeet is an eqaution eqault to a minus B, where A is an equation and B is a value. Occasionally, I have a user who wants to override the eqaution in C with a vlaue. In that instance, he wants to work backwards and replace the usual eqaution for A with A = column B + column C. Is there a function that will be able to tell cell A5 that cell C5 is not an eqaution anymore but, rather, a diectly entered value, a value that might even be entered as zero? If not, is there another easy way to do this? I suppose I could add an extra column with a yes/no for each row, as to whether there is an over-ride, but I would like something simpler. Thx, G . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |