Create List Based on Drop-Down Selection

The Great SrH

Board Regular
Joined
Jan 16, 2015
Messages
172
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 AColumn BColumn CColumn DColumn EColumn 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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,831
This is possible with formulas. What version of Excel do you have? That will determine which functions we can use.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,831
If you have the new Dynamic Array functions, it's actually pretty easy:

Book1 (version 1).xlsb
ABCDEFG
1AdvisorDateMember NameAdvisorsDatesMember Name
2Bob Bobson03/03/2021Correct AnswerAdvisor B03/03/2021Correct Answer
3Advisor C04/03/2021
4advisor d
5advisor E
6Advisor F
7Advisor G
8Advisor H
9Bob Bobson
10Shane Humphreys
11
Home
Cell Formulas
RangeFormula
E2:E10E2=SORT(UNIQUE(FILTER(ObsData!B2:B50,ObsData!B2:B50<>"")))
F2:F3F2=SORT(UNIQUE(FILTER(ObsData!F2:F50,ObsData!B2:B50=A2)))
G2G2=SORT(UNIQUE(FILTER(ObsData!C2:C50,(ObsData!B2:B50=A2)*(ObsData!F2:F50=B2))))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A2:C2List=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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,297
Messages
5,641,415
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top