Auto Fill...Is it Possible?

[F. Lawrence Kulchar]

In column A, I have, in alphabetical order, say, 1000 cities...in A1 through
A1000.

How is it possible to scroll directlt to "Manila", for example??

If I begin to type Manila in a cell, say cell D1, can I 'scroll' directly
to cell A622 (where Manila is)? Obviously, I have other pertinent data
relative to Manila in cells B622 and C622!

Thanks,

FLKulchar

 

[FSt1]

hi
not to my knowledge. you might have better luck typing manila into the find
box, keyboard shortcut Crtl+F.

Regards
FSt1

 

[Pete_UK]

Here's one approach - not exactly what you are asking for, but
similar:

Assuming names in A1:A1000 in alphabetical order, use B1 to enter the
name you are looking for. Put this formula in C1:

=MATCH(B1&"*",A1:A1000,0)

It will return the relative row where a match is found (note the use
of the wildcard in the formula).

Then put this formula in C2:

=HYPERLINK("#A"&C1,"Jump")

This will put the message "Jump" in the cell, and when you click on
that cell it will take you to the cell that matches with your input in
B1.

So, imagine you have Manchester in A50 and Manila in A51. If you type
"M" in B1, or "Ma", or "Man" (without the quotes), and then click on
C2 you will jump to the cell containing Manchester, as that is the
first match. However, if you type "Mani" or any further characters
that make up Manila then you will jump to that cell.

Of course, you could combine the formulae into one - put this in B2:

=HYPERLINK("#A"&MATCH(B1&"*",A1:A1000,0))

and with this one you can see which cell you will be jumping to.

Hope this helps.

Pete

 

[Don]

Another option - insert a column and do the formula =LEFT(B2,2) where column
B is where the cities are. Then Autofilter and you should be able to filter
on "MA" and get all cities with MA. Of course you could autofilter on cities
and find Manila but assume from 1000 that it would be longer list. I think
some versions had a limit of how many items can be in a auto list.

the other options are nice if you know exactly what you want.

good luck

 

[FLKulchar]

Thank you very much...very fine answer.

FLKulchar
Here's one approach - not exactly what you are asking for, but
similar:

Assuming names in A1:A1000 in alphabetical order, use B1 to enter the
name you are looking for. Put this formula in C1:

=MATCH(B1&"*",A1:A1000,0)

It will return the relative row where a match is found (note the use
of the wildcard in the formula).

Then put this formula in C2:

=HYPERLINK("#A"&C1,"Jump")

This will put the message "Jump" in the cell, and when you click on
that cell it will take you to the cell that matches with your input in
B1.

So, imagine you have Manchester in A50 and Manila in A51. If you type
"M" in B1, or "Ma", or "Man" (without the quotes), and then click on
C2 you will jump to the cell containing Manchester, as that is the
first match. However, if you type "Mani" or any further characters
that make up Manila then you will jump to that cell.

Of course, you could combine the formulae into one - put this in B2:

=HYPERLINK("#A"&MATCH(B1&"*",A1:A1000,0))

and with this one you can see which cell you will be jumping to.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete