Create List Based on Drop-Down Selection

[The Great SrH]

Hi all,

I'm hoping you lovely people can help me solve an issue i'm having with Excel. I can't seem to wrap my brain around the best way of doing it. I'm comfortable if it should be VBA or just formulas but my brains hurting now from searching for a solution.

For some context, I have a spreadsheet which has a number of functions but essentially, it pulls in data which has been extracted from a Microsoft Form and then creates a visual view of the rows of data.

On the home page ("Home") of the spreadsheet I want to have a couple of drop-down boxes which will then determine which row of data (from "ObsData" tab) to pull the visual view report from. The visual report tab ("Individual Report") will populate using Index/Match or VLookUps once the option has been determined on the Home tab by user.

The data will be ever growing but will look something like this:

Column A	Column B	Column C	Column D	Column E	Column F
Manager Name​	Advisor Name​	Member Name​	CIS Number​	Date Of Interaction​	Date Of observation​
Tester 1​	Bob Bobson​	Member A​	1​	01/03/2021​	04/03/2021​
Tester 4​	Advisor B​	B​	2​	03/03/2021​	04/03/2021​
Tester 1​	Advisor C​	C​	3​	03/02/2021​	26/02/2021​
Tester 3​	advisor d​	d​	4​	18/02/2021​	01/03/2021​
Tester 1​	advisor E​	E​	5​	16/02/2021​	24/02/2021​
Tester 3​	Advisor F​	F​	6​	22/02/2021​	28/01/2021​
Tester 2​	Advisor G​	G​	7​	10/02/2021​	12/02/2021​
Tester 2​	Advisor H​	H​	8​	17/02/2021​	03/03/2021​
Tester 6​	Bob Bobson​	Correct Answer​	123456789​	02/03/2021​	03/03/2021​
Tester 5​	Shane Humphreys​	Test​	2343​	02/03/2021​	04/03/2021​

So on the Home tab I would like to have the following dropdowns but they will need to talk to each other:

Dropdown 1 - Advisor Name (Column B)
Dropdown 2 - Date of Observation (Column F)
Dropdown 3 (unless too complicated) - Member Name (Column C)

If I was to select "Bob Bobson" from Dropdown 1, Dropdown 2 would give me the options of "04/03/2021" or "03/03/2021".
When I select "03/03/2021" from Dropdown 2, Dropdown 3 will only give me the option of "Correct Answer"

Preferably, the options in the dropdowns would be in the correct order but I'm not too precious about that!

Does anybody have a solution to my request?!

Thanks so much in advance

 

[Eric W]

This is possible with formulas. What version of Excel do you have? That will determine which functions we can use.

 

[The Great SrH]

I'm using the 365 version

 

[Eric W]

If you have the new Dynamic Array functions, it's actually pretty easy:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	Advisor	Date	Member Name		Advisors	Dates	Member Name
2	Bob Bobson	03/03/2021	Correct Answer		Advisor B	03/03/2021	Correct Answer
3					Advisor C	04/03/2021
4					advisor d
5					advisor E
6					Advisor F
7					Advisor G
8					Advisor H
9					Bob Bobson
10					Shane Humphreys
11
Home

Cell Formulas
Range	Formula
E2:E10	E2	=SORT(UNIQUE(FILTER(ObsData!B2:B50,ObsData!B2:B50<>"")))
F2:F3	F2	=SORT(UNIQUE(FILTER(ObsData!F2:F50,ObsData!B2:B50=A2)))
G2	G2	=SORT(UNIQUE(FILTER(ObsData!C2:C50,(ObsData!B2:B50=A2)*(ObsData!F2:F50=B2))))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2:C2	List	=E2#

Change the ranges in the E2:G2 formulas to match your ObsData sheet. Then when you set up the Data Validation in A2:C2, choose List, and in the Source box, put

=E2#

for A2, and =F2# for B2, and =G2# for C2. You'd probably want to put the E:G columns out of view somewhere.

 

[The Great SrH]

Amazing - thanks Eric!