Validation dependent lists

[mrdutchexcel]

Hello everyone,

This is my first post here. I've been working with Excel for quite a few years now but now stumbled upon this challenge, for which I can't seem to find a solution. If it's even supported in Excel 2016. Please note I'm not using Office 365 so I can't use all function that Office 365 has in Office 2016.

I have the following entries on a worksheet:

Column1 Column2
SID lvrs0102
SID lvrs0103
SID lvrs0104
SIA lvrs0205
SIA lvrs0206
SIC lvrs0308
SIC lvrs0309

The values in Column1 are not unique, the ones in Column2 are.

What I like to achieve is the following:
1. I want to be able to select a distinct value from Column1 in a data validation list. So I'm expecting the values SID, SIA and SIC to appear in that list.
2. Based on the selection in 1) I would like to show the dependent entries from Column2 in another data validation list. So if I select SID, I expect only lvrs0102, lvrs0103 and lvrs0104 to appear in that dependent list.

Is there any way you guys can help me out here? Any help would be greatly appreciated.

Regards,
Niels

 

[Yongle]

I prefer to use structured excel tables as sources of individual dropdowns
- they grow automatically as data is added etc

Create 4 tables as in picture
- with headers ITEMS, SIS, SIA, SIC
- also name the tables ITEMS, SIS, SIA, SIC
(the tables would be on another sheet in your workbook)

A2 data validation (copied down)
Allow: List
Source:
=INDIRECT("ITEMS[ITEMS]")

B2 data validation (copied down)
Allow:List
Source:
=INDIRECT(A2)

WARNING
- table names cannot be the same as cell references etc
- so if column A contains values like AB12 or A2 etc then this needs an amended approach

 

[mrdutchexcel]

I've kind of tried that; and it works, but the issue with this approach is that for each line I have an extra bunch of columns that are used to retrieve data from by using VLOOKUP. So for the line with SID and lvrs0103 I have all kinds of other information on that same line like #CPUs, memory, etc. This concept works based on columns, and what I actually need is that it works based on rows. Any ideas?

 

[jasonb75]

I think that you might need to provide a visual example of what you have and what you want end up with.

 

[Yongle]

you did not mention any other constraints in post#1
your issue is difficult to follow from words alone
please illustrate with sample data
XL2BB is a useful tool for this

 

[mrdutchexcel]

Sorry for the late response; here are two simple screenshots of what I like to achieve.
One is the selection part, the other one with the table is the 'master data'.

 

[jasonb75]

If I'm following the task correctly, this is how I would do it. I've done it all one one sheet to keep the example simple, but it will work 2 sheets.

Book1
	A	B	C	D	E	F	G	H	I
1	System	SIA		System		System	Server	Domain	Secure
2	Server	lvrs0205		SID		SID	lvrs0102	Unix	Yes
3	Domain	Linux		SIA		SID	lvrs0103	Unix	Yes
4	Secure	No		SIC		SIA	lvrs0205	Linux	No
5						SIA	lvrs0206	Linux	No
6						SIC	lvrs0308	Linux	Yes
7						SIC	lvrs0309	Linux	No
Sheet1

Cell Formulas
Range	Formula
B3	B3	=VLOOKUP($B$2,$G$2:$H$7,2,0)
B4	B4	=VLOOKUP(B2,$G$2:$I$7,3,0)
D2:D4	D2	=IFERROR(INDEX($F:$F,AGGREGATE(15,6,ROW($F$2:$F$7)/ISNA(MATCH($F$2:$F$7,$D$1:$D1,0)),1)),"")

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=$D$2:$D$4
B2	List	=OFFSET($G$2,MATCH($B$1,$F$2:$F$7,0)-1,0,COUNTIF($F$2:$F$7,$B$1),1)

 

[mrdutchexcel]

That works perfectly, thanks! Just what I needed.

One additional question: would it be possible to get rid of the list from column D and then fill the validation list with unique values from column F instead?

 

[jasonb75]

If you do the list from column F then you will not be able to get unique items in the dropdown, if SID was listed 10 times in column F then it would be listed 10 times in the dropdown.

The additional column is the only way to extract the items from the list.

 

[mrdutchexcel]

That works. Thanks very much for your help you guys.