First Letters Grab
 

Hey All!

I am trying to do something in Excel that is available in
Access.

For example, I have a database on sheet1 with a bunch of
names and other columns.
Ex:
Bill
Bob
Billy
Boris
Brent
Brad

Now, on sheet 2, I would like to have some kind of tool
that would tell me what is available when I type the
begining of the name.

For example, If I type
"Br" I would like to have it say: choices are Brent and
Brad. (actually, in a Drop Down would be awesome!)
When I type "B", I would have all the Bs in the Drop Down.

Just like access does. It this something that is possible
in Excel?


Thanks in advance for the answers!

Pitbull :)

Perhaps this recent post by Debra Dalgleish might be of help:

Although data validation doesn't support autocomplete, there's a sample
file here that provides a combobox from which you can select one of the
values from the data validation list. In the combobox, you can enable
autocomplete:

http://www.contextures.com/excelfiles.html

Under 'Data Validation', look for 'Data Validation Combobox'

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Pitbull" wrote in message
...
Hey All!

I am trying to do something in Excel that is available in
Access.

For example, I have a database on sheet1 with a bunch of
names and other columns.
Ex:
Bill
Bob
Billy
Boris
Brent
Brad

Now, on sheet 2, I would like to have some kind of tool
that would tell me what is available when I type the
begining of the name.

For example, If I type
"Br" I would like to have it say: choices are Brent and
Brad. (actually, in a Drop Down would be awesome!)
When I type "B", I would have all the Bs in the Drop Down.

Just like access does. It this something that is possible
in Excel?


Thanks in advance for the answers!

Pitbull :)

You can bring the list over to the new sheet and create a
drop-down there. Assuming your source list is
called "mylist", on A1 of a new sheet, put:

=INDEX(mylist,SMALL(IF(ISNA(MATCH(LEFT(mylist,LEN
($B$1)),$B$1,0)),"",ROW(INDIRECT("1:"&COUNTA(mylis t)))),ROW
()))

and press ctrl/shift/enter. Now fill down as many rows as
your list contains elements.

Cell B1 is your parameter to create your list, such
as "B", "Bo", etc.

Now select C1, go to Data Validation, choose list, and
in the custom box put:

=OFFSET(A1,,,COUNTIF(A:A,"*"))

HTH
Jason
Atlanta, GA

-----Original Message-----
Hey All!

I am trying to do something in Excel that is available in
Access.

For example, I have a database on sheet1 with a bunch of
names and other columns.
Ex:
Bill
Bob
Billy
Boris
Brent
Brad

Now, on sheet 2, I would like to have some kind of tool
that would tell me what is available when I type the
begining of the name.

For example, If I type
"Br" I would like to have it say: choices are Brent and
Brad. (actually, in a Drop Down would be awesome!)
When I type "B", I would have all the Bs in the Drop Down.

Just like access does. It this something that is
possible
in Excel?


Thanks in advance for the answers!

Pitbull :)
.

How do you get rid of the #NUM! that appears in Col A and the last select in
the
Drop-down?


"Jason Morin" wrote in message
...
You can bring the list over to the new sheet and create a
drop-down there. Assuming your source list is
called "mylist", on A1 of a new sheet, put:

=INDEX(mylist,SMALL(IF(ISNA(MATCH(LEFT(mylist,LEN
($B$1)),$B$1,0)),"",ROW(INDIRECT("1:"&COUNTA(mylis t)))),ROW
()))

and press ctrl/shift/enter. Now fill down as many rows as
your list contains elements.

Cell B1 is your parameter to create your list, such
as "B", "Bo", etc.

Now select C1, go to Data Validation, choose list, and
in the custom box put:

=OFFSET(A1,,,COUNTIF(A:A,"*"))

HTH
Jason
Atlanta, GA

-----Original Message-----
Hey All!

I am trying to do something in Excel that is available in
Access.

For example, I have a database on sheet1 with a bunch of
names and other columns.
Ex:
Bill
Bob
Billy
Boris
Brent
Brad

Now, on sheet 2, I would like to have some kind of tool
that would tell me what is available when I type the
begining of the name.

For example, If I type
"Br" I would like to have it say: choices are Brent and
Brad. (actually, in a Drop Down would be awesome!)
When I type "B", I would have all the Bs in the Drop Down.

Just like access does. It this something that is
possible
in Excel?


Thanks in advance for the answers!

Pitbull :)
.

"Jim May" wrote
How do you get rid of the #NUM! that appears in Col A
and the last select in the Drop-down?

Think you might have missed this part of
Jason's instructions in the construct:
....
Now fill down as many rows as
your list contains elements.

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

I have in Range G1:G6 the six names, with name "mylist" assigned to it.
I have in Range A1:A6 (the same six row length):

=INDEX(mylist,SMALL(IF(ISNA(MATCH(LEFT(mylist,LEN( $B$1)),$B$1,0)),"",ROW(IND
IRECT("1:"&COUNTA(mylist)))),ROW())) << { } surrounds all formulas
(machine-entered, also (meaning not done manually))..
In C1 I went to Data Validation and selected LIST and entered
=mylist (in box)

At this point with cell B1 blank, my cells A1:A6 all show #NUM! - Clicking
the C! drop-down shows only #NUM!

when I enter (in B1) say "Br" -- suddenly my cell A1 changes to Brent and
Cell A2 changes to Brad (Cells A3:A6 remain #NUM! --- with my C1
(Combo-box) still blank
I go there click on the drop-down and wala there appears Brent, Brad and
#Num!

Just following the instructions given ??
hummmmmm,,,
any help (with puzzle) would be appreciated.


(



"Max" wrote in message
...
"Jim May" wrote
How do you get rid of the #NUM! that appears in Col A
and the last select in the Drop-down?

Think you might have missed this part of
Jason's instructions in the construct:
...
Now fill down as many rows as
your list contains elements.

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Jim May wrote
...
Just following the instructions given ??
hummmmmm,,, ..

Sorry, consider it as a "misplaced" friendly comment ..
Thought the #NUM! part you were referring to were those appearing if one had
copied the formula in col A beyond the # of elements in mylist (which would
return #NUM! in cells beyond), after* the setup was complete and working
with a "b" entered in B1
*not those #NUM! appearing in transition during set-up
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----