Can I get a cell to add to itself?

padcrusher

New Member
Joined
Sep 25, 2006
Messages
3
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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

New Member
Joined
Sep 25, 2006
Messages
3
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

New Member
Joined
Sep 25, 2006
Messages
3

ADVERTISEMENT

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

Board Regular
Joined
Sep 18, 2005
Messages
125
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,153
Members
410,666
Latest member
forzasec
Top