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?).
--
~~Rob

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)

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
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?).
--
~~Rob

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
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"Rob" wrote in message
...
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?).
--
~~Rob

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme




"Rob" wrote in message
...
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?).
--
~~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 Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme




"Rob" wrote in message
...
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?).
--
~~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?
--
~~Rob


"Bernard Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme




"Rob" wrote in message
...
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?).
--
~~Rob