Unoffical empeg BBS

Quick Links: Empeg FAQ | RioCar.Org | Hijack | BigDisk Builder | jEmplode | emphatic
Repairs: Repairs

Topic Options
#330185 - 18/02/2010 00:31 Excel auto-complete question
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5546
Loc: Ajijic, Mexico
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.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#330189 - 18/02/2010 01:17 Re: Excel auto-complete question [Re: tanstaafl.]
Attack
addict

Registered: 01/03/2002
Posts: 599
Loc: Florida
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


Edited by Attack (18/02/2010 01:18)
_________________________
Chad

Top
#330190 - 18/02/2010 01:23 Re: Excel auto-complete question [Re: tanstaafl.]
Robotic
pooh-bah

Registered: 06/04/2005
Posts: 2026
Loc: Seattle transplant
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.
_________________________
10101311 (20GB- backup empeg)
10101466 (2x60GB, Eutronix/GreenLights Blue) (Stolen!)

Top
#330224 - 18/02/2010 22:11 Re: Excel auto-complete question [Re: Attack]
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5546
Loc: Ajijic, Mexico
Originally Posted By: Attack
Can you post a sample file that it doesn't work on?


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.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#330230 - 19/02/2010 01:00 Re: Excel auto-complete question [Re: tanstaafl.]
gbeer
carpal tunnel

Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
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. wink

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.


Edited by gbeer (19/02/2010 01:05)
_________________________
Glenn

Top