Generating dynamic dropdown list in Excel

[sumitn]

Background information: I am using Excel 2003 on Windows XP.

I am trying to get a unique list to create a drop down list.

I have a sales report where Col A=Product Name, Col B=Month, Col C=Year, Col D=Total Sale Value.
I am using "Conditional Sum" to get the Total Sale amount from Col D for selected Product for specific month and year.

I have created a drop down list for Month & Year. What I am looking for a drop down box which shows all the Products meeting criteria (Month & Yr) which are unique and sorted meeting the above two criterias.
So, I want to create a dynamic drop down list showing only those Product names which has undergone sale in that month & year.

I have already used Advanced filter but that's not dynamic.

Any help would be appreciated.

Thanks

 

[jbeaucaire]

Here's a basic layout that will create the "list" for you in column K without using any cumbersome array formulas. The key column E could be hidden if you wish...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Product Name	Month	Year	Sales	Key		Month	Year	Max		Name3
2	Name1	2	2008	$ 100.00	0		4	2008	5		Name9
3	Name2	3	2009	$ 200.00	0						Name15
4	Name3	4	2008	$ 300.00	1						Name21
5	Name4	2	2009	$ 400.00	1						Name27
6	Name5	3	2008	$ 500.00	1
7	Name6	4	2009	$ 600.00	1
8	Name7	2	2008	$ 700.00	1
9	Name8	3	2009	$ 800.00	1
10	Name9	4	2008	$ 900.00	2
11	Name10	2	2009	$ 1,000.00	2
12	Name11	3	2008	$ 1,100.00	2
13	Name12	4	2009	$ 1,200.00	2
14	Name13	2	2008	$ 1,300.00	2
15	Name14	3	2009	$ 1,400.00	2
16	Name15	4	2008	$ 1,500.00	3
17	Name16	2	2009	$ 1,600.00	3
18	Name17	3	2008	$ 1,700.00	3
19	Name18	4	2009	$ 1,800.00	3
20	Name19	2	2008	$ 1,900.00	3
21	Name20	3	2009	$ 2,000.00	3
22	Name21	4	2008	$ 2,100.00	4
23	Name22	2	2009	$ 2,200.00	4
24	Name23	3	2008	$ 2,300.00	4
25	Name24	4	2009	$ 2,400.00	4
26	Name25	2	2008	$ 2,500.00	4
27	Name26	3	2009	$ 2,600.00	4
28	Name27	4	2008	$ 2,700.00	5
Sheet2

=========
Then to get a dynamic drop down list, create a named range called PRODUCTS using this: Refers To: =OFFSET(Sheet2!$K$1,0,0,MATCH("*",Sheet2!$K:$K,-1),1)

Use that name in a data validation drop down.

 

[sumitn]

It works as desired!!

Thanks a lot JB.

 

[sumitn]

Hi JB,

In addition to the Product, I need to put in a Sales Rep Name which is creating duplicate entries in the Dropdown list since Product Names are getting duplicated depending on the Sales Rep Names. Is there a way to remove the duplicate entries from Col K?

Thanks in advance.

 

[jbeaucaire]

Use Excel Jeanie to demonstrate your data and situation, like I did above.

 

[TritonCoding]

I have a VBA solution that allows you to dynamically change the filter you want to use.

Sub MakeDropDown()
Dim myList$
Dim c As Range
myList = ""

For Each c In ActiveSheet.UsedRange.Columns(1).Cells
If c.Value Like "*" & Cells(2, 3).Value & "*" Then
myList = myList & c.Value & ","
End If
Next c

myList = Mid(myList, 1, Len(myList) - 1)
With Range("E2").Validation
.Delete
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=myList
End With
End Sub