Need help w/ formula

T

Todd Nelson

Is there a formula that will change another cells information w/o having a
formula in that particular cell. For instance, the IF(N5=Removed" then J5=0)
without putting a formula in j5?
 
D

Duke Carey

No. A cell formula cannot change another cell.

You can write code to react to value changes in cell N5 and the *code* can
change the value in J5
 
Z

Zack Barresse

No, you cannot affect another cell with worksheet formulas. For that you
would need VBA (Visual Basic for Applications).

If you right click your sheet tab and select View Code, then paste this in
there, it may work for you ...


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("N5").Value = "Removed" Then Range("J5").Value = 0
End Sub


HTH
 
Z

Zack Barresse

Yes ...


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Only one cell
If Target.Cell.Count > 1 then Exit Sub
'Only column N
If Intersect(Target, Range("N:N")) Is Nothing Then Exit Sub
'Work on entire column, changing same row column J value
If Target.Value = "Removed" Then Cells(Target.Row, "J").Value = 0
End Sub


You can set the range to anything desired actually.
 
D

Dave Peterson

Just a typo alert.

You have "target.cell.count". You meant "target.cells.count".

But if you're changing something in a worksheet_change event, it's usually best
to disable events while you do your change. Then the change you code makes
doesn't cause the event to fire again.

Your routine slightly modified:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'Only one cell
If Target.Cells.Count > 1 Then Exit Sub

'Only column N
If Intersect(Target, Me.Range("N:N")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

'Work on entire column, changing same row column J value
If LCase(Target.Value) = LCase("Removed") Then
Application.EnableEvents = False
Me.Cells(Target.Row, "J").Value = 0
End If

errHandler:
Application.EnableEvents = True
End Sub

(I also changed target.value to lcase(target.value)--just in case the user types
removed or REMOVED or some variation.)
 
Top