Incrementing a value in a cell

Hi Graham

To do this without VBA would require a "Circular Reference" eg

=IF(B2=25,A1+1,A1)

You would first need to go to Tools>Options>Calculations and check the Iterations box. While there look at the help for this topic. Just be aware that you may well get undesirable results

In case you change your mind you can place this code in the Sheet Module and it will run automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$1" Then
If IsNumeric(Range("B1")) Then
If Range("B1") = 25 Then
Application.EnableEvents = False
Target = Target + 1
Application.EnableEvents = True
End If
End If
End If

End Sub

Dave

OzGrid Business Applications

Graham

If you already have a value in A1 which you want to add 1 to, the formula

=IF(B2=25,A1+1,A1)

will not work, even if you make circular references. A formula-based update of A1 can indeed be done with circular references where you have to memorize the old value of A1 before any aupdate. Stephen Bullen shows how it can be done (see Stephen@BMSLtd.co.uk).

I'd suggest using VBA for this kind of intrusive changes that you need.

Aladin

if you can handle another column, put =if(b2=25,a2+1,a2) in c2