Counting Formula
 

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End With
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"santaviga" wrote in message
...
Hi Folks,

I'm looking for a formula for the following.

Cell range for data input is B10:H12 input into these cells are variable,
they a X, 8, H, 12X, 8X and some are left blank

X has a value of 11.25
8 has a value of 7.5
H has a value of 7.5
12X has a value of 11.25
8X has a value of 7.5

In cell B27 the formula I require is to have 200.0 as a starting number
and
when I input any of the Data in the cell range it needs to subtract the
equivelent value, so it will return a value of sum remaining. e.g. 192.5
will
return when I input 8, 181.25 will return when I input X and so on.

Can anyone Help.


Regards

Counting Formula
 

Hi Bob,

Thanks for your reply but I dont know now anything about VBA and wouldn't
know where to start to input this data.

Mark

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End With
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"santaviga" wrote in message
...
Hi Folks,

I'm looking for a formula for the following.

Cell range for data input is B10:H12 input into these cells are variable,
they a X, 8, H, 12X, 8X and some are left blank

X has a value of 11.25
8 has a value of 7.5
H has a value of 7.5
12X has a value of 11.25
8X has a value of 7.5

In cell B27 the formula I require is to have 200.0 as a starting number
and
when I input any of the Data in the cell range it needs to subtract the
equivelent value, so it will return a value of sum remaining. e.g. 192.5
will
return when I input 8, 181.25 will return when I input X and so on.

Can anyone Help.


Regards

Counting Formula
 

right click the sheet tab with your data, select view code and paste it there.

the code will fire anytime anything in the ws_range (see below) is changed and
put the result in b27

Const WS_RANGE As String = "B10:H12"
--


Gary


"santaviga" wrote in message
...
Hi Bob,

Thanks for your reply but I dont know now anything about VBA and wouldn't
know where to start to input this data.

Mark

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End With
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"santaviga" wrote in message
...
Hi Folks,

I'm looking for a formula for the following.

Cell range for data input is B10:H12 input into these cells are variable,
they a X, 8, H, 12X, 8X and some are left blank

X has a value of 11.25
8 has a value of 7.5
H has a value of 7.5
12X has a value of 11.25
8X has a value of 7.5

In cell B27 the formula I require is to have 200.0 as a starting number
and
when I input any of the Data in the cell range it needs to subtract the
equivelent value, so it will return a value of sum remaining. e.g. 192.5
will
return when I input 8, 181.25 will return when I input X and so on.

Can anyone Help.


Regards

Counting Formula
 

Hi,

Tried this, copied and paste into the worksheet but returning a compile
error, I dont know how to write VBA, is there a way to enter it in the actual
cell, such as sumproduct or sumif?

Mark

"Gary Keramidas" wrote:

right click the sheet tab with your data, select view code and paste it there.

the code will fire anytime anything in the ws_range (see below) is changed and
put the result in b27

Const WS_RANGE As String = "B10:H12"
--


Gary


"santaviga" wrote in message
...
Hi Bob,

Thanks for your reply but I dont know now anything about VBA and wouldn't
know where to start to input this data.

Mark

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End With
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"santaviga" wrote in message
...
Hi Folks,

I'm looking for a formula for the following.

Cell range for data input is B10:H12 input into these cells are variable,
they a X, 8, H, 12X, 8X and some are left blank

X has a value of 11.25
8 has a value of 7.5
H has a value of 7.5
12X has a value of 11.25
8X has a value of 7.5

In cell B27 the formula I require is to have 200.0 as a starting number
and
when I input any of the Data in the cell range it needs to subtract the
equivelent value, so it will return a value of sum remaining. e.g. 192.5
will
return when I input 8, 181.25 will return when I input X and so on.

Can anyone Help.


Regards

Counting Formula
 

ok, i saw bob had a typo, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End Select
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub



--


Gary


"santaviga" wrote in message
...
Hi,

Tried this, copied and paste into the worksheet but returning a compile
error, I dont know how to write VBA, is there a way to enter it in the actual
cell, such as sumproduct or sumif?

Mark

"Gary Keramidas" wrote:

right click the sheet tab with your data, select view code and paste it
there.

the code will fire anytime anything in the ws_range (see below) is changed
and
put the result in b27

Const WS_RANGE As String = "B10:H12"
--


Gary


"santaviga" wrote in message
...
Hi Bob,

Thanks for your reply but I dont know now anything about VBA and wouldn't
know where to start to input this data.

Mark

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End With
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"santaviga" wrote in message
...
Hi Folks,

I'm looking for a formula for the following.

Cell range for data input is B10:H12 input into these cells are
variable,
they a X, 8, H, 12X, 8X and some are left blank

X has a value of 11.25
8 has a value of 7.5
H has a value of 7.5
12X has a value of 11.25
8X has a value of 7.5

In cell B27 the formula I require is to have 200.0 as a starting number
and
when I input any of the Data in the cell range it needs to subtract the
equivelent value, so it will return a value of sum remaining. e.g. 192.5
will
return when I input 8, 181.25 will return when I input X and so on.

Can anyone Help.


Regards

Counting Formula
 

You can't enter a value in a cell with a formula without overwriting the
formula. If the results will be somewhere else, then you can get you value
this way:

=vlookup(B10,{"X",11.25;8,7.5;"H",7.5;"12X",11.25; "8X",7.5},2,False)

you could put that in your subtraction formula.


--
Regards,
Tom Ogilvy

"santaviga" wrote in message
...
Hi,

Tried this, copied and paste into the worksheet but returning a compile
error, I dont know how to write VBA, is there a way to enter it in the
actual
cell, such as sumproduct or sumif?

Mark

"Gary Keramidas" wrote:

right click the sheet tab with your data, select view code and paste it
there.

the code will fire anytime anything in the ws_range (see below) is
changed and
put the result in b27

Const WS_RANGE As String = "B10:H12"
--


Gary


"santaviga" wrote in message
...
Hi Bob,

Thanks for your reply but I dont know now anything about VBA and
wouldn't
know where to start to input this data.

Mark

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End With
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"santaviga" wrote in message
...
Hi Folks,

I'm looking for a formula for the following.

Cell range for data input is B10:H12 input into these cells are
variable,
they a X, 8, H, 12X, 8X and some are left blank

X has a value of 11.25
8 has a value of 7.5
H has a value of 7.5
12X has a value of 11.25
8X has a value of 7.5

In cell B27 the formula I require is to have 200.0 as a starting
number
and
when I input any of the Data in the cell range it needs to subtract
the
equivelent value, so it will return a value of sum remaining. e.g.
192.5
will
return when I input 8, 181.25 will return when I input X and so on.

Can anyone Help.


Regards

Counting Formula
 

thanks Gary works a treat, But, sorry, How do I get the sum to subtract when
I delete an entry in the input cells? this is working but when I delete an
input entry e.g. an X this does not change the sum number, also I need to
input more input cells and return the sum in another cell as previous but
further down the worksheet but on the same worksheet, any ideas.


Thanks


Mark

"Gary Keramidas" wrote:

ok, i saw bob had a typo, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End Select
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub



--


Gary


"santaviga" wrote in message
...
Hi,

Tried this, copied and paste into the worksheet but returning a compile
error, I dont know how to write VBA, is there a way to enter it in the actual
cell, such as sumproduct or sumif?

Mark

"Gary Keramidas" wrote:

right click the sheet tab with your data, select view code and paste it
there.

the code will fire anytime anything in the ws_range (see below) is changed
and
put the result in b27

Const WS_RANGE As String = "B10:H12"
--


Gary


"santaviga" wrote in message
...
Hi Bob,

Thanks for your reply but I dont know now anything about VBA and wouldn't
know where to start to input this data.

Mark

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End With
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"santaviga" wrote in message
...
Hi Folks,

I'm looking for a formula for the following.

Cell range for data input is B10:H12 input into these cells are
variable,
they a X, 8, H, 12X, 8X and some are left blank

X has a value of 11.25
8 has a value of 7.5
H has a value of 7.5
12X has a value of 11.25
8X has a value of 7.5

In cell B27 the formula I require is to have 200.0 as a starting number
and
when I input any of the Data in the cell range it needs to subtract the
equivelent value, so it will return a value of sum remaining. e.g. 192.5
will
return when I input 8, 181.25 will return when I input X and so on.

Can anyone Help.


Regards

Counting Formula
 

=200-SUM(IF(ISERROR(VLOOKUP(B10:H12,{"X",11.25;8,7.5;"H ",7.5;"12X",11.25;"8X",7.5},2,FALSE)),0,VLOOKUP(B1 0:H12,{"X",11.25;8,7.5;"H",7.5;"12X",11.25;"8X",7. 5},2,FALSE)))

entered in B27 with ctrl+Shift+enter rather than just Enter, handled both
entries and deletions for me.

for your second location, just adjust the cell references and use the same
approach.

--
Regards,
Tom Ogilvy



"santaviga" wrote in message
...
thanks Gary works a treat, But, sorry, How do I get the sum to subtract
when
I delete an entry in the input cells? this is working but when I delete an
input entry e.g. an X this does not change the sum number, also I need to
input more input cells and return the sum in another cell as previous but
further down the worksheet but on the same worksheet, any ideas.


Thanks


Mark

"Gary Keramidas" wrote:

ok, i saw bob had a typo, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End Select
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub



--


Gary


"santaviga" wrote in message
...
Hi,

Tried this, copied and paste into the worksheet but returning a compile
error, I dont know how to write VBA, is there a way to enter it in the
actual
cell, such as sumproduct or sumif?

Mark

"Gary Keramidas" wrote:

right click the sheet tab with your data, select view code and paste
it
there.

the code will fire anytime anything in the ws_range (see below) is
changed
and
put the result in b27

Const WS_RANGE As String = "B10:H12"
--


Gary


"santaviga" wrote in message
...
Hi Bob,

Thanks for your reply but I dont know now anything about VBA and
wouldn't
know where to start to input this data.

Mark

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End With
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"santaviga" wrote in message
...
Hi Folks,

I'm looking for a formula for the following.

Cell range for data input is B10:H12 input into these cells are
variable,
they a X, 8, H, 12X, 8X and some are left blank

X has a value of 11.25
8 has a value of 7.5
H has a value of 7.5
12X has a value of 11.25
8X has a value of 7.5

In cell B27 the formula I require is to have 200.0 as a starting
number
and
when I input any of the Data in the cell range it needs to
subtract the
equivelent value, so it will return a value of sum remaining.
e.g. 192.5
will
return when I input 8, 181.25 will return when I input X and so
on.

Can anyone Help.


Regards

Counting Formula
 

Cant get this to work, will only deduct the data entered into cell B10 and no
other cells, do I have to enter a VLOOKUP table?

Mark

"Tom Ogilvy" wrote:

=200-SUM(IF(ISERROR(VLOOKUP(B10:H12,{"X",11.25;8,7.5;"H ",7.5;"12X",11.25;"8X",7.5},2,FALSE)),0,VLOOKUP(B1 0:H12,{"X",11.25;8,7.5;"H",7.5;"12X",11.25;"8X",7. 5},2,FALSE)))

entered in B27 with ctrl+Shift+enter rather than just Enter, handled both
entries and deletions for me.

for your second location, just adjust the cell references and use the same
approach.

--
Regards,
Tom Ogilvy



"santaviga" wrote in message
...
thanks Gary works a treat, But, sorry, How do I get the sum to subtract
when
I delete an entry in the input cells? this is working but when I delete an
input entry e.g. an X this does not change the sum number, also I need to
input more input cells and return the sum in another cell as previous but
further down the worksheet but on the same worksheet, any ideas.


Thanks


Mark

"Gary Keramidas" wrote:

ok, i saw bob had a typo, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End Select
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub



--


Gary


"santaviga" wrote in message
...
Hi,

Tried this, copied and paste into the worksheet but returning a compile
error, I dont know how to write VBA, is there a way to enter it in the
actual
cell, such as sumproduct or sumif?

Mark

"Gary Keramidas" wrote:

right click the sheet tab with your data, select view code and paste
it
there.

the code will fire anytime anything in the ws_range (see below) is
changed
and
put the result in b27

Const WS_RANGE As String = "B10:H12"
--


Gary


"santaviga" wrote in message
...
Hi Bob,

Thanks for your reply but I dont know now anything about VBA and
wouldn't
know where to start to input this data.

Mark

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End With
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"santaviga" wrote in message
...
Hi Folks,

I'm looking for a formula for the following.

Cell range for data input is B10:H12 input into these cells are
variable,
they a X, 8, H, 12X, 8X and some are left blank

X has a value of 11.25
8 has a value of 7.5
H has a value of 7.5
12X has a value of 11.25
8X has a value of 7.5

In cell B27 the formula I require is to have 200.0 as a starting
number
and
when I input any of the Data in the cell range it needs to
subtract the
equivelent value, so it will return a value of sum remaining.
e.g. 192.5
will
return when I input 8, 181.25 will return when I input X and so
on.

Can anyone Help.


Regards

Counting Formula
 

Insufficient testing on my part. This worked:

=200-SUM(IF(ISERROR(LOOKUP(B10:H12,{8,"12X","8X","H","X "},{7.5,11.25,7.5,7.5,11.25})),0,LOOKUP(B10:H12,{8 ,"12X","8X","H","X"},{7.5,11.25,7.5,7.5,11.25}) ))

also entered with ctrl+Shift+enter

--
Regards,
Tom Ogilvy



"santaviga" wrote in message
...
Cant get this to work, will only deduct the data entered into cell B10 and
no
other cells, do I have to enter a VLOOKUP table?

Mark

"Tom Ogilvy" wrote:

=200-SUM(IF(ISERROR(VLOOKUP(B10:H12,{"X",11.25;8,7.5;"H ",7.5;"12X",11.25;"8X",7.5},2,FALSE)),0,VLOOKUP(B1 0:H12,{"X",11.25;8,7.5;"H",7.5;"12X",11.25;"8X",7. 5},2,FALSE)))

entered in B27 with ctrl+Shift+enter rather than just Enter, handled both
entries and deletions for me.

for your second location, just adjust the cell references and use the
same
approach.

--
Regards,
Tom Ogilvy



"santaviga" wrote in message
...
thanks Gary works a treat, But, sorry, How do I get the sum to subtract
when
I delete an entry in the input cells? this is working but when I delete
an
input entry e.g. an X this does not change the sum number, also I need
to
input more input cells and return the sum in another cell as previous
but
further down the worksheet but on the same worksheet, any ideas.


Thanks


Mark

"Gary Keramidas" wrote:

ok, i saw bob had a typo, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End Select
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub



--


Gary


"santaviga" wrote in message
...
Hi,

Tried this, copied and paste into the worksheet but returning a
compile
error, I dont know how to write VBA, is there a way to enter it in
the
actual
cell, such as sumproduct or sumif?

Mark

"Gary Keramidas" wrote:

right click the sheet tab with your data, select view code and
paste
it
there.

the code will fire anytime anything in the ws_range (see below) is
changed
and
put the result in b27

Const WS_RANGE As String = "B10:H12"
--


Gary


"santaviga" wrote in message
...
Hi Bob,

Thanks for your reply but I dont know now anything about VBA and
wouldn't
know where to start to input this data.

Mark

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B10:H12" '<== change to suit
Dim i As Double
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "X": i = 11.26
Case 8: i = 7.5
Case "H": i = 7.5
Case "12X": i = 11.25
Case "8X": i = 7.5
End With
Me.Range("B27").Value = Me.Range("B27").Value - i
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"santaviga" wrote in
message
...
Hi Folks,

I'm looking for a formula for the following.

Cell range for data input is B10:H12 input into these cells
are
variable,
they a X, 8, H, 12X, 8X and some are left blank

X has a value of 11.25
8 has a value of 7.5
H has a value of 7.5
12X has a value of 11.25
8X has a value of 7.5

In cell B27 the formula I require is to have 200.0 as a
starting
number
and
when I input any of the Data in the cell range it needs to
subtract the
equivelent value, so it will return a value of sum remaining.
e.g. 192.5
will
return when I input 8, 181.25 will return when I input X and
so
on.

Can anyone Help.


Regards