Formula embeded cell

[TBLO]

I would like to have formula in a cell that when 5.69 is entered it is converted to .0569. In other words I would like the data entered in a cell to be changed or divided by 100 to move the decimal to 100's.

 

[GlennUK]

It sounds like you are asking to have a formula in a cell that is also used for data entry. That's not possible. To get the behaviour that you've described you'd need VBA ( although I could see this being very confusing for any user ... suddenly having an entry change for no apparent reason ) .... the worksheet_change event could be used for this, if that's really what you want to do. Is that really what you want to do?

 

[mancemonster]

Couldn't you just change the format to %?

Then any value entered will look like 5.69% but for any calnculation purpouses would be 0.0569..

Regards


Roger

 

[TBLO]

Glenn,
That is exactly what I want it to do. The data is collected and entered in one form, then I want to form to change to reflect a decimal point change. This cell is then pulled to another for a pass/fail criteria.
Mancemonster,
Changing it to percent doesn't calculate the data the way I need it to. The data is collected from a machine in one form i.e. 1880 then when its entered I as such I need it to read as 1.88. As stated above the cell is linked to another for a Pass/Fail indication.
Thanks to you both.

 

[GlennUK]

Are the figures to be entered without a decimal point? Is the number of places of decimal to be applied the same for every cell?

 

[TBLO]

Not nesseccarily. The data entered may be 188.5 or 18.85. That is why I think I need a formula. And yes every cell in a range would have this applied.

 

[GlennUK]

Right-click the sheet tab, and choose View Code, and paste this into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        On Error GoTo diverror
        Target = Target / 100
        Application.EnableEvents = True
    End If
Exit Sub
diverror:
Application.EnableEvents = True
End Sub

 

[TBLO]

Mr. GLennUK,
YOU ARE AWESOME! That is right on the money mister!
Thanks a bunch!

 

[TBLO]

GlennUk,
One little issue, I am using this on a form where the date is entered and the date is defaulting to 2/9/1901 7:40:48 AM. Any idea how to rectify this?

 

[GlennUK]

If you are putting values from a form into the worksheet, switch off events for when you don't want worksheet events to happen. You put:

Application.EnableEvents = False

before the code that changes the worksheet, and put:

Application.EnableEvents = True

afterwards.