auto select

[atuljadhavne]

Dear expert

I have excel sheet having details of dealers and code
now what i want
if i type "a" in E1 Cell than excel give me list of dealer whose name starting from a, if i press "h" than excel give me list of dealer whose name starting from "h"
and select the dealer full name from list

e.g
i press "a" in E1 cell and the result is

Atul Jadhav
Amitabh
Ashish
Abcd

etc

i have list of dealer in another sheet
using excel 2007

[Bob Phillips]

Put this array formula in E2 and copy down as far as you might need

=IFERROR(INDEX(A:A,SMALL(IF(LEFT($A$1:$A$200,1)=$E$1,ROW($A$1:$A$200)),ROW( A1))),"")

[atuljadhavne]

not working nothing to be happen

giving u more details

i working in sheet "Display" and want list in this sheet in E1 cell
the backup details of details having in sheet "Dealer details"

and as per your formula the range is from same sheet

i also try by A:A column but not working

[Bob Phillips]

I bet you didn't array-enter it, Ctrl-Shift-Enter.

[atuljadhavne]

I did with Cnt+sht+enter but it show only first name and nothing in E3 cell
but i don't want such this type
explain again, i want popup menu below E1 Cell but putting single alphabet of name, and it show only that name which is start from the alphablet inserted in E1 cell

e.g if i type "atul" in E1 and the popup menu open with contain atul in full name
like (i am still in E1 Cell, i still not press enter)

Atul Jadhav
Atul Pradhan
Atul Sinha
Atul bhark
Atul biswas

than i will select Atul Sinha from below list

and the list of dealer in another sheet not in same sheet

i hope u got it

[Bob Phillips]

You could use

=SUMPRODUCT((Rawsheet!$J$5:$J$1000=TRIM($B$1))*(Rawsheet!$E$5:$E$1000=TRIM( $C$2))*(Rawsheet!$F$5:$F$1000=B4)*(Rawsheet!$H$5:$H$1000<>0))

and

=SUMPRODUCT((Rawsheet!$J$5:$J$1000=TRIM($B$1))*(Rawsheet!$E$5:$E$1000=TRIM( $C$2))*(Rawsheet!$F$5:$F$1000=B4)*(Rawsheet!$H$5:$H$1000<>0))

but it gets very different numbers than yours