Excel: How to create a drop down list that pulls up all the data associated with the selection made?

[exc_can]

I am trying to create a master sheet where all the information associated with each name is entered. It is in a table and each name is repeated in 4 rows and then it goes to the next name and so on. Each name has 4 (1,2,3,4) different ID#'s that indicates the depth where the sample was taken. I am entering different lab test results for each location based on the depth the sample was taken from.

On a separate sheet I want to create a drop down menu to select the location name to display all the data/lab results associated with that location including the depth where the sample was taken from? Can this be done in excel without using a macro? Please help.

 

[Trevor_S]

The only option I can think of without using a macro would be to filter the data. Would be easiest to do this straight on the master sheet, rather than a separate sheet though?

 

[uzaname]

You should be able to do it with Form Controls (Developer>Insert>Form Controls) and functions like INDEX and VLOOKUP.

 

[FDibbins]

No filters of forms needed

Assuming your data looks like this...

	A​	B​	C​	D​
1​	Sheet7
2​	Name	ID	Data1	data1
3​	aa	1​	10​	110​
4​	aa	2​	20​	220​
5​	aa	3​	30​	330​
6​	aa	4​	40​	440​
7​	bb	1​	50​	550​
8​	bb	2​	60​	660​
9​	bb	3​	70​	770​
10​	bb	4​	80​	880​
11​	cc	1​	90​	990​
12​	cc	2​	100​	1100​
13​	cc	3​	110​	1210​
14​	cc	4​	120​	1320​
15​	dd	1​	130​	1430​
16​	dd	2​	140​	1540​
17​	dd	3​	150​	1650​
18​		4​	160​	1760​

To get this...

	A​	B​	C​	D​
1​	Sheet8
2​	aa	ID	Data1	data1
3​		1​	10​	110​
4​		2​	20​	220​
5​		3​	30​	330​
6​		4​	40​	440​

A2 would be your selection. This can be a drop-down or a manual entry
B3=IFERROR(INDEX(Sheet7!B:B,SMALL(IF(Sheet7!$A$3:$A$17=$A$2,ROW(Sheet7!$A$3:$A$17)),ROWS(Sheet7!$A$2:A2))),"")
ARRAY entered using CTRL SHIFT ENTER, not just enter. You will see your formula wrapped in {}

Then copy down and across