Dynamic Dropdown using Data Validation list .

[jambar]

I have 3 columns of text data in Columns A , B and C . I have the text input in D1 ( which is from Column A) and text input in E1(which is from Column B ) . Now I want a dropdown list in F1 , which should be matching data for E1,F1. How to get this done ? Thanks for the help .

 

[jambar]

 

[etaf]

what version of excel do you have ?

and is that exactly how you want the output to look { and "

 

[jambar]

what version of excel do you have ?

and is that exactly how you want the output to look { and "

Excel2016
I want a data validation list drop-down to show me qwe and abc ....so that I can chose anyone in F1 cell..

Hope I made that clear?

 

[kevin9999]

With 365 it's relatively simple using the Filter() function. The alternatives for 2016 can get (IMO) pretty messy. Personally, I would opt for a UDF to create a double-filtered validation list on your sheet - which could then be referenced as a list for your validation list in cell F1. Consider the following suggestion: you put the UDF in cell H1, with the 2 ranges referenced in the function as A2:C9 and D1:E1. The function returns the only 2 possible options in cell H1 (spills down). Then in F1, set the validation to List / Source = $H$1# (note the hash).
The UDF looks like this (put in a standard module):

Function jambar(rng1 As Range, rng2 As Range) As Variant
    Application.Volatile
    Dim a, b, i As Long, j As Long
    Dim fltr1 As String, fltr2 As String
    a = rng1.Value
    ReDim b(1 To UBound(a, 1)) As Variant
    fltr1 = rng2(1, 1): fltr2 = rng2(1, 2)
    
    For i = 1 To UBound(a, 1)
        If a(i, 1) = fltr1 And a(i, 2) = fltr2 Then
            ReDim Preserve b(j)
            b(j) = a(i, 3): j = j + 1
        End If
    Next i
    jambar = Application.Transpose(b)
End Function

And the sheet looks like this:

Book1
	A	B	C	D	E	F	G	H
1	Data1	Data2	Data3	A	P	qwe		qwe
2	C	P	abc					abc
3	B	P	qwe
4	C	Q	pqr
5	A	R	abc
6	A	P	qwe
7	B	R	pqr
8	C	Q	pqr
9	A	P	abc
10
Sheet1

Cell Formulas
Range	Formula
H1:H2	H1	=jambar(A2:C9,D1:E1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
F1	List	=$H$1#

 

[jambar]

Thank you very much Kevin....will try this and respond back .

 

[Peter_SSs]

Excel2016

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jambar]

I have 3 columns of text data in Columns A , B and C . I have the text input in D1 ( which is from Column A) and text input in E1(which is from Column B ) . Now I want a dropdown list in F1 , which should be matching data for E1,F1. How to get this done ? Thanks for the help .

Can someone suggest a solution without using VBA? I mean using functions (sumproduct,offset,aggregate index etc)and array formula ?

 

[Peter_SSs]

Firstly, could you please update your details as I requested above? You are asking helpers to provide a formula solution and different Excel versions contain different functions so your version information should be readily-available to your helpers.

You could try a set-up like this.

23 12 14.xlsm
	A	B	C	D	E	F	G	H	I
1	Data1	Data2	Data3	A	P			qwe	$H$1:$H$2
2	C	P	abc					abc
3	B	P	qwe
4	C	Q	pqr
5	A	R	abc
6	A	P	qwe
7	B	R	pqr
8	C	Q	pqr
9	A	P	abc
DV

Cell Formulas
Range	Formula
I1	I1	=ADDRESS(ROW(H1),COLUMN(H1))&":"&ADDRESS(COUNTIF(H1:H9,"?*")+ROW(H1)-1,COLUMN(H1))
H1:H9	H1	=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$2:C$9)/((A$2:A$9=D$1)*(B$2:B$9=E$1)),ROWS(H$1:H1))),"")

Cells with Data Validation
Cell	Allow	Criteria
F1	List	=INDIRECT($I$1)

 

[jambar]

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done