vlookup Tractors

[bianchipassione]

I want to create page using a filters data as it proceeds. the end result is a means for our salesmen to find the purchase price of different models of tractors.

Ideally, the person will select the brand of tractor, then using the next box, select the particular make. (up to 30). After selecting the make, they would then select from up to 5 models. Once done, the purchase information would be displayed.

I have a page containing all the information from which the vlookup will use.

Right now, I am getting #NA as a result.

My vlookup uses the entered brand name the user would type in. the formula is: =VLOOKUP(B1,CombPurchModel!A2:X$112,2,0)

 

[Fluff]

Have a look at dependant dropdowns http://www.contextures.com/xlDataVal02.html

 

[bianchipassione]

Have a look at dependant dropdowns http://www.contextures.com/xlDataVal02.html

I am working through this, but one thing I am having issues with is the fact that my data sheet has 113 lines. Each line has a brand, model and description.

When I click on the brand cell to select "John Deere; Harlo; Kubota" etc, I see a list showing each line. I was expecting to just see the three brands not John Deere repeated more than once. I obviously missed something in creating the Named list process, but cannot find it.

 

[Fluff]

Your named ranges should have each value once only

 

[bianchipassione]

What I am actually looking for is a way to make the brand show the "unique" values that are in my data sheet. So,despite there being 75 rows starting with John Deere as the brand, I want my selection box to shwo John Deere 1 time.

 

[bianchipassione]

Your named ranges should have each value once only

Not sure how. SO I will try to include a sample

ID	Brand	Model	TracModelDesc	TracWheetType	TracTransType	TracModelFrame	TracModelWT	TracModType	TracDHrRate	TracTRent	TracStationType
2	John Deere	6230	6230 2WD Open R&P	2WD		S	W	R			Open
3	John Deere	6230	6230 Mudder Open Flange	AWD		S					Open
4	John Deere	6320	6320 Mudder Open	AWD		S					Open
8	John Deere	5095	5095M 4WD Open	AWD							Open
9	John Deere	5095	5095M AWD Cab 230/95 R48	AWD							Standard
10	John Deere	5095	5095MH Mudder Open	AWD							Open
11	John Deere	5100	5100M AWD Open	AWD							Open
12	John Deere	5100	5100MH Mudder Open	AWD							Open
13	John Deere	5100	5100MH Mudder Cab	AWD							Standard
14	John Deere	5100	5100EN AWD Narrow Open	AWD							Premium
23	John Deere	6420	6420 2WD Open	2WD		S					Premium
24	John Deere	6420	6420 Mudder Open	AWD		S					Standard
25	John Deere	6420	6420 AWD Open	AWD		S					Open
26	John Deere	6420	6420L AWD Open Low Profile	AWD		S					Open
27	John Deere	6420	6420L AWD Cab Low Profile	AWD		S					Standard
28	John Deere	6430	6430 Mudder Open R&P	AWD		S					Open
29	John Deere	6430	6430 AWD Row Crop Open R&P	AWD		S					Open
30	John Deere	6125	6125M AWD Open Flange Row Crop	AWD		S					Open
31	John Deere	6430	6430 AWD Open Flange Row Crop	AWD		S					Open
32	John Deere	6430	6430 AWD Cab Row Crop	AWD		S					Standard
33	John Deere	6430	6430 AWD P Cab Row Crop Premium PQ	AWD	Power shift	S					Premium
34	John Deere	6125	6125R AWD Row Crop Cab Premium AQ+	AWD	AQ+	S					Premium
35	John Deere	6430	6430 AWD Cab Row Crop Premium IVT	AWD	IVT	S					Premium
84	Cat	MT765	MT765 C Cab Power Shift Narrow	2WD	Power shift		T	B			Standard
85	Cat	MT765	MT765 C Cab Power Shift Wide	2WD	Power shift		T	B	56		Standard
86	Cat	MT765	MT765 D Cab Power Shift Wide	2WD	Power shift		T	B			Standard
91	Kubota	M9000	M9000 Mudder Open	AWD							Open
92	Kubota	M96	M96 Mudder Open	AWD		S	W				Open
97	Kubota	L3200 DT	L3200 DT 2WD Open Station - Syncro	2WD		S	W		0	0	Open
99	Kubota	L3800 DT	L3200 DT 2WD Open Station - Syncro	2WD		S	W		0	0	Open
100	Massey-Ferguson	2606	2606 2WD Open Station - Syncro	2WD		S	W		0	0	Open
101	Kubota	MX4800	MX4800 2WD Open Station - Syncro	2WD					0	0	Open
102	Kubota	L4600	L4600 4WD Open Station - IVT	4WD		S	W		0	0	Open
103	Kubota	MX5200 DT	MX5200 DT 4WD Open Station - Syncro	4WD		S	W		0	0	Open
104	Massey-Ferguson	4707	4707 4WD Open Station - PR	4WD		S	W		0	0	Open

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>

 

[Fluff]

You need to set-up your named ranges as shown in the link I provided.
That is a list of Makes, a list of models per make etc.

 

[Aladin Akyurek]

What is the output you require from the data in post #6 and which conditions must hold for that output?

 

[bianchipassione]

What is the output you require from the data in post #6 and which conditions must hold for that output?

Essentially, once the user "drills" down and selects the correct tractor, he will see the purchase amount, rental rates as well as help determine the selling price. (my list above does not display at least 8 columns of data)

 

[bianchipassione]

So...

The user will select a Brand from a drop down list. then from another box, he will select from a list of Makes. Then in the third, he will select the desired model which will then display the associated data on the SS.

In the brand drop down, I wanted to show only the Unique brand values. But right now, I see a brand line for each of the makes and model that exist in that brand (for John Deere, I am seeing at least 20 John Deere" (hopefully this make sense)