how can i type ONE letter and get a full word entered

[mythiey]

hi there,

im looking for a way to be able to type one letter into a cell, and then it auto compleate the word for me, such as follows

O = OFF
H = Holiday
S = Sick
B = Bankholiday

how would i go about doing this?

also, once i have worked out this, i would like to be able to have one cell count how many Holiday entreys there are upon the worksheet and then another cell for sick, etc, how would i also go about doing this?

 

[Gerald Higgins]

For the count part, take a look at the COUNTIF function.

 

[dave3009]

For the entry part you could look at the autocorrect feature.

 

[Weaver]

The auto complete might work if you have the 4 entries typed into cells in the column you're working in. Otherwise you might need some visual basic (macro) code.

An alternative might be to set up cell validation. Select the cells you want the options to appear in, choose data -> validation and select 'list'

then type

OFF,Holiday,Sick,Bankholiday

into the souce textbox

If you want to count the occurences of some text in column A, say

=COUNTIF(A:A,"OFF")

HTH

 

[Gerald Higgins]

dave3009 - interesting idea. Seems to work, but also seems to work in every file, once set up, so maybe not ideal . . .

 

[dave3009]

Yes and it's not all that transferable either if I recall.

How about some event code

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.value
    Case "o": Target.value = "Overtime"
    Case "h": Target.value = "Holiday"
    'Add more cases
End Select
End Sub

If you wanted it to act on capital letters then just add them in as cases