Example: I want to type a number in a cell and have it subtract a fraction and display the result, all within the same cell.
1 Answer
I am quite sure it is not possible without VBA.
I usually use this VBA code for such tasks:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHnd
Dim cell As Range
'This is the place where you put cells which are affected (A2 in this case)
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
Application.EnableEvents = False
For Each cell In Target
cell.Value = cell.Value - 0.5
Next
End If
ErrHnd:
Application.EnableEvents = True
End Sub
There is a place to define cells which will be affected by this code. Just change the "A2"
part. You can put there ranges and separate them using commas, i.e. "A2:A3,A5"
.
The part cell.Value = cell.Value - 0.5
is the actual fraction subtraction. Currently it subtracts 0.5, but you can change it as well.
You should copy it into the Sheet module in VBA Editor. VBA Editor opens after keyboard combination Alt + F11.