Data entry & Formula in same cell

B

Bridge

Is it possible to have a value entered into the same cell
that the formula resides.

For example... in cell A1 you enter 1 (for 1 foot)and
this same cell calulates how many inches (A1 equals 12)

One addition question is there a way to have a cell round
up to the nearest even number?

Thank you so much for your help.
 
F

Frank Kabel

Hi Bridge
1. without VBA code it is not possible to enter a value and a formula
at the same time into one cell.
2. If you number is stored in A1 use the formula
=EVEN(A1)

HTH
Frank
 
B

Bridge

Thanks Frank for the quick response.

Could you possible give me an example of a VBA code that
I could use.

Thanks!
 
F

Frank Kabel

Hi Bridge
put the following code in your worksheet module. To get there right
click on the tab name of your worksheet; select code and paste the
following

Private Sub Worksheet_change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value <> "" Then
.Value = .Value * 12 ' -> change this formula according to
your needs
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Note: I would not use this kind of code but use two different cells.
This code will overwrite the original value.

HTH
Frank
 
J

Jack Sons

Bridge,

Could this help?
Put the number 10 or any other number in A1 and this formula
=2*A1+N("this is text") in any other cell.
That cell shows you the result of the formula but when you click on it you
see the text in the formula bar above your spreadsheet.

Jack Sons
The Netherlands
 
Top