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