Creating a formula which would affect multiple cells

[Rob]

Hello,

Here's what I am looking to do:

For every multiple of 2 inserted in cell A1, the number in cell A2 is
subtracted by one (ie, in cell A2 is the number 17. If I then insert the
number 6 in A1, A2 is automatically reduced to 14. If the number 20 is in A2
and then I insert 2 in A1, A2 is automatically reduced to 19, and so on… My
only concern is what will happen if I insert an odd number in A2, is there a
ROUNDUP option?).

 

[T. Valko]

if I insert an odd number in A2, is there a ROUNDUP option?

So, if A1 = 3 it should be evaluated as 4 and then subtract 2 from A2?
If A1 = 1 it should be evaluated as 2 and then subtract 1 from A2?

If that's the case try this. I assume you know that this has to be a
separate formula entered in some cell other than A2.

=A2-CEILING(A1/2,1)

 

[Bernard Liengme]

A1 is where you enter the 'control number'
A2 has 17
A3 has =A1-INT(A1/2)

So a 5 in A1 will give 17-2
If you want 17-3, use =ROUNDUP(A2-A1/2,0)

But if you expect do have A2 change every time you enter a value in A1, then
we need to talk VBA
best wishes

 

[Bernard Liengme]

Here is some VBA code to try. Right click the tab of the worksheet in
question, click View Code and copy the code there.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Set myRange = Intersect(Range("A1"), Target)
If Not myRange Is Nothing Then
' MsgBox "hello"
Range("A2").Value = Range("A2").Value - Int(Target / 2)
End If
Application.EnableEvents = True
End Sub

best wishes

 

[Rob]

Bernard,

The code works. Thanks.

Maybe it can be tweaked...Before I enter anything into A2, there's a formula
attached to it. THe formula is =$C$6/150. Once I add any number into A1, A2
is affected. If i delete the number from A1, the formula doesn't come back
into A2. It's not a big deal but it would be awesome if the formula would pop
back up.

Additionally, is there a way to add a ROUNDUP formula to a cell that already
has a formula attached to it?

 

[Rob]

Bernard,

I tried re-copying the code under the original code and changed the "Range"
to apply to other cells in an effort to have the same effect but it didn't
work. What did I do wrong?