Dynamic dropdown list

Dear Sir,

Currently I am facing with dropdownlist problem.I have posted at http://www.excelforum.com/excel-form...alidation.html but could not be able get the desired solution.I think I might get solution at VBA forum hopefully.

As enclosed in attachment,I want result in F3 and G3.Currently I have shown single data in E3.There will be data in E4,E5,E6...... and so on.The data to be displayed in F3 and G3 should be in dropdown list which I tried using data validation but could not able to do so and omitted lateron.The lookup data are in A,B and C column.

Rest of the things I have explained in comment in F3 and G3.

Hope,I could able to achieve the result.

With Best Rgds,

Suresh

Try this:-

Originally Posted by MickG

Try this:-

Dear Sir,

Thanx for the reply to my post.It works fine F3 and G3 when the cell contains data in E3.But when I put data in E4, the dropdownlist does not display correct list in F4 and G4.Hence be noted that data will be filled by the user in E3,E4,E5,E6,E7,E8 & so on.So,the correct list must be displayed in the corresponding F and G columns rows.

It should be further to be notified to you that here Matching conditions is currently uptill at A18,B18 and C18 .It might be more than this in my real spreadsheet say more than 2000 as day to day data will be filled.

Hence I think,other cells are missing something which I think you can correct it.

Hope for the best.

With Rgds,
Suresh

Try the attached.
No restriction of 256 characters even if the list grows bigger and bigger.

See if this is how you wanted.

try this then

If you want to understand how the system works, do a step debug.

Delete your email address immediately and I will not give you my email anyway.
You can ask question on the board only.

Originally Posted by jindon

Delete your email address immediately and I will not give you my email anyway.
You can ask question on the board only.

I have deleted it.A stepwise instructions to implement will be highly appreciated.

With Best Rgds,
Suresh

Make sure you copy the code everything in;

1) Standard module, Module1
2) ThisWorkbook module, Workbook_Open and Workbook_SheetChange
3) Worksheet module in "detail" sheet, Worksheet_Change and GenerateNames

If you miss any one of the above, the system won't work.

Do I have to create any sheet also or not.Becoz I found one hidden sheet named as hiddennames.

I will marked as solved as soon as I fully get into my original workbook done.

With Best Rgds,
Suresh

Originally Posted by jindon

Make sure you copy the code everything in;

1) Standard module, Module1
2) ThisWorkbook module, Workbook_Open and Workbook_SheetChange
3) Worksheet module in "detail" sheet, Worksheet_Change and GenerateNames

If you miss any one of the above, the system won't work.

Dear Sir,

I have duly copied all the code but there has been error of Sheet1 which was of 'Details' but still there it exists.I have now enclosed my original workbook where I am actually working.I have other worksheets also which I require is currently I have made hiddenas it does not require.Also in 4 worksheets I have grouped the data as already I have filled up the data.Other unncessary column data I have deleted which is not required.All other formats are as of earlier.

Hope you can correct the error in this workbook and help me to resolve it.

With Best Rgds,
Suresh

When you ask question and if you are not familiar with vba, you need to show us.

1) Correct data layouts. (no problem with dummy data as long as data type are the same)
2) Correct sheet names, Case sensitive.

Otherwise, you will be asking to fix errors endlessly.

Originally Posted by jindon

When you ask question and if you are not familiar with vba, you need to show us.

1) Correct data layouts. (no problem with dummy data as long as data type are the same)
2) Correct sheet names, Case sensitive.

Otherwise, you will be asking to fix errors endlessly.

Dear Sir,

I am extremely sorry,if I hurt you.I could not be able to figure out the code where I underlooked.I think now I would able to save time with your consistence help.

Thanx once again for deliverying your precious time to my queries.

With Best Rgds,
Suresh

Dear Sir,

I have been using Post #17 till now is much worthy to me and working with fine as on date.

I require little bit changes in the code that in dropdown in next four consecutive sheet I want to be used PROPER Excel command to be shown.As in the 'Detail'Sheet all the data are in Capital letters.I mean to say that in dropdown list it must not show all the letters in capital letter.Instead a 'PROPER' Excel command is needed to be built in vba.

Kindly help me in this regard for modifying the code.

With Best Rgds,
Suresh

Replace Sub GenerateNames() in "Detail" sheet module with

Sub GenerateNames()
    Dim a, i As Long, e, s, t, dic As Object, temp As String
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
        On Error Resume Next
        Sheets("hiddennames").Visible = -1
        Sheets("hiddennames").Delete
        On Error GoTo 0
        Set dic = CreateObject("Scripting.Dictionary")
        dic.CompareMode = 1
        a = Range("a3").CurrentRegion.Value
        For i = 3 To UBound(a, 1)
            a(i, 1) = CStr(a(i, 1))
            If Not dic.exists(a(i, 1)) Then
                Set dic(a(i, 1)) = CreateObject("Scripting.Dictionary")
                dic(a(i, 1)).CompareMode = 1
            End If
            a(i, 3) = StrConv(a(i, 3), 3)
            If Not dic(a(i, 1)).exists(a(i, 3)) Then
                Set dic(a(i, 1))(a(i, 3)) = CreateObject("System.Collections.ArrayList")
            End If
            a(i, 4) = StrConv(a(i, 4), 3)
            If Not dic(a(i, 1))(a(i, 3)).Contains(a(i, 4)) Then
                dic(a(i, 1))(a(i, 3)).Add a(i, 4)
            End If
        Next
        With Sheets.Add
            .Name = "hiddennames"
            .Visible = 2
            t = t + 1
            With .Cells(1, t).Resize(dic.Count)
                .Value = Application.Transpose(dic.keys)
                .Name = "MyList"
            End With
            For Each e In dic
                On Error Resume Next
                ThisWorkbook.Names("z_" & e).Delete
                Err.Clear
                t = t + 1
                With .Cells(1, t).Resize(dic(e).Count)
                    .Value = Application.Transpose(dic(e).keys)
                    .Name = "z_" & e
                End With
                For Each s In dic(e).keys
                    t = t + 1
                    On Error Resume Next
                    temp = GetCleanName(s)
                    ThisWorkbook.Names("z_" & e & "_" & temp).Delete
                     Err.Clear
                    With .Cells(1, t).Resize(dic(e)(s).Count)
                        .Value = Application.Transpose(dic(e)(s).ToArray)
                        .Name = "z_" & e & "_" & temp
                    End With
                Next
            Next
        End With
        With Range("a2", Range("a" & Rows.Count).End(xlUp)).Offset(, 4)
            With .Validation
                .Delete
                .Add xlValidateList, Formula1:="=mylist"
            End With
        End With
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

That really was wonderful.Thanx a lot once again to you.

Dear Sir,

As on date 27.5.2015,My list is growing more than 4000 and there is hanging problem.The file opens and it takes more than 1/2 hours and sometimes it won't open.
There is no problem in the code u supplied but since the data grows hanging problems in opening excel file gets continue.

How can I resolve this issue ?

If you have any idea to deal with such large data,kindly let me know.