Can I get a cell to add to itself?

[padcrusher]

Please forgive me if this question seems daft,
but I'm new to excel and just learning.

I need a cell's number to increase by 1
on the condition of another cell, something like;

=if(A10>500,+1) or if(A10>500,A1=A1+1)

Is there any way I could do such a thing in Excel?

thanks

 

[alkarkar]

Check this:
Under the Tools / Options / Calculation tab, make sure that Calculation is set to Automatic, and that the Iteration box is checked. Then enter the following formula in cell A1:
IF(A10>500,A1+1)

http://www.mrexcel.com/board2/viewtopic.php?t=50244&highlight=min+circular+reference

 

[pgc01]

Hi padcrusher
Welcome to the board

When do you want the increment to take place, each time A10 changes value? Other condition?

Kind regards
PGC

 

[padcrusher]

Hi PGC, thanks for replying.
Yes I need it to change every time,
my example wasn't very clear.
I'd like it to go something like;
=if(A10="STRING" and B10>0, B1=B1+1)
And I'd like B1 to increase each time those conditions are true.

Thanks again.

 

[padcrusher]

Hi alkarkar, thanks for your help.
Your suggestion did work, but when A10 reverts back to it's original condition, so does A1.
I should point out that it's pocket excel I'm using,
but basic formulae should still work in the same way, I think.

 

[alkarkar]

You wrote:

when A10 reverts back to it's original condition, so does A1

What do you mean by this? What is ''the original condition'' (for both A10 and A1. An example?

 

[pgc01]

Hi padcrusher

I'd like it to go something like;
=if(A10="STRING" and B10>0, B1=B1+1)

I still don't understand very well what you want.

Anyway it may be this

Every time the values of A10 or B10 change, if both conditions A10="STRING" and B10>0 are true, then increment the value of the cell B1.

In this case you have to use the Change Event of the worksheet. Click on the tab of the worksheet, choose View Code and paste the code at the end of this post.

If I misunderstood what you want, please try to explain again and give a working example. like

- This is the initial condition of B1, A10 and B10
- A10 changes to ???, this happens
- B10 changes to ???, this happens
- B10 changes again, this time to ???, this happens
etc.

Hope this helps
PGC

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$10" Or Target.Address = "$B$10" Then

    If Range("A10").Value = "STRING" And Range("B10").Value > 0 Then _
        Range("B1").Value = Range("B1").Value + 1

End If
End Sub