Cascading Combo help

[PinkUnicorn]

I'm looking for some help with what I think is a Cascading Combo (but I might be completely wrong!). I've tried following various different suggestions but just keep failing (likely because I don't really know what I'm doing!)

I want to provide an interactive option to help users decide which system to use for an activity.

I have put the information into a table as shown below and what I need them to be able to do is have a selection box with a drop down list of first the Category, this will then give them only the options of the file types within that category, or the file formats within that category - so first question is whether I need to have these all in one column?
Then having selected the category and file type/format they will then get a result of either DAISY, MAPS or BOTH (these being the systems they can use).

Ideally i want it to be as easy and straightforward for the user, but also that in the background I can add new file types/categories in if needed in the future.

I hope this makes some sort of sense and that someone can help me please

Files.xlsx
	A	B	C	D
1	Category	File Type	FileFormat	Capability (Result)
2	Doucment/Common File	Adobe PDF	PDF	DAISY
3	Doucment/Common File	Microsft visio	VSDX	BOTH
4	Doucment/Common File	Microsoft One Note	ONE	DAISY
5	Image	Auto CAD Drawing	DWG	DAISY
6	Doucment/Common File	MS Office Word	DOCX	DAISY
7	Doucment/Common File	MS Office Excel	XLSX	DAISY
8	Doucment/Common File	MS Office Powerpoint	PPTX	BOTH
9	Doucment/Common File	MS Office Word	DOC	DAISY
10	Doucment/Common File	MS Office Excel	XLS	BOTH
11	Doucment/Common File	MS Office Powerpoint	PPT	BOTH
12	Image	GIF Image	GIF	BOTH
13	Image	TIFF Image	TIFF	BOTH
14	Mapping	ESRI Mapping Data	FREELIST	MAPS
15	Mapping	ESRI Mapping Data	GDBINDEXES	MAPS
16	Mapping	ESRI Mapoping Data	GDTABLE	MAPS
17	Image	Portable Network Graphics	PNG	DAISY
18	Images	Photoshop	PDS	DAISY
19	Images	Paintshop Pro Image	PSP	DAISY
20	Other	Internate Clendar Sharing	ICAL	BOTH
21	OLE	Object Linking	OLE	DAISY
Sheet1

 

[felixstraube]

What I would do:
Take your data and convert it into a table "Table1" in lets say sheet "Data":

Cascading combo help.xlsx
	A	B	C	D
1	Category	File Type	FileFormat	Capability (Result)
2	Doucment/Common File	Adobe PDF	PDF	DAISY
3	Doucment/Common File	Microsft visio	VSDX	BOTH
4	Doucment/Common File	Microsoft One Note	ONE	DAISY
5	Doucment/Common File	MS Office Excel	XLS	BOTH
6	Doucment/Common File	MS Office Excel	XLSX	DAISY
7	Doucment/Common File	MS Office Powerpoint	PPT	BOTH
8	Doucment/Common File	MS Office Powerpoint	PPTX	BOTH
9	Doucment/Common File	MS Office Word	DOC	DAISY
10	Doucment/Common File	MS Office Word	DOCX	DAISY
11	Image	Auto CAD Drawing	DWG	DAISY
12	Image	GIF Image	GIF	BOTH
13	Image	Portable Network Graphics	PNG	DAISY
14	Image	TIFF Image	TIFF	BOTH
15	Images	Paintshop Pro Image	PSP	DAISY
16	Images	Photoshop	PDS	DAISY
17	Mapping	ESRI Mapping Data	GDTABLE	MAPS
18	Mapping	ESRI Mapping Data	FREELIST	MAPS
19	Mapping	ESRI Mapping Data	GDBINDEXES	MAPS
20	OLE	Object Linking	OLE	DAISY
21	Other	Internate Clendar Sharing	ICAL	BOTH
Data

Then create an auxiliary Sheet (which you can hide later) like this:

Cascading combo help.xlsx
	A	B	C	D	E	F	G	H
1	Unique Categories		Category	Unique Filetype By Category		Category	File Type	FileFormat
2	Doucment/Common File		Doucment/Common File	Adobe PDF		Doucment/Common File	Adobe PDF	PDF
3	Image		Doucment/Common File	Microsft visio		Doucment/Common File	Microsft visio	VSDX
4	Images		Doucment/Common File	Microsoft One Note		Doucment/Common File	Microsoft One Note	ONE
5	Mapping		Doucment/Common File	MS Office Excel		Doucment/Common File	MS Office Excel	XLS
6	OLE		Doucment/Common File	MS Office Powerpoint		Doucment/Common File	MS Office Excel	XLSX
7	Other		Doucment/Common File	MS Office Word		Doucment/Common File	MS Office Powerpoint	PPT
8			Image	Auto CAD Drawing		Doucment/Common File	MS Office Powerpoint	PPTX
9			Image	GIF Image		Doucment/Common File	MS Office Word	DOC
10			Image	Portable Network Graphics		Doucment/Common File	MS Office Word	DOCX
11			Image	TIFF Image		Image	Auto CAD Drawing	DWG
12			Images	Paintshop Pro Image		Image	GIF Image	GIF
13			Images	Photoshop		Image	Portable Network Graphics	PNG
14			Mapping	ESRI Mapping Data		Image	TIFF Image	TIFF
15			OLE	Object Linking		Images	Paintshop Pro Image	PSP
16			Other	Internate Clendar Sharing		Images	Photoshop	PDS
17						Mapping	ESRI Mapping Data	FREELIST
18						Mapping	ESRI Mapping Data	GDBINDEXES
19						Mapping	ESRI Mapping Data	GDTABLE
20						OLE	Object Linking	OLE
21						Other	Internate Clendar Sharing	ICAL
Aux

Cell Formulas
Range	Formula
A2:A7	A2	=SORT(UNIQUE(Table1[Category]))
C2:D16	C2	=SORT(SORT(UNIQUE(Table1[[Category]:[File Type]]),2),1)
F2:H21	F2	=SORT(SORT(SORT(UNIQUE(Table1[[Category]:[FileFormat]]),3),2),1)
Dynamic array formulas.

Then go to your sheet where you want to have the dropdowns, in my example "Sheet1" like this:

Cascading combo help.xlsx
	B	C	D	E
1	Category	File Type	File Format	Capability
2	Doucment/Common File	Adobe PDF	PDF	DAISY
3	Doucment/Common File	Microsft visio	VSDX	BOTH
4	Doucment/Common File	Microsoft One Note	ONE	DAISY
5	Doucment/Common File	MS Office Excel	XLS	BOTH
6	Doucment/Common File	MS Office Excel	XLSX	DAISY
7	Doucment/Common File	MS Office Powerpoint	PPT	BOTH
8	Doucment/Common File	MS Office Powerpoint	PPTX	BOTH
9	Doucment/Common File	MS Office Word	DOC	DAISY
10	Doucment/Common File	MS Office Word	DOCX	DAISY
11	Image	Auto CAD Drawing	DWG	DAISY
12	Image	GIF Image	GIF	BOTH
13	Image	Portable Network Graphics	PNG	DAISY
14	Image	TIFF Image	TIFF	BOTH
15	Images	Paintshop Pro Image	PSP	DAISY
16	Images	Photoshop	PDS	DAISY
17	Mapping	ESRI Mapping Data	FREELIST	MAPS
18	Mapping	ESRI Mapping Data	GDBINDEXES	MAPS
19	Mapping	ESRI Mapping Data	GDTABLE	MAPS
20	OLE	Object Linking	OLE	DAISY
21	Other	Internate Clendar Sharing	ICAL	BOTH
Sheet1

Cell Formulas
Range	Formula
E2:E21	E2	=IFERROR(FILTER(Table1[Capability (Result)],(Table1[Category]=Sheet1!B2)*(Table1[File Type]=Sheet1!C2)*(Table1[FileFormat]=Sheet1!D2)),"")

Cells with Data Validation
Cell	Allow	Criteria
B2:B21	List	=Category
C2:C21	List	=FileType
D2:D21	List	=FileFormat

And here create the following named ranges:

First create named range "Category" like this:

=DESREF(Aux!$A$2;0;0;CONTARA(Aux!$A$2:$A$10000))

Then select the cell B2 and create a list data validation with "=Category", and as you already know will get the dropdown in B2 for Categories.

Now select cell C2 (this is important as it will have a relative reference to B2, the cell to the left) an create the named range "FileType" like this:

=DESREF(Aux!$D$1;COINCIDIR(Sheet1!A2;Aux!$C$2:$C$10000;0);;SUMA((Aux!$C$2:$C$10000=Sheet1!A2)*1))

Now still in C2 create a list data validation with "=FileType", and you will get you dropdown for filetype.

And finally select D2 (also important for the relative reference), an create the named range "FileFormat" like this:

=DESREF(Aux!$H$1;COINCIDIR(Sheet1!B2&Sheet1!C2;Aux!$F$2:$F$10000&Aux!$G$2:$G$10000;0);;SUMA((Aux!$F$2:$F$10000=Sheet1!B2)*(Aux!$G$2:$G$10000=Sheet1!C2)))

And as you guessed in D2 create the data validation with "=FileFormat".

Last use the following formula in E2:

=SI.ERROR(FILTRAR(Table1[Capability (Result)];(Table1[Category]=Sheet1!B2)*(Table1[File Type]=Sheet1!C2)*(Table1[FileFormat]=Sheet1!D2));"")

Last select the whole row and copy down.

I also upload a working book here: Cascading combo help.zip


As you add information to your table everything will update.
Keep in mind that if you change or delete data in Table1 you information in Sheet1 may have wrong entries. But dropdown should populate with the correct data.
Also keep in mind to use the dropdowns from left to right in each row, because they depend on each other in that order.

Hope this helps

 

[PinkUnicorn]

Thank you so much for the above. I think perhaps I didn't explain what I was trying to achieve by the Cascading Combo - I hoped to have one sheet which simply has Combo Boxes on it, so the user firstly goes to the Combo box for the Category and chooses Images, Mapping etc by clicking on the correct one. They then go to the second Combo box and will only get shown those options which align to that category and they choose the relevant one by clicking on that. This will then result in them being shown a result which with be either DAISY, MAPS or BOTH. It's this interactive bit that I'm struggling with - if you can advise at all that would be fantastic, thank you

 

[felixstraube]

Easy fix.
In sheet "Aux" you don't need columns F:H. just delete them.
And you can delete/not create named range FileFormat
And lastly in Sheet1 delete column D which is not longer needed and replace formula in the new column D (Capability, before column E) with this in D2, and drag down:

=IFERROR(TEXTJOIN(" / ",1,UNIQUE(FILTER(Table1[Capability (Result)],(Table1[Category]=Sheet1!B2)*(Table1[File Type]=Sheet1!C2)))),"")

Now you can select in B the category, in C the FileType, and in D you get you result, which is shown like this:

DAISY

BOTH

BOTH / DAISY

MAPS

I showed all possibilities in Sheet1. If you just want this once, delete the rows from 3 onwards.

Here is the working example:

Cascading combo help (1).zip

 

[felixstraube]

Having a look again i think you just want the results DAISY, MAPS or BOTH.
Use this formula in D2 and drag down:

=LET(r,IFERROR(TEXTJOIN(" / ",1,UNIQUE(FILTER(Table1[Capability (Result)],(Table1[Category]=Sheet1!B2)*(Table1[File Type]=Sheet1!C2)))),""),IF(ISNUMBER( FIND("/",r)),"BOTH",r))

 

[PinkUnicorn]

Thank you so much for the above, and I'm sorry it has taken me so long to reply - your help with this has been invaluable.

Is it possible to ask one further question please - is there any way I can create something on Sheet1 which the user can click to clear the entries so they see clear boxes? The users I am working with aren't fabulous with tech and if they were able to start with blank boxes I think it could really help them. Thank you again for your really helpful answers

 

[felixstraube]

You can create a macro like so:

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("B2:C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
End Sub

And asigne the macro to a Textbox or Button for example.
Here is a working example. You may have to unlock the macro book before you can use it.

Cascading combo help (2).zip

 

[PinkUnicorn]

That is absolutely fantastic. thank you so much for your help with all this, it really is appreciated

 

[felixstraube]

You are most welcome. Thanks for the feedback!