Can I 'embed' a formula to affect numbers as they're entered?

[barkingmad]

Ms Office Excel 2003 ( Ms Office Professional Edition 2003)

I want to 'embed' a formula (eg: *.05) in multiple cells so that when data
is then entered into the cells it operates on the data and displays the
result. For instance, in the example given, if I then entered '16' into a
cell the figure displayed would be '0.8'

 

[Mike H]

Hi,

One way.

Put 0.05 in a cell and give the cell a name (e.g. Factor)

then in any other cell you can enter =16*factor and get the answer you require
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Matt Richardson]

Hi,

One way.

Put 0.05 in a cell and give the cell a name (e.g. Factor)

then in any other cell you can enter =16*factor and get the answer you require
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

Hi.

You could add some vba code to the worksheet in question on the
Worksheet_Change event:-

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

Dim myValue

If Not Application.Intersect(Target, Me.Range("A1:A10")) Is Nothing
Then
myValue = Target.Value
myValue = myValue * 0.5
Target.Value = myValue
End If

Application.EnableEvents = True

End Sub

Just change the range details in the commas to tell the code which
cells to embed the formula into.

Hope this helps.

Matt
http://teachr.blogspot.com

 

[Gord Dibben]

Not within the same cell unless you were to use VBA.

See Mike's reply for use of a helper cell.

For the VBA part...............

Right-click on the sheet tab and "View Code"

Copy/paste this code into that module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "A1:A10" 'adjust to suit
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value * 0.05
Application.EnableEvents = True
End With
End If
End Sub


Gord Dibben MS Excel MVP