Auto predict values in a column

[excel_2009]

hi excel gurus,

I was wondering if someone could help me with the following,

i have the following worksheet:
col A| col B
Magazine | Batman
DVD|Spiderman
Video| Superman
Poster|Wonderwoman

A user enters a comic character but i want it possible so that if a user types in "bat" or even "b" then it'll fill in the cell with batman, some sort of v lookup? from another sheet containing a list of all the characters

is that possible?

thank you!

 

[RichardMGreen]

Look into Data Validation.
You can pre-define the list that a user can choose from.

 

[excel_2009]

Hi Richard,

That seems a good idea, but i'm not sure if it'd be efficient in a sense that there are 15,000 rows, would a user have to select a drop down? or could they type in the character name partially and it'd autofill/suggest the character in the cell?


thank you for the prompt reply!!

 

[Dryver14]

an alternative to the above is to go into options
Proofing
Auto correct

this gives you the option to type in some letters or a letter and it will complete the word when you exit the cell

you key in your letter in the box next to it add the word and then click add at the bottom of the page



This method is not perfect

 

[RichardMGreen]

Not sure about the autofill, try it and see.
You don't need to have a drop-down, you can use validation to ensure things are typed in correctly, etc.

 

[excel_2009]

Not sure about the autofill, try it and see.
You don't need to have a drop-down, you can use validation to ensure things are typed in correctly, etc.

Hi Richard,

unfortunately it only allows the drop down, if i type in bat it doesnt select batman, so i can either manually type in batman or select it from the drop down list

 

[Dryver14]

did you try my method?

 

[excel_2009]

did you try my method?

hi dryver,

i cant find the options > Proofing >Auto correct where cant it be found? im using excel 2007

 

[Dryver14]

click the excel button in the top left hand corner of the spreadsheet as though you were going to print then select options from the bottom of the page,


this option does not auto fill as you type it completes the word when you leave the cell

 

[shg]

I have an add-in that does this, if you're comfortable with that approach.

There's an example workbook that uses it at http://www.box.net/shared/e8dbbple34.

If you like it, there's a ReadMe at http://www.box.net/shared/4qxybepxxr, and the add-in itself is at http://www.box.net/shared/ono8ni9xvr