I want to enter a figure &excel makes last two digits decimals

[Guest]

for speeding up data entry I want to enter the figures without a decimal
point, and have excel make the last two digits decimals. e.g. key in 3925
which will desplay as 39.25. I've done this before but forget how! Its a
custom number format I think
Thanks

 

[Guest]

Tools>options>edit tab>fixed decimals & set to 2 places
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Guest]

thanks that works. It must apply to the whole spreadsheet I guess. Suppose I
wanted just one column to behave in this way?

 

[Guest]

=A1/100

Copy down as far as required

 

[Gord Dibben]

For one column you could use event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Column = 1 Then
If Not IsNumeric(Target.Value) Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Target
.Value = .Value / 100
.NumberFormat = "0.00"
End With
End If
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.

As written, works only on Column A. Change the Column = 1 to whatever column
you wish.


Gord Dibben MS Excel MVP