Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Hello everyone

This is a 2 step question in microsoft excel:

I would like to be able to take a cell with number 50 hrs which will go down anywhere to 0 while another cell will increase the difference between that 50 and 0.

Part 1:

Example: cell A1=50 cell A2=0 cell A3=0

now cell A1=45 cell A2=5 therefore still = 50 cell A3=5

Cell A1=32 cell A2=18 therefore still =50 cell A3=18

Cell A1=0 cell A2=50 cell A3=50

Now the next thing I need is another cell that would keep adding this A2 hrs that would keep accumulating even though these 2 values in A1 and A2 will keep going up and down anywhere between 0 and 50.

Part 2:

So here goes my example:

Example: cell A1=50 cell A2=0 cell A3=0

now cell A1=45 cell A2=5 therefore still = 50 ...cell A3=5

Cell A1=32 cell A2=18 therefore still =50 ...cell A3=18

Cell A1=0 cell A2=50 ...cell A3=50

Cell A1=50 cell A2=0 ...cell A3=50

Cell A1=36 cell A2=14 ...cell A3=(50+14)=64 therefore Cell A3 keep accumulating all the time whenever cell A2 increases

Cell A1=15 cell A2=35 ...cell A3 =(64+35)=99

Cell A1=7 cell A2=43 ...cell A3=(99+43)=142

This will keep going on and on and on.....therefore cell A3 will keep increasing.

Is there a way to do this formula work?

Thanks very much for any help with this.

Cell A1=50 cell A2=0 ...cell A3=98

This is a 2 step question in microsoft excel:

I would like to be able to take a cell with number 50 hrs which will go down anywhere to 0 while another cell will increase the difference between that 50 and 0.

Part 1:

Example: cell A1=50 cell A2=0 cell A3=0

now cell A1=45 cell A2=5 therefore still = 50 cell A3=5

Cell A1=32 cell A2=18 therefore still =50 cell A3=18

Cell A1=0 cell A2=50 cell A3=50

Now the next thing I need is another cell that would keep adding this A2 hrs that would keep accumulating even though these 2 values in A1 and A2 will keep going up and down anywhere between 0 and 50.

Part 2:

So here goes my example:

Example: cell A1=50 cell A2=0 cell A3=0

now cell A1=45 cell A2=5 therefore still = 50 ...cell A3=5

Cell A1=32 cell A2=18 therefore still =50 ...cell A3=18

Cell A1=0 cell A2=50 ...cell A3=50

Cell A1=50 cell A2=0 ...cell A3=50

Cell A1=36 cell A2=14 ...cell A3=(50+14)=64 therefore Cell A3 keep accumulating all the time whenever cell A2 increases

Cell A1=15 cell A2=35 ...cell A3 =(64+35)=99

Cell A1=7 cell A2=43 ...cell A3=(99+43)=142

This will keep going on and on and on.....therefore cell A3 will keep increasing.

Is there a way to do this formula work?

Thanks very much for any help with this.

Cell A1=50 cell A2=0 ...cell A3=98

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I will try this out today if time permits and see if I can make it work. Yes I will only run it once A2 is 50 that's fine. The reason for this is every time the impacts on our Jumbo drills get to 50 we have to do a maintenance PM on it as for the A3 cell total this will give me the exact hours/year/ impact on each percussion drill done.

Thanks very much...I will try it and post back my results.

Have a great day also...Merry Xmas and best wishes.

```
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [A1]) Is Nothing Then
[C1] = [C1] + [B1]
End If
End Sub
```

Now every time you change the value in A1, C1 will automatically recalculate.

Any chance you can elaborate a little on how to create this macro? I keep getting errors when i run it so not sure if i have it configured correctly.

Thanks for any help...greatly appreciated.

I tried creating the macro but no luck. I'm not sure if I've got it configured properly. I'm not sure how to configure the macro.

Do I use Alt + f11 and add a module or do I record a macro? Can someone help me out with the steps.

Thanks very much for the help. I attached a photo of what I have tried.

impactmacro-1.jpeg

I only saw jell's macro. If you try with mine from "above" it will work. It's a little longer but requires no additional configuring...

Have a nice day

May be you should try the code attached which changes the values in A2 and A3 for any change in A1.

Hope this is what you require.

Tils. impact-worksheet.xlsm

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
i = Range("A1")
j = 50 - i
k = Range("A3")
If k >= 50 Then
k = Range("A3") + j
Range("A2") = j
Range("A3") = k
Else
k = j
Range("A2") = j
Range("A3") = k
End If
Else
Exit Sub
End If
End Sub
```

The reason it took me a while to get back was that I'm a little green when it comes to programing this and had to figure out how to do this.

Tilsant:

This code works very well for the first 50 but once it goes back to 50 then the sum doesn't work anymore.

Ex: A1=50 A2=0 A3=0

A1=40 A2=10 A2=10

A1=35 A2=15 A3=15

A1= 0 A2=50 A3=50

A1=50 A2=0 A3=50

A1=40 A2=10 A3=60

A1=35 A2=15 A3=75 This is where the code stops functionning cause it adds A2 with A3 but it should only add 40-35=5 therefore A3=65 and not 75. Other then that this code would work perfectly.

Gasperk:

Same thing here. The code works well for the first time you enter a number but as soon as you enter a number the 2nd time then it adds the entire value of the cell A2 and not just the change of the cell.

A1=50 A2=0 A3=0

A1=45 A2=5 run macro A3=5

A1=35 A2=15 run macro but now A3=20 it started adding up the A2 and A3 values immediately therefore the answer is now 20 instead of 15.

I will try the one from jell and see if I can figure out how to create the macro itself and test it with results.

Thanks very much for the help. Please advise if someone can figure out the issue that I have. They are very close to what I need but not correct.

I figured out how to get this going but still not what I'm looking for. This is how this code works out:

A1=50 B1=0 C1=0

A1=40 B1=10 C1=10

A1=35 B1=15 but now C1=25 when it should be 15.

Thanks again to everyone for the help and sorry it took a bit of time but I had to figure out how to make these work since I'm pretty green at coding. If anyone can figure something out that would do what I need please advise. I will try keep working with these and see If I can't figure out some way to make one of these codes work for me.

Tils.

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
i = Range("A1")
j = 50 - i
k = Range("A3")
If k >= 50 Then
If i = 50 Then
k = Range("A3") + j
Range("A2") = j
Range("A3") = k
Else
k = Range("A3") + 50 - Range("A2") - i
Range("A2") = j
Range("A3") = k
End If
Else
k = j
Range("A2") = j
Range("A3") = k
End If
Else
Exit Sub
End If
End Sub
```

impact-worksheet.xlsm
Anyway, hope the previous solution works for you.

-Tils.

```
Part 2:
So here goes my example:
Example: cell A1=50 cell A2=0 cell A3=0
now cell A1=45 cell A2=5 therefore still = 50 ...cell A3=5
Cell A1=32 cell A2=18 therefore still =50 ...cell A3=18
Cell A1=0 cell A2=50 ...cell A3=50
Cell A1=50 cell A2=0 ...cell A3=50
Cell A1=36 cell A2=14 ...cell A3=(50+14)=64 therefore Cell A3 keep accumulating all the time whenever cell A2 increases
Cell A1=15 cell A2=35 ...cell A3 =(64+35)=99
Cell A1=7 cell A2=43 ...cell A3=(99+43)=142
This will keep going on and on and on.....therefore cell A3 will keep increasing
```

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
i = Range("A1")
j = 50 - i
k = Range("A3")
If i = 50 Then
k = Range("A3") + j
Range("A2") = j
Range("A3") = k
Else
k = Range("A3") + 50 - Range("A2") - i
Range("A2") = j
Range("A3") = k
End If
Else
Exit Sub
End If
End Sub
```

impact-worksheet-v2.xlsm
Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialYou are totally right and I did say that , my mistake. The code you now have works perfectly. If I may I would now require a little clarification for me to complete this in my situation:

I have to do this in the same worksheet for mutiple rows. The code you have created works perfectly for 1 machine. Now this must work for 6 different machines using the same pattern you could say in the same worksheet. Can I have multiple If, Else, End statements or do I include the other machines in this code?

Example:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, j, k As Long

If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then

i = Range("A1")

j = 50 - i

k = Range("A3")

If i = 50 Then

k = Range("A3") + j

Range("A2") = j

Range("A3") = k

Else

k = Range("A3") + 50 - Range("A2") - i

Range("A2") = j

Range("A3") = k

End If

Else

Exit Sub

End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, j, k As Long

If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then

l = Range ("A21")

m = 50 - l

n = Range("A23")

If l = 50 Then

k = Range("A23") + m

Range("A22") = m

Range("A23") = n

Else

n = Range("A23") + 50 - Range("A22") - l

Range("A22") = l

Range("A23") = n

End If

Else

Exit Sub

End If

End Sub

Then I would keep going for 5 more machines. Is this the way I can achieve this?

Or is this not possible to code?

Thanks very much for the help.

Here's the code I now have:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, j, k As Long

If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then

i = Range("A1")

j = 50 - i

k = Range("A3")

If i = 50 Then

k = Range("A3") + j

Range("A2") = j

Range("A3") = k

Else

k = Range("A3") + 50 - Range("A2") - i

Range("A2") = j

Range("A3") = k

End If

End If

Dim l, m, n As Long

If Not Intersect(Target, ActiveSheet.Range("A21")) Is Nothing Then

l = Range("A21")

m = 50 - l

n = Range("A23")

If l = 50 Then

n = Range("A23") + m

Range("A22") = m

Range("A23") = n

Else

n = Range("A23") + 50 - Range("A22") - l

Range("A22") = m

Range("A23") = n

End If

End If

Exit Sub

End Sub

i tried it in 4 machines (still in the example not the real document yet) and all seems to work perfectly for all of them.

I want to thank everyone for all their help and efforts. I do apologize for the error made that Tilsant pinpointed out and amount of time it took for me to reply back but I had to learn how to make this work (newbie programmer) with the codes created.

Again thanks very much to all it is greatly appreciated.

No I'm tracking the engine and drill impact hours on around 114 machines and keep growing all the time they are all in the same worksheet. What I'm doing is tracking the hour meters on engines and drill impacts hours which need to have preventive maintenance services done on them at intervals and I have to keep track of hours also for budgeting purposes at the end of the year. The way you did that code works very well in the test worksheet I've done. Tomorrow I'll implement it in the actual copy of the document and check it but all should be fine.

I do have to add another type of code in here for another calculation that must be done in the same document but I'll open another post so you can get points for it.

Basically this one is:

Cell A44=0 Cell A45=0

Cell A44=20 Cell A45=20

Cell A44=50 Cell A45=50

Cell A44=3000 Cell A45=3000

This keeps going on until I have to change an hour meter or an engine on a machine. Therefore now

Cell A44=0 Cell A45=3000

Cell A44=24 Cell A45=3024

Cell A44=157 Cell A45= 3024+157=3181

In other words cell A44 can be anything from 0 to whatever but will 0 out eventually. It will zero out more then once through the years. Cell A45 should just keep adding up the hours (for 1 year then I'll zero both of them and start again.

There are actually about 114 pieces of equipment counting plated vehicles. (So A1 to A114 let's say)

NOTE: This must be implemented for any amount of machines and I must be able to add on as we purchase more.

Like I said I'll start a new thread for this one for point purposes.

Thanks very much for the help. I really appreciate you and this site for all the support and help I 've always received here.

My hat's off to all of you here.

In the attached file, changes in cell A1, A21, A41 would do the required changes.

More cell references (to add more machines) can be added in the 5th line of the code as required.

Hope this helps.

Tils.

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1, A21, A41")) Is Nothing Then
i = Target
j = 50 - i
k = Target.Offset(2, 0)
If i = 50 Then
k = Target.Offset(2, 0) + j
Else
k = k + 50 - Target.Offset(1, 0) - i
End If
Target.Offset(1, 0) = j
Target.Offset(2, 0) = k
Else
Exit Sub
End If
End Sub
```

impact-worksheet-v3.xlsm
Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

here is my idea,

You leave cell A1 as is, and manually change that value.

In cell A2 you write =50-A1

For cell A3 you have a problem, because you will do a circular reference since the formula you want is =A3+A2. So my advice is to resolve this by a simle macro:

Sub AddAValue()

Dim s1 As Integer, s2 As Integer, s3 As Integer

s1 = Range("A3").Value

s2 = Range("A2").Value

s3= s1 + s2

Range("A3").Value = s3

End Sub

You run this every time the value in A2 changes.

Not quite shure if you want to start adding only when the value in A2 is 50. Could not get that from the example....

Have a nice day....