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

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

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
.