Exclude certain values from dropdown list

[Polanskiman]

Hello,

I am working a rather complex worksheet. I'll try to make my question simple. Please let me know if you need further details.

I have a table with multiple rows/columns containing data. I need to create several dropdown lists from that table and from the same range but with each dropdown list excluding certain values according to certain criteria.

I cannot use VBA for this project.

Is that possible?

 

[Polanskiman]

Small note. I am aware I could replicate the table as many times as necessary by excluding the values I don't want in each dropdown list but I want to avoid that.

 

[Polanskiman]

Anyone has any idea?

 

[Polanskiman]

Having no answer I posted my question here as well:

Exclude certain values from dropdown list [SOLVED]

Hello, I have a table with multiple rows/columns containing data. I need to create several dropdown lists from that table and from the same cloumn/range but with each dropdown list excluding certain values according to certain criteria. I cannot use VBA for this project. Is this...

www.excelforum.com

 

[Peter_SSs]

Since you have Excel 365, try this. I have just done one but you should be able to replicate for the others. Helper column (P) can be hidden.
Formula needs to be entered into cell P4 only. The other results will automatically 'spill' to the other rows.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers.

mockup.xlsx
	B	C												O	P
1
2
3	Products	Type													fert + rem
4	ADA Brighy 'N'	fert													ADA Brighy 'N'
5	ADA Dosing	fert													ADA Dosing
6	ADA E.I. Mix	fert													ADA E.I. Mix
7	APFUK Trace	tr													Clives E.I. Target
8	Clives E.I. Target	fert													EI Dosing Full
9	CSM+B Trace	tr													EI Dosing Low
10	EI Dosing Full	fert													EI Dosing Mid
11	EI Dosing Low	fert													EI Modified
12	EI Dosing Mid	fert													PMDD
13	EI Modified	fert													PMDD+PO4
14	GLA EDTA Micromix	tr													Proflora Ferropol
15	PMDD	fert													Seachem Flourish
16	PMDD+PO4	fert													TNC Complete
17	Proflora Ferropol	fert													TPN+ Fertiliser
18	Rexolin APN	tr													Tropica Premium Nutrition
19	Seachem Flourish	fert													Tropica Specialised Nutrition
20	Solufeed TEC	tr													Seachem Equilibrium (remineraliser)
21	TNC Complete	fert													TNC GH Boost (remineraliser)
22	TPN+ Fertiliser	fert
23	Tropica Premium Nutrition	fert
24	Tropica Specialised Nutrition	fert
25	Seachem Equilibrium (remineraliser)	rem
26	TNC GH Boost (remineraliser)	rem
27
28	This list should only contain values from B colum which match 'fert' and 'rem' from C column:
29
RCFerts

Cell Formulas
Range	Formula
P4:P21	P4	=FILTER(RCFerts_Table[Products],(RCFerts_Table[Type]="fert")+(RCFerts_Table[Type]="rem"))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B29	List	=P4#

 

[Polanskiman]

Thank you Peter. Isn't that more less what I had done in the helper column (A column)? Your formula is however way simpler and I like that! Would it work with earlier versions of Excel? The file is intended to be distributed publicly so it should support older versions of Excel.

Also any specific reason why the helper column should be at the end of the table and not at the beginning?

Finally, the issue that remains is that if for some reason the column is sorted then it screws up the results of my formulas who are picking data in other sheets. The sorting causes the index/match to pick data from the wrong rows

For XL2BB yes I know about it. Next time I will use it.

Thank you again.

 

[Polanskiman]

Actually I think I figured a way to avoid the mixing up when sorting. Instead of using the helper column in the Match statement, simply use the actual column (column B) and use the selected value from the dropdown list as a mean to index/match. I was previously using the helper column in my formula.

 

[Peter_SSs]

Isn't that more less what I had done in the helper column (A column)?

Yes, it is, though only for "fert". I hadn't looked at column A as your question appeared to be about columns B & C.

Would it work with earlier versions of Excel?

No. You will need to revert to something more like you had done. I'll get back to that.

Also any specific reason why the helper column should be at the end of the table and not at the beginning?

Left or right is not an issue. To me though, I don't think I would be compiling the drop-down lists in the table itself since in virtually all cases the drop-down lists will be shorter than the full table. How did you intend to use that reduced length list in column A in your Data Validation? Or were you just going to leave all those blank rows in your DV drop-down list at the bottom?


I would consider doing the list outside the table along these lines.

Polanskiman mockup.xlsx
	B	C												O	P
1
2															fert + rem
3	Products	Type													$P$4:$P$21
4	ADA Brighy 'N'	fert													ADA Brighy 'N'
5	ADA Dosing	fert													ADA Dosing
6	ADA E.I. Mix	fert													ADA E.I. Mix
7	APFUK Trace	tr													Clives E.I. Target
8	Clives E.I. Target	fert													EI Dosing Full
9	CSM+B Trace	tr													EI Dosing Low
10	EI Dosing Full	fert													EI Dosing Mid
11	EI Dosing Low	fert													EI Modified
12	EI Dosing Mid	fert													PMDD
13	EI Modified	fert													PMDD+PO4
14	GLA EDTA Micromix	tr													Proflora Ferropol
15	PMDD	fert													Seachem Flourish
16	PMDD+PO4	fert													TNC Complete
17	Proflora Ferropol	fert													TPN+ Fertiliser
18	Rexolin APN	tr													Tropica Premium Nutrition
19	Seachem Flourish	fert													Tropica Specialised Nutrition
20	Solufeed TEC	tr													Seachem Equilibrium (remineraliser)
21	TNC Complete	fert													TNC GH Boost (remineraliser)
22	TPN+ Fertiliser	fert
23	Tropica Premium Nutrition	fert
24	Tropica Specialised Nutrition	fert
25	Seachem Equilibrium (remineraliser)	rem
26	TNC GH Boost (remineraliser)	rem
27
28	This list should only contain values from B colum which match 'fert' and 'rem' from C column:
29
RCFerts

Cell Formulas
Range	Formula
P3	P3	=CELL("address",P4)&":"&CELL("address",OFFSET(P4,COUNTIF(P4:P26,"?*")-1,,1))
P4:P26	P4	=IFERROR(INDEX(RCFerts_Table[Products],AGGREGATE(15,6,(ROW(RCFerts_Table[Products])-ROW(RCFerts_Table[#Headers]))/((RCFerts_Table[Type]="fert")+(RCFerts_Table[Type]="rem")),ROWS(P$4:P4))),"")

Cells with Data Validation
Cell	Allow	Criteria
B29	List	=INDIRECT(P3)

 

[Polanskiman]

Left or right is not an issue. To me though, I don't think I would be compiling the drop-down lists in the table itself since in virtually all cases the drop-down lists will be shorter than the full table. How did you intend to use that reduced length list in column A in your Data Validation? Or were you just going to leave all those blank rows in your DV drop-down list at the bottom?
I would consider doing the list outside the table along these lines.

Hi Peter. Highly appreciate it. Ok for the placement of the helper column.

The intention was not to leave the blanks. The secondary issues was in fact not directly the DV dropdown list but the formulas using the DV dropdown list range as a means to do an index/match and that's where the problem was coming from. This is a collaborative work excel sheet so I hadn't noticed that until yesterday after your answer.

Thank you for the updated version of the helper column formula. I had been banging my head on how to add multiple criteria to the formula. I was trying to do it with an AND statement but I failed miserably.

Few questions:

• Can you elaborate on the use of the INDIRECT statement for DV formula line?
• Would this new arrangement allow inserting rows in the table and the DV dropdown list would dynamically update?
• Prior my question here, I had integrated the helper column in the table in column A. I am on Excel for MAC but I don't a way to modify the range of the table other moving the column beyond the table to the right. What would be the best way to go?

Thank you.

 

[Peter_SSs]

Prior my question here, I had integrated the helper column in the table in column A. I am on Excel for MAC but I don't a way to modify the range of the table other moving the column beyond the table to the right. What would be the best way to go?

Starting with this one first. I do not use a Mac so I can't be any help specifically with that. The grammar of this point lost me a little but I think you are saying that you want the DV list in the table? If so, that is fine. Would you hide this column? If so, that would also hide what I have in A29 as the users do not need to see that. So I have moved the list formula to column A. You might consider simplifying the table heading in col A to something like 'ProductHelp' as that would make the A29 formula somewhat simpler.

Polanskiman mockup.xlsx
	A	B
1
2
3	Products (Helper Column)	Products
4	ADA Brighy 'N'	ADA Brighy 'N'
5	ADA Dosing	ADA Dosing
6	ADA E.I. Mix	ADA E.I. Mix
7	Clives E.I. Target	APFUK Trace
8	EI Dosing Full	Clives E.I. Target
9	EI Dosing Low	CSM+B Trace
10	EI Dosing Mid	EI Dosing Full
11	EI Modified	EI Dosing Low
12	PMDD	EI Dosing Mid
13	PMDD+PO4	EI Modified
14	Proflora Ferropol	GLA EDTA Micromix
15	Seachem Flourish	PMDD
16	TNC Complete	PMDD+PO4
17	TPN+ Fertiliser	Proflora Ferropol
18	Tropica Premium Nutrition	Rexolin APN
19	Tropica Specialised Nutrition	Seachem Flourish
20	Seachem Equilibrium (remineraliser)	Solufeed TEC
21	TNC GH Boost (remineraliser)	TNC Complete
22		TPN+ Fertiliser
23		Tropica Premium Nutrition
24		Tropica Specialised Nutrition
25		Seachem Equilibrium (remineraliser)
26		TNC GH Boost (remineraliser)
27
28		This list should only contain values from B colum which match 'fert' and 'rem' from C column:
29	$A$4:$A$21
RCFerts

Cell Formulas
Range	Formula
A4:A26	A4	=IFERROR(INDEX([Products],AGGREGATE(15,6,(ROW([Products])-ROW(RCFerts_Table[#Headers]))/(([Type]="fert")+([Type]="rem")),ROWS(A$4:A4))),"")
A29	A29	=CELL("address",INDEX(RCFerts_Table[Products (Helper Column)],1))&":"&CELL("address",OFFSET(INDEX(RCFerts_Table[Products (Helper Column)],1),COUNTIF(RCFerts_Table[Products (Helper Column)],"?*")-1,,1))

Cells with Data Validation
Cell	Allow	Criteria
B29	List	=INDIRECT(A29)

Can you elaborate on the use of the INDIRECT statement for DV formula line?

We don't know how many rows there will be in the DV list so we cannot input a range directly into the Data Validation. You will see that A29 holds the address of the actual DV range and it will automatically adapt if the list grows or shrinks. The INDIRECT(A29) tells the DV to go to A29 & use that text as the range reference for the DV.

Would this new arrangement allow inserting rows in the table and the DV dropdown list would dynamically update?

Yes.