Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting down formula | Excel Discussion (Misc queries) | |||
Counting formula | Excel Worksheet Functions | |||
Counting Formula | Excel Discussion (Misc queries) | |||
Counting formula | Excel Discussion (Misc queries) | |||
Counting Formula | Excel Worksheet Functions |