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