AutoComplete vs. Section Headers

[Timma]

I have a customer (I'm in tech support) that wants to know why sometimes in
Excel he can enter the first few characters of a word and Excel will
AutoComplete the rest of the word for him but sometimes it won't. I've
determined that he is having this problem because of section headers. As
long as the word he wants to AutoComplete already exists within a section,
AutoComplete will work in that section; if the word does not yet exist in
that section, AutoComplete isn't going to work.

My question is, does anyone know any tips or tricks or better formatting
methods to get around this? This is a rather lengthy spreadsheet and there
are section headers galore.

Thank You!!

 

[Tyro]

I have no idea what a section header is. If you try to select from a list
in the column above your entry, Excel will show you the entries above but
stop if a cell is empty. For example if cell A1 contains abc and cell A2
contains def and cell A4 contains ghi and cell A5 contains jkl if I enter in
cell A3 a, Excel will show abc to choose from. If I enter in A6 g, Excel
will show ghi to choose from. In other words. Excel shows all cells above
that meet the beginning letters of the entry, but terminates when a cell
above is empty.

Tyro

 

[Timma]

Yep, that is very true, and this spreadsheet has many blank lines that
they've colored grey to make it more readable, so the first thing I'd
recommend is getting rid of the blank lines.
I tried getting rid of all the blank lines, but it still doesn't
AutoComplete as anticipated. I'm sure it's because of the section headers
(actually called list header rows).

From Microsoft KB (Paraphrased):
If the characters in cell A1 are all uppercase, and the characters in cell
A2 are all lowercase or Mixed Case, Microsoft Excel determines that row 1 is
a list header row. The algorithm for the AutoComplete feature does not
generate suggested text if the first row is considered a list header row.

So, I guess my question becomes, does anyone know how to convert a list
header row to mere text such that autocomplete will work?

 

[Ken Johnson]

Yep, that is very true, and this spreadsheet has many blank lines that
they've colored grey to make it more readable, so the first thing I'd
recommend is getting rid of the blank lines.
I tried getting rid of all the blank lines, but it still doesn't
AutoComplete as anticipated. I'm sure it's because of the section headers
(actually called list header rows).

From Microsoft KB (Paraphrased):
If the characters in cell A1 are all uppercase, and the characters in cell
A2 are all lowercase or Mixed Case, Microsoft Excel determines that row 1 is
a list header row. The algorithm for the AutoComplete feature does not
generate suggested text if the first row is considered a list header row.

So, I guess my question becomes, does anyone know how to convert a list
header row to mere text such that autocomplete will work?

Hi Timma,

Try this trick out...

AutoComplete is not stopped by blanks if the column is adjacent to a
continuous column of entries.
Insert a column next to the column requiring AutoComplete, type any
entry into the top cell of the inserted column then fill it down as
far as needed, then hide that column.
I too am unfamiliar with the section headers you mention, but the
above trick might still work.

Ken Johnson