Excel - autocomplete

  Newuser1970 23:14 29 Jun 2006

I complete income and expenditure spreadsheets monthly. In the expenditure sheet there is a column showing the firm or individual to whom the cheque is made out to. I have noticed that if the name is already in the list above the entry that I am filling in then when I enter the first letter it will autocomplete. Since most of my entries are to the same firms each month on separate sheets, I was wondering if there was a method of setting up a list so that entries would autocomplete when I enter the fist letter of the name.

  Noldi 07:06 30 Jun 2006

Me I use the auto correct function.
Tools Autocorrect.
eg. Replace No with Noldi, or Replace Mi with Microsoft.

  Noldi 07:11 30 Jun 2006

Sorry didnt explain that so good.
It does not have to be the same lettsre as the word it could also be Replace XX with Harry, just avoid using normal day uses suce as AC or DC etc.

  VoG II 08:26 30 Jun 2006

You could also consider using Data Validation.

  exdragon 08:40 30 Jun 2006

Just trying to think how I do it - I used to have a whole set of names and addresses which automatically printed out when I just entered, for example, the person's initials, preceded by the letter X (incase their initials appeard in a normal word)

Can't remember how I got the resulting data to display in columns down rather than rows across. I hope VoG™ or someone else can remember because it's beginning to bug me!

  VoG II 08:50 30 Jun 2006

One way click here

  VoG II 08:53 30 Jun 2006
  VoG II 09:25 30 Jun 2006

Right click the sheet tab, select View Code. Paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Target.Value) = "Y" Then
Target.Value = "Yes"
ElseIf UCase(Target.Value) = "N" Then
Target.Value = "No"
ElseIf UCase(Target.Value) = "D" Then
Target.Value = "Don't know"
End If
End Sub

Obviously you will need to substitute your desired firms for "Y", "Yes" etc.

If the names of the firms only go in one column (say E) then add the following before the first If statement:

If Target.Column <> 5 Then Exit Sub

  Woolwell 12:22 30 Jun 2006

I wonder if a custom list would be sufficient. Once you have have completed the first firm name you could drag and drop to fill in the remainder of the list

  Newuser1970 15:03 02 Jul 2006

My apologies for not replying earlier. First of all many thanks for all your suggestions. I have in fact tried them all out. All of them work but in the end I have found that in terms of time and ease of use a custom list seems to be the best outcome. Once again many thanks for your time and efforts.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HTC U12 Plus review: Hands-on

Best Android emulators for Mac

Comment utiliser l’Apple Store Éducation ?