LookUp Validation

[hrayani]

Hello To All

i am trying to prepare a file for rent a car purpose..

i have the date in Sheet1

A B
<TABLE style="WIDTH: 116pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=154 x:str><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" span=2 width=77><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=77>Toyota</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=77>Corolla GLI</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Toyota</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Corolla XLI</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Toyota</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Corolla Altis</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Toyota</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Vitz</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Suzuki</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Mehran</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Suzuki</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Alto</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Honda</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">City</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Honda</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Civic</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Daihatsu</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Coure</TD></TR></TBODY></TABLE>

Now what i want on sheet2 is when i select toyota through data validaton on sheet2 Cell A1 then sheet2 Cell B2 should show a list of cars attached with toyota on sheet1 -- like the validation list should show Corolla GLI, Corolla XLI & Corolla Altis

Awaiting Reply

Humayun

 

[Andrew Poulsom]

Look here:

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

 

[Yahya]

Hi
try this
this your orignal data

Excel Workbook
	A	B
1	Cars	Type
2	Toyota	Corolla GLI
3	Toyota	Corolla XLI
4	Toyota	Corolla Altis
5	Toyota	Vitz
6	Suzuki	Mehran
7	Suzuki	Alto
8	Honda	City
9	Honda	Civic
10	Daihatsu	Coure
Sheet1

#VALUE!

Excel 2007
select range from B2:B5
in Data Validation --> List
write this formula
=OFFSET(Cars,MATCH(A2,Cars,0),1,COUNTIF(Cars,A2))
=============
HTH

 

[hrayani]

Dear Yahya

Thanks for your reply ... But its not working brother

do i have to also NAME the type column

Or is there anything else

Awaiting reply

Humayun

 

[hrayani]

Dear Yahya

The message i am getting after copying the formula in the data validation list is THE SOURCE CURRENTLY EVALUATES TO AN ERROR. DO YOU WISH TO CONTINUE?

let me know what to do then

Humayun

 

[Yahya]

Hi
first : My Formula need small correction
=OFFSET(Cars,MATCH(A2,Cars,0)-1,1,COUNTIF(Cars,A2))
u shoud Subtract 1 form match Formula
u don't have to name Type
it is working with me
download it
http://rapidshare.com/files/434437483/Data_validation_.xlsx

 

[hrayani]

Dear Yahya

Thanks brother --- its working great

when i change the car in column A lets say from toyota to suzuki then the type column does not changes. - i mean it does change when the cell is selected in the drop down list - but i guess it should change automatically - i mean it should pick the first type availabale in the drop down column for the suzuki car -- INS"T IT??

secondaly, i would like to know that is it possible to show the cars only once in the drop down list... in this case toyota is mentioned 4 times in the drop down list. i guess its write coz the data is also having 4 toyota cars within the list... But is it possible i mean any formula to hide duplicates in the list ----- IF ANY

Regards,

Humayun

 

[Yahya]

Hi
my way working with your data when you sort the cars
that mean your col A should be sorted to work fine with you.
======
and to get Unique value from cars you have to use AdvancedFilter
or by another way to do it with formula like this one :

Excel Workbook
	A	B	C	D	E
1	Cars	Type
2	Toyota	Corolla GLI			Toyota
3	Toyota	Corolla XLI			Suzuki
4	Toyota	Corolla Altis			Honda
5	Toyota	Vitz			Daihatsu
6	Suzuki	Mehran
7	Suzuki	Alto
8	Honda	City
9	Honda	Civic
10	Daihatsu	Coure
Sheet1

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

Excel Workbook
Name	Refers To
Cars	=Sheet1!$A$2:$A$10
Workbook Defined Names