I’ve noticed that the autocomplete in Excel only completes words that are already in the spreadsheet somewhere else. Is there any way to autocomplete words not already present?
Also, is there any way to make Excel ignore the fact that I have two functions the are circularly defined? I have a function that maps a word to a number called wordtocode, and another that converts a number to a word called codetoword. I set up two cells so that the one on the left is defined as codetoword(B1) and the one on the left is wordtocode(A1). This way both cells start out empty, but if I fill in one the other is automatically completed. However, excel pops up an error message because the functions are defined in terms of each other. The sheet still works, but the error message appears any time I change something. Can I make it go away and not come back?
How many characters do you want to type before being prompted? I’m playing devils advocate a bit here and saying (at least from the PocketPC interface experience) autocomplete can be auto-annoying. Do you have a specific list of words you want autocompleted? If so, there are a few ways to handle it using macros w/arrays, vlookup refs to cells off sheet, OLE and DAO. If you don’t have a set in mind, I have to question the logic you intend to apply to the autocomplete suggestions (or your sanity).
Yes, I have a specific list of about 100 words to be used for autocomplete. It’s actually not my idea to use autocomplete, but I’m setting this up for someone else who wants it.
How many characters do you want to type before being prompted? I’m playing devils advocate a bit here and saying (at least from the PocketPC interface experience) autocomplete can be auto-annoying. Do you have a specific list of words you want autocompleted? If so, there are a few ways to handle it using macros w/arrays, vlookup refs to cells off sheet, OLE and DAO. If you don’t have a set in mind, I have to question the logic you intend to apply to the autocomplete suggestions (or your sanity).
Circular refs:
You can avoid the circular reference by using VBA macros and the Worksheet_Updated event. (I’m going from memory here & the event name may not be exactly right).
If VBA is not something you’re used to you can spend hours getting the hang of it. The tradeoff may not be worth it.
Other than VBA, one approach is to use 3 cells; one input for code, one input for number & one output for both. Set the output cell to something like =If(IsEmpty(CodeInput), NumberToCode(NumberInput),CodeToNumber(CodeInput)).
With that formula, code input takes precedence over number input if both are present. You can easily rearrange it to produce the opposite effect, or even add an outer If to drive an error message if they’re both occupied (or both empty).
Autocomplete:
Does autocomplete just use words already in the sheet or already in the workbook? If workbook, you could add an extra sheet with just a list of words in it. IIRC you can even hide the sheet to avoid confusing the naive.
If they have to be on-sheet, thats not so tough either; just include the words way out to the lower right corner of the sheet where they won’t be disturbed by typical end-user row/column delete/insert actions.