auto complete only for one row down?

[Guest]

It seems that auto complete has previously worked for me when there was a
separation of several rows at least. Today it will only auto complete for
the next cell down. Is this right or wrong?

 

[Peo Sjoblom]

No, that is how it works, if there is a blank cell between then it won't
work.

 

[JE McGimpsey]

Neither right, nor wrong.



Autocomplete operates on the "active data region", i.e., there must be
no completely blank rows between the list and your entry.

For example, when making an entry at <entry>,

Autocomplete works

A B
1 data1 1
2 data2 2
3 data3 3
4 <blank> 4
5 <blank> 5
6 <entry> 6

Autocomplete doesn't work:

A B
1 data1 1
2 data2 2
3 data3 3
4 <blank> <blank>
5 <blank> 5
6 <entry> 6





article <[email protected]>,

 

[Peo Sjoblom]

Interesting, I didn't know that, always thought it was single column
centric, not region.

 

[kprice999]

Interesting, I didn't know that, always thought it was single column
centric, not region.

I have a sheet open right now that will auto complete only in a
scattered handful of cells further down the column that are not
"connected" to the cells above. I too would like to have it auto
complete if it finds a match ANYWHERE in that column. Strange that a
FEW cells will do it, but I can't find any rhyme or reason why... the
formating, etc all looks the same. Copying a "magic" cell doesn't
seem to carry it's magic with it, and I can't seem to duplicate the
effect. Please forward any solutions you may have come across.

 

[kprice999]

I have a sheet open right now that willautocompleteonly in a
scattered handful of cells further down the column that are not
"connected" to the cells above. I too would like to have itautocompleteif it finds a match ANYWHERE in that column. Strange that a
FEW cells will do it, but I can't find any rhyme or reason why... the
formating, etc all looks the same. Copying a "magic" cell doesn't
seem to carry it's magic with it, and I can't seem to duplicate the
effect. Please forward any solutions you may have come across

CORRECTION:
FYI: I found the "rhyme or reason". While it appears that cells will
auto complete only to the contents of of other cells in the same
column that are directly above the row you are in, you CAN have
totally blank cells between your new cell and the ones above you that
you are trying to get an autocomplete out of. However, there must be
"some" connection to that region of cells, EVEN IF IT IS IN ANOTHER
COLUMN FAR AWAY FROM YOUR COLUMN OF INTEREST. i.e.

A B C D E
1 john
2 bob 0 0 7
3 fred 9
4 jane 12
5 0
6 0
7 0
8 jane 0 0 0
9

Here, "Jane" WILL auto complete into cell A8 because of the around the
block connection across row 8, up column D, and back on row 2.
FYI: I desire autocompletes in a special column of my sheet that I am
constantly inserting and removing row and there will always be some
blank areas. I created a very narrow column column just before it
each cell filled with a period (a blank space bar entry will work
too). I simply copy that period down whereever I insert a row, or
from top to bottom of my sheet and autocomplete functions like one
would think it would.

 

[JE McGimpsey]

[/QUOTE]

CORRECTION:
FYI: I found the "rhyme or reason".

Yup, that's what I meant...

 

[Guest]

I was almost certain that this was not the case for v. 2003. I could skip
lines/rows and still have it auto complete.

Has this truly always been this way, or is this new to v. 2007 and later?

 

[JE McGimpsey]

Frankly, I don't remember when auto complete was introduced, but I think
that this behavior has been consistent at least since XL97/98.

 

[Bob I]

My 2003 don't autocomplete if you skip a row.

I was almost certain that this was not the case for v. 2003. I could skip
lines/rows and still have it auto complete.

Has this truly always been this way, or is this new to v. 2007 and later?

:

 

[istraus]

I am using 2000, and have seen this behavior in more than one spreadsheet
which i use for logging in resposes. In all cases my left column is a form
num,ber, so there are no completely blank rows.
In one column, autocomplete works even though there may be many blank cells
above it.
In another column farther to the right, autocomplete never works unless the
cell immediately above it is already filled in.