Data Validation drop down

[moonbreakker]

Hello,

I have a table with 2 columns.


Company Model
BMW 520
BMW 720
AUDI A8
MERC XYZ
AUDI XYZ
MERC Etc
BMW Etc
SUZUKI Etc
MERC Etc
SUZUKI Etc

A company can have multiple models. What i need is.. 2 Drop down boxes.

1. 2 drop down boxes. 1st to select company, second will only show models from that company.
2. Anyway to give auto complete option? for eg. i type H and it shows me Honda and Hyundai in drop down?
3. I should be able to add new models at bottom and it should auto update.

PS: I am ok with using VBA if there is no other option.

Thank you.

[tim201110]

sorry, i don't know is there any such solutions in english
but if you are any good in VBA

[JohnTopley]

See attached.

Look at "Name Manager" for "MAKE" to see how to define expanding data validation list.

[igormigor]

try with this
Attachment 428232

[tim201110]

JohnTopley, i wonder if you've read this: "give auto complete option"

[igormigor]

ok, one more chance to attach the correct file

Data Validation drop down 2.xlsb

[JohnTopley]

Yes .. I did: it was DESIRED not mandatory: if you are using a drop-down is it that necessary?

I looked at your file but as I don't speak Russian the explanation was of little use. I see the VBA but it failed as it presumably required the drop downs to be defined?).

[tim201110]

not quite, it works with forms and it gives auto complete option

[moonbreakker]

Well, Using data validation and indirect does the job, but doesn't let me add more models at bottom. Moreover do i need a "helper" column?

I only have to 2 columns and both columns have duplicate entries. (Same company repeating with same models/different models)
So i need in drop down only unique values. In one of the sheets above, i see Mercedes multiple times in drop down list.
Please see attached sheet. I need to be able to select company from drop down and i should get model automatically. Preferably auto complete in company selection. VBA will also do..

Thank you.

[moonbreakker]

Tim, Sorry, but i could not get your code working. I don't know Russian and could not understand. I have attached the sheet, if possible can you insert code in it and resend so i can test?

Thank you.

[JohnTopley]

You can only get a LIST of models for a given make: you will have to make selection yourself.

And you can add makes/ models by extending the named ranges as I described.

[moonbreakker]

Is it possible to clear the "model" cell when i select a company? As of now previous model shows by default even after changing the company. Something like make it calculate every time company is selected?

[moonbreakker]

Hello,

Suppose if models don't repeat and entire column B in my sheet is filled with unique models only. Does that get easier then? I will remove duplicate models. in attached sheet there are no duplicate models in column B. is it possible to sort using code by company and then just fetch the models? Only problem is that this sheet gets updated frequently and new models get added at the bottom of the sheet on daily basis. Old models get deleted too.

[JohnTopley]

See attached: in particular sheet "Parameters"

Column A contains MAKE and is a named range MAKE (data in first sheet sorted by MAKE/MODEL)

Columns C onwards (row 1) contain MAKE headings (Copy/Transpose from Column A)

Under each MAKE there is list of models extracted from the first sheet using the following formula ..

in C2

=IFERROR(INDEX('MODEL SEGMENT AND DISCOUNTS'!$B$2:$B$500,SMALL(IF('MODEL SEGMENT AND DISCOUNTS'!$A$2:$A$500=Parameters!C$1,ROW($A$2:$A$500)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl+Shift+Enter

Copy across and down

I then created named ranges (AUDI,BMW only) for each model list

Data Validation in first sheet uses List=>MAKE for MAKE and List==> =INDIRECT(D3) for MODEL

You could further sub-divide models into Diesel/Petrol (See example)

[moonbreakker]

Thank you for your time John. I got it working with your latest sheet. Although, creating named ranges for so many makes and updating it on daily basis is lot of manual work. I think there should be a way to fetch

unique values from column A (Make)
Corresponding model --> unique (but multiple) values based on make from column B

Doesn't sound that complicated but it took me an hour to understand your function above.

I am still on google and other forums and i will surely get back to you if i find a simpler solution.

Thank you so much for your time.

[JohnTopley]

I tried to automate the process as much as possible by creating the lists of models for each make using the formula I provided. If Models are added/deleted from the main sheet, the named ranges will update automatically although you will need to extend the NAMED range if models are added.

If a new MAKE (and hence models) is added (or existing MAKE and/or models deleted) then:

- Sort the MAKE/MODEL table

- Copy the MAKE list to "Patameters" and use the "Data"==> "Remove Duplicates" facility in Excel to get your unique list

- Copy and Paste Transpose to columns C1 onwards (leaving the formulae)

- Copy the formula from C2 across and down as required

The ultimate automation would be to do the above using VBA.

[JohnTopley]

See the attached which creates all using VBA.

Please Login or Register  to view this content.