If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
Help
Hi,
You might look at conditional formatting to for example change the colour if the cell is not a formula. Good luck -----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 . |
#3
|
|||
|
|||
Help
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? -----Original Message----- Hi, You might look at conditional formatting to for example change the colour if the cell is not a formula. Good luck -----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 . . |
#4
|
|||
|
|||
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. 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? -----Original Message----- Hi, You might look at conditional formatting to for example change the colour if the cell is not a formula. Good luck -----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 . . |
Thread Tools | |
Display Modes | |
|
|