drop down list autocomplete

[mikelmendit]

I'm transcribing a census in Excel2007 and I find that the autocomplete
option is very useful, However my problem occurs when I have a common name
like John, and along comes johanna, or as in place names, Lambeth, which
occurs very often and then I come across Lamberhurst, as I'm transcribing
rows in their many hundreds, I find this a pain in the b...I know that I can
uncheck autocomplete for that single entry, but if only I could find a way of
removing/deleting Johannah, Lamberhurst et al from the autocomplete drop down
list, it would save me hours of unpaid voluntary work
I've searched and searched for the answer.I bet someone out there can help me!

Cheers, Mike

 

[Spiky]

I'm transcribing a census in Excel2007 and I find that the autocomplete
option is very useful, However my problem occurs when I have a common name
like John, and along comes johanna, or as in place names, Lambeth, which
occurs very often and then I come across Lamberhurst, as I'm transcribing
rows in their many hundreds, I find this a pain in the b...I know that I can
uncheck autocomplete for that single entry, but if only I could find a way of
removing/deleting Johannah, Lamberhurst et al from the autocomplete drop down
list, it would save me hours of unpaid voluntary work
I've searched and searched for the answer.I bet someone out there can help me!

Cheers, Mike

How does the autocomplete slow you down? Even if you have to type more
characters than for some words, like "Lamber", isn't that still less
than "Lamberhurst"?

 

[mikelmendit]

Thanks for replying, but the point is repetition.......for instance Lambeth
is just one of very many instances, I might enter lambeth as place of birth
50 times out of 400 entries in each piece of transcription, by typing L
lambeth would occur in cell, so with lamberhurst, I have to overtype
practically the whole word 50 times............all I want to know is if I
happen to make a mistake by entering this sort of word, how can I edit it
from the cell drop-down list
I have a 720 page manual on 2007 here,but cannot find reference to editting
this list :-(
Still need your help ............Thanks

 

[Gord Dibben]

The autocomplete list is comprised of the data in contiguous cells above or
below the point at which you are typing.

To remove any from the list, delete from the cell(s) above or below.

If you have made a mistake when you are choosing from the list just hit CTRL
+ z to undo.


Gord Dibben MS Excel MVP

 

[mikelmendit]

Thanks Gord but I'm no closer to the answer.......I simply want to remove
unwanted items from the drop down list which is revealed when right clicking
in a cell, I tried doing what you suggest but to no avail, instead of
deleting from list it immediately places the chosen deletion in the active
cell and hides the list.........fortunately I haven't a lot of hair but a lot
of patience!
I've found a way around this by prefixing alpha numerics followed by the
entry that I want to avoid using, X1 johanna for instance ( the more commonly
encountered John will therefore take precedence when typing j) which means on
completing this cencus district usually 700 to 1000 rows I will then have to
edit by removing these X1 fonts before sending off for analysis...Hey....And
I'm doing this for free!
I know.........I'm a pain in the butt, right?

 

[Gord Dibben]

Right-click>Pick from list is a list of unique items in contiguous cells
above or below.

The only way to remove anything from that "pick from list" is to remove it
from the cells.

I don't know any other method of removing.

Sorry, Gord

 

[Spiky]

Thanks Gord but I'm no closer to the answer.......I simply want to remove
unwanted items from the drop down list which is revealed when right clicking
in a cell, I tried doing what you suggest but to no avail, instead of
deleting from list it immediately places the chosen deletion in the active
cell and hides the list.........fortunately I haven't a lot of hair but alot
of patience!
 I've found a way around this by prefixing alpha numerics followed by the
entry that I want to avoid using, X1 johanna for instance ( the more commonly
encountered John will therefore take precedence when typing j) which means on
completing this cencus district usually 700 to 1000 rows I will then haveto
edit by removing these X1  fonts before sending off for analysis...Hey.....And
I'm doing this for free!
I know.........I'm a pain in the butt, right?

That's about all you can do. You have both these words needed, so both
of them need unique characters in order to shorten the auto-entry.
Otherwise, you are asking the computer to read your mind and pick
which of these similar terms you want in a particular cell.

Let me explain. Say you did have a way to delete Lamberhurst from your
pick-list. Then Lambeth is easier. So...what happens next time you
need Lamberhurst? You have to type the entire 11 character word again.
You can't have both of these at your fingertips in under 3 characters
without a workaround.

Another workaround might be a macro system to enter certain words that
give you this issue with a certain keystroke combination. But then
you'd have to remember those combos. And you'd want to find keystroke
combos that aren't already in use by Excel.