Lookup come list

[Jim]

Hi,
Wonder if anyone can help me here. I am looking to a cell
to look up a list that is identified by the first letter
in that cell or Contents
For example in A1 "Speed" is entered.


In B2 I would like to pick from a list that is contained
in the cells AA1 to AA100 (100 Variables) but only 10 is
related to "Speed".
So I only want these ten items listed.
Is there anyway that this can be done and picked from the
list?

I have tried using DATA, Validation, List & Formula but no
success

Here's Hoping
Jim

 

[Frank Kabel]

Hi
how do you know which entries in AA1:AA100 are related to 'Speed'. Do
they contain this word?

 

[Jim]

I have them listed being with the letter "S"
Kind Regards
jim

 

[Frank Kabel]

Hi
still not quite sure. Please post some examples of the data in this
column

 

[Guest]

IN AA1:AA100
some data entry will be
S Acc
S Break
S Vel
S Power
A car
C Vech

Hope this helps

 

[Frank Kabel]

Hi
try the following:
In AB1 enter the following array formula (committed with
CTRL+SHIFT+ENTER):
=IF(ISERROR(INDEX($AA$1:$AA$100,SMALL(IF(LEFT($A$1,1)=LEFT($AA$1:$AA$10
0,1),ROW($AA$1:$AA$100)),ROW(1:1)))),"",MID(INDEX($AA$1:$AA$100,SMALL(I
F(LEFT($A$1,1)=LEFT($AA$1:$AA$100,1),ROW($AA$1:$AA$100)),ROW(1:1))),3,2
55))

and copy this down for as many rows as required. Now use this helper
column AB for your data validation listbox as source

 

[Guest]

Hi Frank,

When i put the formula in there is an error message that
comes up. formula contains an error.
Cna'tget any further than this.
sorry,
jim

 

[Frank Kabel]

Hi
formula works for me. You may have to remove the linebreaks inserted by
your newsreader. You may also post the formula you have inserted in
this cell.

If this does not help you may use the semicolon instead of a coma as
delimiter. in this case replace all comas with a semicolon

 

[Guest]

Works a treat thank you
Jim

-----Original Message-----
Hi
formula works for me. You may have to remove the linebreaks inserted by
your newsreader. You may also post the formula you have inserted in
this cell.

If this does not help you may use the semicolon instead of a coma as
delimiter. in this case replace all comas with a semicolon


--
Regards
Frank Kabel
Frankfurt, Germany


the data in
this

.