Data Validation drop down

**moonbreakker** · 11-02-2015, 06:31 AM

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** · 11-02-2015, 07:11 AM

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

**JohnTopley** · 11-02-2015, 07:35 AM

See attached.

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

**igormigor** · 11-02-2015, 07:42 AM

try with this
Attachment 428232

**igormigor** · 11-02-2015, 07:53 AM

ok, one more chance to attach the correct file

Data Validation drop down 2.xlsb

**tim201110** · 11-02-2015, 07:51 AM

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

**JohnTopley** · 11-02-2015, 08:00 AM

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** · 11-02-2015, 08:09 AM

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

**moonbreakker** · 11-02-2015, 10:37 AM

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** · 11-02-2015, 10:38 AM

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** · 11-02-2015, 10:45 AM

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** · 11-02-2015, 10:54 AM

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** · 11-02-2015, 11:09 AM

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** · 11-02-2015, 06:00 PM

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** · 11-02-2015, 06:28 PM

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** · 11-03-2015, 03:53 AM

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** · 11-03-2015, 08:07 AM

See the attached which creates all using VBA.

Sub Create_Parameters()
'
'
'

    Dim ws1 As Worksheet
    Dim Ws2 As Worksheet
     
    Dim r As Long
    Dim rs As Long
    Dim c As Long
    Dim col As Integer
    
    Dim Make As String
    Dim RefersTo As String
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
    
    
    Set ws1 = Worksheets("MODEL SEGMENT AND DISCOUNTS")
    Set Ws2 = Worksheets("Parameters")
    
    Ws2.Range("A1:AZ1000").ClearContents
    
    ws1.Activate
    '
    '  Sort by MAKE and MODEL
    '
    ws1.Range("A2:B1000").Select
    
    ActiveWorkbook.Worksheets("MODEL SEGMENT AND DISCOUNTS").Sort.SortFields.Clear
    
    ActiveWorkbook.Worksheets("MODEL SEGMENT AND DISCOUNTS").Sort.SortFields.Add _
        Key:=Range("A2:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        
    ActiveWorkbook.Worksheets("MODEL SEGMENT AND DISCOUNTS").Sort.SortFields.Add _
        Key:=Range("B2:B1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("MODEL SEGMENT AND DISCOUNTS").Sort
        .SetRange Range("A1:1000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    '
    '  Copy MAKE to "Parameters" worksheet
    '
    ws1.Range("A1:A1000").Copy
    Ws2.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    '
    '  Remove duplicate MAKE from "Parameters" worksheet
    '
    
    Ws2.Activate
    
    ActiveSheet.Range("$A$1:$A$1000").RemoveDuplicates Columns:=1, Header:=xlYes
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("A2:A" & LastRow).Copy
    
    Range("C1").PasteSpecial xlPasteValues, Transpose:=True
'
' Create Named Lists of MODELS for Each MAKE
'
        c = 3
        r = 2
        
        ws1.Activate
        
        Do While ws1.Cells(r, 1) <> ""
            Make = ws1.Cells(r, 1)
            rs = r
                Do While ws1.Cells(r, 1) = Make
                r = r + 1
                Loop
                ws1.Range("B" & rs & ":B" & r - 1).Copy Destination:=Ws2.Cells(2, c)
                c = c + 1
        Loop
'
'  Create the NAMED ranges .......
'
    
    Ws2.Activate

    With Ws2

        col = 3
    
        Do While .Cells(1, col) <> ""
    
            Make = Cells(1, col)
            Make = Application.WorksheetFunction.Substitute(Make, " ", "_")
            '
            '  Delete existing
            '
            ActiveWorkbook.Names(Make).Delete
    
            LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    
            RefersTo = "=Parameters!R2C" & col & ":R" & LastRow & "C" & col
    
            .Cells(1, col).Select
            ActiveWorkbook.Names.Add Name:=Make, RefersToR1C1:= _
                RefersTo
    
        col = col + 1
    
        Loop
        
       End With
'
    Application.ScreenUpdating = True
    
End Sub

Data Validation drop down

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Re: Data Validation drop down

Thread Information

Users Browsing this Thread

Similar Threads

data validation drop down menu only allowed if previous drop down cell is completed

Drop down in data validation

drop down w/ data validation

Data validation, drop-down box

Data Validation like Drop-Down Box

Data validation in drop down box

Data Validation Drop-Down Will Not Appear

Bookmarks

Bookmarks

Posting Permissions