Using Data Validation and VLOOKUP for Dependent Drop Down Lists Using Years

[jessisan]

After browsing the forums, I am unable to perform the task my boss is asking me to complete. Although I did get good information and I learned how to create dependent lists. Here is my problem, my lists involve numbers and I have yet to find a solution to titling a list with a number. Therefore I think I need to use VLOOKUP, but I am not familiar with it. I see how it is used in other examples, but I can not find an example quite like my own. Here is what I need to create:

I work at an automotive repair shop. We get a lot of questions about servicing and at what mileage is a service due. So I need to create a table that in the first column the customer can select the year of their vehicle. Then dependent on the year (see, this is my problem, I cannot create a list using a number so I am unable to create my second dependent list), the customer can choose their body type. Then dependent on their body type, their specific mileage intervals will appear in the third column. For example:

I have a 2006 (first column), E46 (second column dependent on first), therefore my service interval (third column dependent on second) is.......

Then in the third column the answer is multiple mileages. For example 15,000, 30,000, 45,000, 60,000. And I can't just say 15,000 mile interval because some of the intervals are varied like 16,000, 30,000, 32,000. So my third column will have to include the entire, long, interval. The end result should look as follows: All lists dependent on the one before it

Year	Body	Interval
1996	E46	16,000: 30,000: 32,000: 50,000: 55,000: 62,000

<tbody>
</tbody>

Any help would be greatly appreciated. Thank you!

 

[Akashwani]

Hi and welcome to MrExcel.

You have come to the right place to get the answer you require, but we will need a bit more information from you.

What are the sheet names which you are using?
What is the range of your data?
Does your data have "Headers?

I think that you can create a Data Validation Drop Down list based on the Years (numbers)
The main problem here will be "concatenating" (joining) all the results in one cell, if you can use more than one cell, then it should be fairly straight forward to give you a solution.

Check this out...

Excel Magic Trick # 259: Dynamic DV List Based On DV List - YouTube

Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula - YouTube

I hope this helps.

Ak

 

[jessisan]

Thanks so much for your examples! I watched the first video because I need to be able to resolve the number issue first. I did exactly as the video showed and I was able create list names with numbers. So the first part of my first problem was solved! But.....when I try to create the second column using indirect, I get an error message that says :The source currently evaluates to an error. Do you want to continue?"
I clicked yes and while my first column of years shows up, my second column does nothing. So I did not watch the second video you posted yet, because I cannot create a third column until my second column works. I also tried to turn my worksheet into HTML so that you could see my example, but I did not understand how. So tomorrow I will look on youtube to create HTML and I will show you what I have. I really do appreciate you helping me. It gives me hope that what I'm trying to do is possible. I am very inexperienced with excel, but I can follow directions well.

Also to answer your questions:
I am only using one sheet and I do not have it named bc I am simply testing it out right now.
My datas range is A:1 to AN:19
It does not have headers.

Thanks, Thanks, Thanks!

 

[Akashwani]

Hi,

Does this set up work for you?....

Sheet holding Data Validation Lists and Table of Service Intervals....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Year	1990	1991	1992	1993	*	Body	Service1	Service2	Service3	Service4	Service5	*
2	1990	90A	91A	92A	93A	*	90A	5000	10000	15000	*	*	*
3	1991	90B	91B	92B	93B	*	90B	6000	12000	18000	*	*	*
4	1992	90C	91C	92C	93C	*	90C	4000	8000	12000	*	*	*
5	1993	*	91D	92D	93D	*	91A	5000	10000	15000	20000	25000	*
6	1994	*	*	92E	93E	*	91B	6000	12000	18000	24000	*	*
7	1995	*	*	*	93F	*	91C	4000	8000	12000	16000	*	*
8	1996	*	*	*	*	*	91D	3000	6000	9000	12000	*	*
9	1997	*	*	*	*	*	92A	15000	25000	35000	45000	*	*
10	1998	*	*	*	*	*	92B	5000	10000	15000	*	*	*
11	1999	*	*	*	*	*	92C	6000	12000	18000	*	*	*
12	2000	*	*	*	*	*	92D	4000	8000	12000	*	*	*
13	*	*	*	*	*	*	92E	5000	10000	15000	20000	25000	*
14	*	*	*	*	*	*	93A	6000	12000	18000	24000	*	*
15	*	*	*	*	*	*	93B	4000	8000	12000	16000	*	*
16	*	*	*	*	*	*	93C	3000	6000	9000	12000	*	*
17	*	*	*	*	*	*	93D	15000	25000	35000	45000	*	*
18	*	*	*	*	*	*	93E	2000	4000	6000	8000	10000	*
19	*	*	*	*	*	*	93F	4000	8000	12000	*	*	*
20	*	*	*	*	*	*	*	*	*	*	*	*	*
DV Lists

Customer Sheet with Data Validation Drop Down List, Dependent List and Service Results...

Example1...

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Years	Body	*	Service1	Service2	Service3	Service4	Service5	*
2	1991	91A	*	5000	10000	15000	20000	25000	*
3	*	*	*	*	*	*	*	*	*
Customer

Example2....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Years	Body	*	Service1	Service2	Service3	Service4	Service5	*
2	1992	92C	*	6000	12000	18000	0	0	*
3	*	*	*	*	*	*	*	*	*
Customer

I wont go into an indepth explanation on how to achieve this until you confirm that this is the type of setup you require.
You may want to take a look here though on how to create a Data Validation List based on numbers...

Data Validation - dependent lists using name lists with numbers as - Microsoft Community

Sample File here...
<a href=http://www.filedropper.com/jessisan><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >file upload storage</a></div>

I hope this helps.

Ak

 

[jessisan]

Your spreedsheet would work great, I think. I am unsure what the astericks are for though.
Due to the weather we have been computer-less all day today so I apologize for the late response. I figured out how to make my speedsheet an HTML, it was rather simple, who knew?!

file:///Users/jessiroberson/Desktop/Work in progress.htm

So as you can see (hopefully my link worked) I have my lists and my first column, Years, works fine. Its when I try to connect the second list where I'm receiving an error. But that is the exact list I am working with for work. I wouldn't even know where to begin adding the third column of intervals.

 

[jessisan]

After i posted I tested my link, no surprise that it did not work. Here is another try. ***fingers crossed*** \\psf\Home\Desktop\Work in progress.mht
or maybe this http://\\psf\Home\Desktop\Work in progress.mht

 

[jessisan]

Well none of my links worked. So I will copy and paste my spreedsheet. So you can at least see that I have tried and where I'm trying to go with it.

Year		1996		1997		1998		1999		2000		2001		2002		2003		2004		2005		2006		2007		2008		2009		2010		2011		2012		2013		Year	Body
1996		E36		E36		E36		E36		E36		E36		E36		E39		E46		E46		E46		E60		E60		E60		E63		E70		E70		E70		2005
1997				E38		E38		E38		E38		E38		E39		E46
1998				E39		E39		E39		E39		E39		E46		E52
1999								E46		E46		E46		E52
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;" width="17"> <col style="width: 48pt;" width="64"> <col style="width: 11pt; mso-width-source: userset; mso-width-alt: 512;" width="14"> <col style="width: 48pt;" width="64"> <col style="width: 9pt; mso-width-source: userset; mso-width-alt: 438;" width="12"> <col style="width: 48pt;" width="64"> <col style="width: 9pt; mso-width-source: userset; mso-width-alt: 438;" width="12"> <col style="width: 48pt;" width="64"> <col style="width: 8pt; mso-width-source: userset; mso-width-alt: 365;" width="10"> <col style="width: 48pt;" width="64"> <col style="width: 7pt; mso-width-source: userset; mso-width-alt: 329;" width="9"> <col style="width: 48pt;" width="64"> <col style="width: 8pt; mso-width-source: userset; mso-width-alt: 365;" width="10"> <col style="width: 48pt;" width="64"> <col style="width: 7pt; mso-width-source: userset; mso-width-alt: 329;" width="9"> <col style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;" width="64"> <col style="width: 5pt; mso-width-source: userset; mso-width-alt: 256;" width="7"> <col style="width: 48pt;" width="64"> <col style="width: 8pt; mso-width-source: userset; mso-width-alt: 365;" width="10"> <col style="width: 48pt;" width="64"> <col style="width: 7pt; mso-width-source: userset; mso-width-alt: 329;" width="9"> <col style="width: 48pt;" width="64"> <col style="width: 7pt; mso-width-source: userset; mso-width-alt: 329;" width="9"> <col style="width: 48pt;" width="64"> <col style="width: 5pt; mso-width-source: userset; mso-width-alt: 219;" width="6"> <col style="width: 48pt;" width="64"> <col style="width: 7pt; mso-width-source: userset; mso-width-alt: 329;" width="9"> <col style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;" width="64"> <col style="width: 5pt; mso-width-source: userset; mso-width-alt: 256;" width="7"> <col style="width: 48pt;" width="64"> <col style="width: 6pt; mso-width-source: userset; mso-width-alt: 292;" width="8"> <col style="width: 48pt;" width="64"> <col style="width: 7pt; mso-width-source: userset; mso-width-alt: 329;" width="9"> <col style="width: 48pt;" width="64"> <col style="width: 7pt; mso-width-source: userset; mso-width-alt: 329;" width="9"> <col style="width: 48pt;" span="4" width="64"> <tbody>
</tbody>

 

[Akashwani]

Hi,

The * have NO relevance, they get added to the table when a post has been edited!!

Take a look here....

<a href=http://www.filedropper.com/jessisanv2><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >upload files free</a></div>

I hope that helps, but I'm still unsure as to how you have your "service"/"intervals" data stored.

Ak