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

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

 

[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)