Can't get dynamic dropdown list to work...

[DarkoDeign2]

Hi,

I have two worksheets. On the first sheet I have a dropdown list in cell C15. You can select either "Ongoing", "Sales", "Sold Out" or "Closed".
If I select "Ongoing" in C15, I want to have the data from E7:E100 to be shown in the dropdown list in cell C16.
If I select "Sales" in C15 I want to have the data from F7:F100 to be shown in the dropdown list in cell C16, and so on.

I have a formula in E7, F7, G7, H7. I suspect that this is what is causing the issue, but I am not sure.
I will also put my data sheet from where the data is pulled, just for your reference.

Book1
	B	C	D	E	F	G	H
2				Local
3
4	Company name:	Celestial Imports		Ongoing	Sales	Sold out	Closed
5
6
7				7654321		4321098
8				5678901
9
10
11
12
13
14
15		Ongoing
16
Adress & Overview

Cell Formulas
Range	Formula
E2	E2	=IFERROR(INDEX(Table1[Global Local],MATCH('Adress & Overview'!$C$4,Table1[Supplier],0)),"")
E7:E8,F7:H7	E7	=IF($C$4="","",FILTER(Table1[Numbers],(Table1[Global Local]=$E$2)*(Table1[Supplier]=$C$4)*(Table1[Status]=E4),""))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Table7	=Table1	E7:H7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:J2	Cell Value	contains "Local"	text	NO
E2:J2	Cell Value	contains "Global"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C15	List	Ongoing; Sales; Sold out; Closed
C16	List	=INDIRECT(ADDRESS(C15;4;1))
C4	List	Celestial Imports; Nexus Supplies; Aurora Trading Co.; Starlight Enterprises; Spectrum Suppliers; Empyrean Distributors; Quantum Merchandisers

This is my data sheet with my table of data:

Book1
	C			F																V											AG
2	8456234			Global																Starlight Enterprises											Ongoing
3	9876543			Global																Starlight Enterprises											Ongoing
4	1234567			Local																Spectrum Suppliers											Sales
5	8765432			Local																Aurora Trading Co.											Sold Out
6	2345678			Global																Empyrean Distributors											Closed
7	7654321			Local																Celestial Imports											Ongoing
8	3456789			Local																Spectrum Suppliers											Ongoing
9	6543210			Global																Empyrean Distributors											Closed
10	4567890			Local																Nexus Supplies											Ongoing
11	5432109			Global																Starlight Enterprises											Sold Out
12	9876543			Local																Aurora Trading Co.											Closed
13	9871234			Global																Starlight Enterprises											Ongoing
14	8765432			Local																Nexus Supplies											Closed
15	3456789			Local																Aurora Trading Co.											Closed
16	5678901			Local																Celestial Imports											Ongoing
17	4321098			Local																Celestial Imports											Sold Out
18	2109876			Global																Quantum Merchandisers											Ongoing
19	6543210			Local																Nexus Supplies											Sales
Tracking

 

[Bosquedeguate]

Hi ... are you willing to create another sheet with some list information on/in it?
I do not think your formulas are an issue.
Try the Data Validation Formulas and let me know if they work for you.

On my "Lists" sheet I have the following.

DynamicDataValidationLists.xlsm
	A	B	C	D
14	Ongoing	Sales	Sold out	Closed
15	E7:E1000	F7:F1000	G7:G1000	H7:H1000
Lists

then for Data Validation formulas I have these for both C15 and C16. It seem to work with a Quick Test.

DynamicDataValidationLists.xlsm
	B	C	D
15		Sold out
16		345678
17
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
C15	List	=$E$4:$H$4
C16	List	=INDIRECT(INDEX(Lists!$A$15:$D$15,MATCH(C15,Lists!$A$14:$D$14,0)))

 

[DarkoDeign2]

Hi,

I would like to do this if possible without having to create a new sheet.
However when I tried your example I get an error. When I do the data validation for cell c16 and try to close the popup window, I get, "The source currently evaluaes to an error, do you want to continue?".

Correction: when I removed the $signs, I don't get an error message, but instead the drop down list in cell C16 shows the formula for the data validation.

 

[Peter_SSs]

I would like to do this if possible without having to create a new sheet.

No need for a new sheet & no need for the volatile function INDIRECT. Use the Data Validation for C16 as shown below.

Also,

• it is a bad idea to use the sheet name of the sheet that the formula is on. It is not needed & can in fact lead to incorrect results in some circumstances. See my changed formula in E2
• in the future, when posting a formal table, please include the header row

DarkoDeign2.xlsm
	B	C	D	E	F	G	H
1
2				Local
3
4	Company name:	Celestial Imports		Ongoing	Sales	Sold out	Closed
5
6
7				7654321		4321098
8				5678901
9
10
11
12
13
14
15		Sold out
16
Address & Overview

Cell Formulas
Range	Formula
E2	E2	=IFERROR(INDEX(Table1[Global Local],MATCH($C$4,Table1[Supplier],0)),"")
E7:E8,F7:H7	E7	=IF($C$4="","",FILTER(Table1[Numbers],(Table1[Global Local]=$E$2)*(Table1[Supplier]=$C$4)*(Table1[Status]=E4),""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C15	List	=$E$4:$H$4
C16	List	=INDEX($E$7:$H$7,MATCH(C15,$E$4:$H$4,0))#

Examples

 

[DarkoDeign2]

This works perfect, thanks! I also removed the name of the worksheet in the formulas.
I came to think of it while playing around.

If I select "Celstial imports" in cell C4.
Is it possible to show "Ongoing" and "Sold out" in C15 drop down list? In other words, just to show the ones that has values below.
So in your example above Sales and Closed doesn't have any values so these are not shown in the drop down list?

I just came to think of this, if it is even possible...

 

[Peter_SSs]

This works perfect, thanks!

You're welcome.

Is it possible to show "Ongoing" and "Sold out" in C15 drop down list? In other words, just to show the ones that has values below.

Add a new formula (I have used cell J4)
And change the C15 DV as follows.

DarkoDeign2.xlsm
	B	C	D	E	F	G	H	I	J	K	L
2				Local
3
4	Company name:	Celestial Imports		Ongoing	Sales	Sold out	Closed		Ongoing	Sold out
5
6
7				7654321		4321098
8				5678901
9
14
15		Ongoing
16
Address & Overview

Cell Formulas
Range	Formula
E2	E2	=IFERROR(INDEX(Table1[Global Local],MATCH($C$4,Table1[Supplier],0)),"")
J4:K4	J4	=FILTER(E4:H4,E7:H7<>"")
E7:E8,F7:H7	E7	=IF($C$4="","",FILTER(Table1[Numbers],(Table1[Global Local]=$E$2)*(Table1[Supplier]=$C$4)*(Table1[Status]=E4),""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C15	List	=$J$4#
C16	List	=INDEX($E$7:$H$7,MATCH(C15,$E$4:$H$4,0))#

Example:

 

[DarkoDeign2]

Works like a charm! Thanks Peter!

 

[Peter_SSs]

You are welcome. Glad it worked for you. Thanks for the follow-up.