Excel 2002: How to overcome inconsistancy in auto cell function ?

[Mr. Low]

Dear Sir,

Lets consider the following worksheet:

A
1 Penang 1
2 Penang 1
3 Penang 1
4 Penang 2
5 Penang 2 <-- Auto cell entry not functioning
6 Penang 2 <-- Auto cell entry not functioning
7 Tokyo 12
8 Tokyo 12
9 Tokyo 12
10 Tokyo 16
11 Tokyo 16 <-- Auto cell entry not functioning
12 Tokyo 16 <-- Auto cell entry not functioning


I find that the Auto Cell Entry function very well from A2 to A3.

However after I key in “Penang 2†at A4, and when I input “P†at A5, the
word “Penang 2 “does not come out automatically.

I face similar problem at Cell A11 and A12 after I change the entry to
“Tokyo 16 at cell A10.

Logically the same words should appear in the folowing cell if there is no
alteration in the preceeding cell.

May I know how to overcome the inconsistency of this function?

Thanks

Low

 

[Pete_UK]

The problem is that there isn't a unique value beginning with P (or
even Penang) after you put Penang 2 in cell A4, and autofill uses
unique values to suggest what the cell entry should be. If you entered
Pnang 2 instead, then autofill would be able to complete after you
type "Pn" (for Pnang 2) or "Pe" (for Penang 1). You could then use
Edit/Replace to change Pnang to Penang after you have typed them all
in. Similarly, you could enter Tkyo 16 in A10 and still retain the
autofill capability after two letters of input.

Hope this helps.

Pete

 

[Mr. Low]

Hello Pete,

Thanks for your info.


Low

 

[Pete_UK]

You're welcome.

Pete

Hello Pete,

Thanks for your info.

Low

--
A36B58K641






- Show quoted text -

 

[Mr. Low]

Dear Pete and the rest in the cummunity,

By the way, I think the proper auto fill function should be designed with
the following capability :

Data Entry
and press Enter
A Result in cell

1 Rose 22 Rose 22
2 R Rose 22
3 Osaka 62 Osaka 62
4 Hibiscus 36 Hibiscus 36
5 H Hibiscus 36
6 Orchard 48 Orchard 48
7 Carmella 54 Carmella 54
8 R Rose 22
9 O Orchard 48
10 H Hibiscus 36
11 C Carme4lla 54
12 Os Osaka 62


The function should be able to search the closest match in the column based
on the entry of the first few character

This would enable data entry to be done with less effort and more swiftly,
thus improving the office productivity


Thanks

Low

 

[Pete_UK]

It does almost this, apart from your row 9 - you would need to enter
Or to get Orchard 48. You are implying that it should match on the
number of characters entered with the (physically) closest entry
above, but not everyone would want that ability. By the way, with
longer lists there is also a limit as to how far back it looks for a
match - you need to enter more characters to get the match.

Pete

 

[Mr. Low]

Dear Pete,

I have tested the worksheet again today.

Overall, I think the current functionality is fair and acceptable.

Many thanks for MS for upgrading the program lately.

Previously I can only do autofill once after an entry.

Thanks for your explanation


Low

 

[Pete_UK]

You're welcome, though it has always had this functionality (I use XL
2000). If you leave blank cells above then autofill resets the ranges
it looks at - better to have contiguous data.

Pete