When I create a new spreadsheet by cutting and pasting rows from other spreadsheets, frequently (usually? always?) the auto-complete function doesn't seem to work. I'm talking about when I start to type data into a cell and if as I type it matches a previous cell earlier in the same column it will auto-complete the rest of the cell.

The auto-complete ignores the cells that were filled by cut and paste, but does fill from cells that I have typed. In other words, if "King, Stephen" is pasted from another spreadsheet, the next time I type "Kin..." I get no auto-complete but have to type the whole thing. However, if I subsequently have yet another "King, Stephen" to enter in that column, it will auto-complete using the one I typed as a source.

What are the rules on how the auto-complete is supposed to work? And, yes, the "Enable AutoComplete" box is checked in Tools-->Options-->Edit.

tanstaafl.

I just tried this and the auto complete works for me. Can you post a sample file that it doesn't work on?

I find it annoying that auto complete doesn't work if you have an empty cell above the current cell.

Edit: Please move thread

What happens if you save the file after you've pasted the data?
Will it autocomplete then?
If not, try closing the file and re-opening it.

Worth a shot, I guess.

Not easily, and it is data that I'd prefer not to publish.

The file is large (1800+ rows) and the problem is erratic and intermittent.

I've been playing with it and experimenting, and the problem arises when I am adding rows to the bottom of the file. The file is sorted on the first column, with new obviously unsorted rows added to the bottom. If I add a new cell starting with a letter near the end of the alphabet so that it is auto-completing from a cell that is physically near by, it is more likely to auto complete than if I add a cell starting with A or B. But not always.

I experimented by adding new cells (rows) at the bottom that matched cells known to exist above, and sometimes it would auto complete, other times not, and sometimes it would auto complete a cell that it had refused to auto-complete less than a minute earlier. I would try and match "Bohjalian", and it would not auto complete, so I would erase that and try "Blackstock" and that would auto complete, then I'd try "Bohjalian" again... and it would then auto complete. It's as if, after failing to auto complete (and deleting the "failed" cell), if I managed to successfully auto complete a different cell then the first one (the failed attempt) would work.

So, the auto complete is working. Sort of. But not reliably or consistently.

tanstaafl.

The auto-complete function does not bridge empty cells. This allows a sheet to have several tables, of differing structure, not cross-pollinate auto-completions.
It's a feature.

Filling all the empty cells with a single quote mark (') will lend continuity. When adding rows, prefill all the cells with 's. (edit: A space will work as well)

Leading 's are formatting marks that have been around since the dawn of spreadsheets. (Try Visicalc) They are non printing and signify that the cell contents should be treated as a string.

A number entry preceded by a ' will be omitted from the sum() function. Excel typically flag's that with a warning.