Creating a formula which would affect multiple cells
 

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

Creating a formula which would affect multiple cells
 

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

Creating a formula which would affect multiple cells
 

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

Creating a formula which would affect multiple cells
 

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

Creating a formula which would affect multiple cells
 

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

Creating a formula which would affect multiple cells
 

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