Excel - advanced formula help Closed

Question

Hi,

I am trying to come up with (find) a formula in Excel 2007 that will allow me to affect either "cell2" or "cell3" depending on the value of "cell1".  To be more specific, the values in B1:B100 are either the letter "s" or "f".  The values in C1:C100 and D1:D100 are numerical.  If "s" is the value in B3 (for example), I want it to subtract 1 (-1) from C3, and leave D3 at it's current value; but if "f" is the value of B3, I want it to subtract 1 from D3, and leave C3 at it's current value.

B is the type of event while C and D are the number of times remaining that that event can be carried out.

I hope this makes sense.  Thanks in advance!!



Configuration: Windows XP / Internet Explorer 8.0

TrowaD · Answer

Hi mjohnson,

A formula won't do the job. A formula can't change the value of another cell.
A VB code can.

So try this code:
Sub test()
Set MyRng = Range("B1:B100")
For Each cell In MyRng
    If cell.Value = "s" Then cell.Offset(0, 1).Value = cell.Offset(0, 1).Value - 1
    If cell.Value = "f" Then cell.Offset(0, 2).Value = cell.Offset(0, 2).Value - 1
        Next
End Sub
Best regards,
Trowa

Excel - advanced formula help

1 response

Similar discussions

Subscribe To Our Newsletter!