MEMORISING TEXT IN EXCEL COLUMNS

  bartonside 21:47 28 Apr 2006
Locked

I have recently started using Excel to catalogue books which is mostly text entries - title, author, publisher and so on. I have noticed that, as long as I work from the same basic file, it will remember most of the text entries but not all. It sometimes does not refer back and call up a previous entry until four or five letters have been entered, sometimes a couple. This is a useful facility as it saves typing time but I cannot find, on a brief survey, any reference to this feature in the Help section. Is there a way of ensuring that it remembers and recalls every previous entry in a column? If so, it would speed up my work considerably.

  mammak 21:55 28 Apr 2006

Enter VOGtm.

  VoG II 21:55 28 Apr 2006

Excel is a bit strange in this respect. If you enter 'horse' then the next time you enter 'h' it will offer to enter 'horse'. But if you then enter 'house' it will take a while before it will offer you either 'horse' again or 'house'

If you really want to speed up your work then I can write an Excel macro for you that will autocomplete (without any prompting) based upon the initial character typed. Please post back.

  VoG II 21:58 28 Apr 2006

I'm beginning to think that mammak is my alter ego :o)

  bartonside 22:21 28 Apr 2006

Yes, VoG, that would be helpful. I am not sure if I understand Macros but I will have a go!

  VoG II 22:29 28 Apr 2006

OK bartonside, it is dead simple since you need to know nothing!

What I need from you, please, is a list of columns and the entries that might be placed in them. For example

A: Yes, No

B: Chops, Trifle, Spuds

C: could be anything

D: Dogs, Cats, Geese

So, for example if you typed a 'c' in Column C it would autocomplete with 'Cats'. Anything in Column C would be ignored - this might be an address for example.

  VoG II 22:31 28 Apr 2006

So, for example if you typed a 'c' in Column D it would autocomplete with 'Cats'. Anything in Column C would be ignored - this might be an address for example.

  bartonside 09:25 29 Apr 2006

I'm not quite clear about this, VoG. The columns I in which I most need to use auto complete are book titles, authors, publishers and keywords (an indication of a non-fiction book's subject). Other columns can be dealt by using fill down and some are letter codes so just need N, L, V etc. Now, titles and so on are infinitely variable - can a Macro cope with this and can it be used on an existing spreadhseet? Your advice would be appreciated.

  bartonside 09:25 29 Apr 2006

I'm not quite clear about this, VoG. The columns I in which I most need to use auto complete are book titles, authors, publishers and keywords (an indication of a non-fiction book's subject). Other columns can be dealt by using fill down and some are letter codes so just need N, L, V etc. Now, titles and so on are infinitely variable - can a Macro cope with this and can it be used on an existing spreadhseet? Your advice would be appreciated.

  bartonside 09:27 29 Apr 2006

Oops! Sorry, entered response twice!

  VoG II 09:43 29 Apr 2006

A macro can deal with, for example, when you want to enter Y, N or D in a cell and have it autocomplete to Yes, No or Don't know. So it would deal with keywords and possibly publishers but not titles or authors - you would have to rely on the built-in autocomplete for those. A macro would work on an existing worksheet - basically it would run when you had typed something into a cell. This is the code for the Y, N, D example

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

In your case, to avoid erroneous substitutions being made, we would need to modify the above to check which column the entry was in and take no action if it was the title or author column.

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

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Inside the iMac Pro - Apple's most powerful Mac yet

iMac Pro release date, UK price & specs

Football : comment regarder la Ligue 1 en direct ?