Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi,
You might look at conditional formatting to for example
change the colour if the cell is not a formula.

Good luck
 
Thanks for trying but I guess I wasn't clear enough.
Let's say A is in column A, B in B, and C in C. If
someone overwrites the equation, say, a5-b5 in cell c5,
with the number 6, I want the number that I previously
might put in cell a5 to change into an eqaution. Suppose
it is cell a5 and it normally would contain the value
1233. I would like to rewrite cell a5 as

if (c5 = number, c5+b5, 1233)

But how can the formula tell that cell c5 is a number
entered by the user, as opposed to the normal eqaution
that would reside there, say = a5 - b5.

Do you see what I'm asking? Can anyone help?
 
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



Thanks for trying but I guess I wasn't clear enough.
 
Back
Top