LookUp Validation

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,522
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Hi
try this
this your orignal data
Excel Workbook
AB
1CarsType
2ToyotaCorolla GLI
3ToyotaCorolla XLI
4ToyotaCorolla Altis
5ToyotaVitz
6SuzukiMehran
7SuzukiAlto
8HondaCity
9HondaCivic
10DaihatsuCoure
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
ABCDE
1CarsType
2ToyotaCorolla GLIToyota
3ToyotaCorolla XLISuzuki
4ToyotaCorolla AltisHonda
5ToyotaVitzDaihatsu
6SuzukiMehran
7SuzukiAlto
8HondaCity
9HondaCivic
10DaihatsuCoure
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
NameRefers To
Cars=Sheet1!$A$2:$A$10
Workbook Defined Names
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,597
Messages
6,179,808
Members
452,944
Latest member
2558216095

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top