AutoComplete for Cell Value

[TQ]

Hi, I would like to key in data which duplicate before, I notice that some
will be auto complete but some not, I already check the autocomplete function
in Tools.

Anyone who know the reson and can provide solutions ? All my data start wz "
- " i.e - Willam or - Robert & etc.

Thanks.

 

[ozgrid.com]

If your list is Contiguous (no blanks) then AutoComplete will kick in.

 

[TQ]

Thanks for reply, sorry , I cant get what your mean, can you explain more
detail or show me example ? Thanks.

 

[ozgrid.com]

Enter "Dave" into A1 and Enter. Now start typing "Dave" in cell A2 and
AutoComplete will kick in. Now select A4 and start typing "Dave" and
AutoComplete will NOT kick in because you have blank cells in your list.

 

[TQ]

Get it, thanks, but how to solve this kind of problem ? Solutions ? Thanks.

 

[ozgrid.com]

Don't have blank cells between entries.

 

[Jacob Skaria]

The match that Excel fetches is nothing but the contents of the immediately
adjacent cells above the one in which you are entering the data. It stops
trying to match entries when a blank cell is reached. Incase you have
information from A1 to A5 and then from A7 to A10; if you make a new entry in
A11, excel only try to match entries in A7:A10.

If you have blank cells between the range try the below workaround. I am not
sure whether this would help (depends on how your data is managed.).

--Select the column. Press F5>Special> select Blanks> Hit OK. This would
select all the blank cells in the column.

--The active cell would be the first blank cell in the column. Active cell
will have a white background even when selected.

--Press apostrophe (') key. Dont press Enter key instead press Ctrl+Enter.

--Now try autocomplete in a new cell. As Dave mentioned when you enter new
data make sure you dont leave blank cells.

 

[TQ]

Thanks Jacob, actually my data don have any blank field between the cell. I
jz don understand why when I key in the repeated data, some will autocomplete
and some not, I suspect it because start with " -" , I don't know whether my
assumption correct.

 

[Jacob Skaria]

--The cells must contain either text or a combination of text and numbers.
Cells that contain numerics, dates, or times are not considered.

--Excel displays the matched entry when you hit the first alpha character.
Suppose you have an entry as " -apple" ; excel displays the matched entry
only when you enter " -a"..Again if there are more than one entry starting
with " -a" excel will not display the matched entry unless you type in the
other characters so that it filter down to match one entry..

--Also if you have entries such as below excel will not be able to find ** a
** matched entry and hence not displayed...Hope this helps...

-a
-ap
-app
-appl
-apple
-apples

 

[TQ]

Thanks Jacob, I belive my case is more than one entry starting with the
similar begining . Anyway, any solutions to solve this kind of problem ? It
is impossible for me to change the similar name of customer. Is tat possible
excel will list out few similar in a list to let me choose ? Thanks.

 

[Jacob Skaria]

After typing in the first few characters if you dont get a unique matched
entry hit Alt+Enter to choose from the dropdown list..

 

[Jacob Skaria]

Correction..

Alt+DownArrow to choose from list. This will ignore the blank entries too.

 

[TQ]

Tried, but nothing show in the list......any mistake i make ?

 

[Jacob Skaria]

Try this.

--Copy paste the below to a new workbook Sheet1 A1:A5
--From cell A6 hit Alt+DownArrow

a
ap
app
appl
apple

 

[TQ]

Hi Jacob,

I found out the reason, if come to the field which after the blank field,
even Ctrl + down also list will not appear, anyway, thanks Jacob.

 

[ozgrid.com]

I told you that couple of hours ago.

 

[TQ]

Yes, how about I cant skip the blanks field in between ? Solutions to use the
autocomplete with even blank field in between ?

 

[ozgrid.com]

You can't. When you feel the urge to leave a blank cell in a column, don't
and increase the row height instead. Blank cells/rows/columns between
related data is a big spreadsheet design mistake.

 

[TQ]

OK, noted with thanks.

You can't. When you feel the urge to leave a blank cell in a column, don't
and increase the row height instead. Blank cells/rows/columns between
related data is a big spreadsheet design mistake.