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
|