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?

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


"Todd Nelson" wrote:

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?

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

--
Regards,
Zack Barresse, aka firefytr


"Todd Nelson" wrote in message
...
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?

How?? If N5=removed, j5=0, is what i need it to state

"Duke Carey" wrote:

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


"Todd Nelson" wrote:

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?

That works! Now....is there a way to do a complete column??

"Zack Barresse" wrote:

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

--
Regards,
Zack Barresse, aka firefytr


"Todd Nelson" wrote in message
...
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?

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.

--
Regards,
Zack Barresse, aka firefytr


"Todd Nelson" wrote in message
...
That works! Now....is there a way to do a complete column??

"Zack Barresse" wrote:

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

--
Regards,
Zack Barresse, aka firefytr


"Todd Nelson" wrote in message
...
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?

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.)

Zack Barresse wrote:

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.

--
Regards,
Zack Barresse, aka firefytr

"Todd Nelson" wrote in message
...
That works! Now....is there a way to do a complete column??

"Zack Barresse" wrote:

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

--
Regards,
Zack Barresse, aka firefytr


"Todd Nelson" wrote in message
...
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?




--

Dave Peterson