Dropdown list to show data from another table/column?

[Sp8y8]

Hi, I am looking for help with a formula to populate a column with data from another by using a dropdown menu.

I have a dropdown menu in A3, the choice is basically 1-5. the 1-5 corresponds to 5 columns containing names.
What I would like to do is use the dropdown menu (already in place), select "1", then all the names under "1" (already on another column) to show below the dropdown menu in A3, select "3" and all the names in the column under "3" will then populate under A3, etc....

table range is E69:I125, E69 contains 1,2,3,4 and 5, rows 70-125 have all the names, 5 columns, 5 lists. Full list of each need to show when Value is selected in A3 from dropdown list.

Any help would be greatly appreciated.

Regards,

Colin.

 

[Fluff]

How about, in A4 dragged down

=INDEX($E$70:$I$125,ROWS(A$4:A4),$A$3)

 

[Sp8y8]

Hi, I am looking for help with a formula to populate a column with data from another by using a dropdown menu.

I have a dropdown menu in A3, the choice is basically 1-5. the 1-5 corresponds to 5 columns containing names.
What I would like to do is use the dropdown menu (already in place), select "1", then all the names under "1" (already on another column) to show below the dropdown menu in A3, select "3" and all the names in the column under "3" will then populate under A3, etc....

table range is E69:I125, E69 contains 1,2,3,4 and 5, rows 70-125 have all the names, 5 columns, 5 lists. Full list of each need to show when Value is selected in A3 from dropdown list.

Any help would be greatly appreciated.

Regards,

Colin.

How about, in A4 dragged down

=INDEX($E$70:$I$125,ROWS(A$4:A4),$A$3)

unfortunately this did not work,
A3 is the dropdown menu, when one of the values is selected, I would like the list of names to appear that are in the columns under each shift colour that is selected. column E69-I69 contain 1,2,3,4,5. If "1"is selected in A3, then I would like all the names that appear under "1" in column E69(E70:E125) to appear in A4 downwards, if "2" is selected in A3, then I would like all the names that appear under "2" in I69 (I70:I125) to appear in A4 downwards etc.

 

[Fluff]

In what way didn't it work?

 

[Sp8y8]

apologies if I am doing something wrong with this, I have copied the formula and placed it in A4, which returns #VALUE!

 

[Fluff]

What exactly is in A3? Is it a number, or does it have the quotes like "1"?

 

[Sp8y8]

E-A1 2022 FINAL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	01/01/28	KEY DATE IN COLUMN 'A1'. This sets up the entire workbook for the year ahead. Make sure you "SAVE AS (CURRENT YEAR)". Keep a MASTER TEMPLATE before populating.
2
3	AMBER	Use of Electronic A1-H4HM:			In each box on the A1, select the position of Runners and riders using the codes from 1-12. For non attendance, use the 'Absence Codes'.
4	#VALUE!	SUBMITTING H4H:			Open E-A1 in desktop app, complete H4HM names, dates etc. (Noting ref No. in column 1), Select date on A1 eg JAN 20th, select from Menu, 'INSERT HYPERLINK', when box appears, select 'PLACE IN THIS DOCUMENT' - then select H4HM, Change 'TEXT TO DISPLAY' to the ref Number eg H7, then change the CELL REFERENCE NUMBER to A(plus the row the ref is on, eg. A7), This will provide link to H4HM from E-A1.
5	Stewart G
6	Speight C
7	Beattie S
8	MacKenzie C	Runners Positions		DETACHED DUTY ROTA					DUTY ROTA'S					H4H'S	TOTAL	PENDING	NO DATE
9	Cameron	1 =	OIC1		ALL	FIREY	CREW	DRIVER	DUTY FIREFIGHTER		DUTY COOK			Beattie P	-	-	-
10	Ovenstone J	2 =	DVR1		GARY	STEVIE	GARY	STEVIE	Today	NEXT:	Today	NEXT:		Stewart G	-	-	-
11	James A	3 =	BA1	PADDY					2/10/21	CHRIS	2/10/21	CHRIS		Speight C	-	-	-
12	Nimmo G	4 =	BA2	GARY	27/09/21		27/09/21		CHRIS	13/4/21	CHRIS	13/4/21		Beattie S	-	-	-
13	Wands R	5 =	ECO1	COLIN	30/09/21		30/09/21		ADAM	14/4/21	ADAM	14/4/21		Cameron J	-	-	-
14		6 =	OIC2	STEVIE	27/09/21	27/09/21		27/09/21	JC	15/4/21	JC	15/4/21		James A	-	-	-
15	TOIL	7 =	DVR2	CHRIS	28/09/21	30/09/21		28/09/21	OVY	16/4/21	OVY	16/4/21		MacKenzie C	-	-	-
16	RED	8 =	BA2	OVY	29/09/21	30/09/21		29/09/21	GOGS	17/4/21	GOGS	17/4/21		Ovenstone J	-	-	-
17	Edgar C	9 =	BA2	ADAM	30/09/21	30/09/21			STEVIE	18/4/21	STEVIE	18/4/21		Nimmo G	-	-	-
18	Paul A	10 =	ECO2	JC	01/10/21	01/10/21			ROBERT	19/4/21	ROBERT	19/4/21		Wands R	-	-	-
19	King G	11 =	SPR1	GORDON	02/10/21	02/10/21			Input todays date into oldest date (CTRL :)					TOTALS	-	-	-
20	Teevan R	12 =	SPR2	ROBERT	03/10/21	03/10/21								ORANGE HOURS
21	Pirie B			USE 1ST COLUMN FOR INPUT, 2ND,3RD & 4TH COLUMN AS GUIDE										NAME	START	TO DATE	TOTAL
22	Nelson S													PADDY	56	14	42
23	Hinks N	A1 ABSENCE CODES							H4HM SHEET					GARY	56		56
24	Webster P	SM	SICKNESS MANAGEMENT		DD	DETACHED DUTIES (+STATION)			TBC	To be confirmed				COLIN	56		56
25	Corson L	C	COURSE LEAVE		OPR(N)	ORANGE DAY IN			N/A	When TOIL selected.				STEVIE	56		56
26	Hughes A	SPL	SPECIAL LEAVE		OT(?)	OVERTIME IN + (WATCH INITIAL)			PAO	Pre Arranged Overtime				CHRIS	56		56
27	WHITE	RL	ROTA LEAVE		ISO	ISOLATION (COVID-19)								OVY	56		56
28	Cameron O	CPL	COMPASSIONATE LEAVE		CVD	OFF DUE TO COVID								ADAM	56		56
29	Wann S	ROFF	ROTA OFF											JC	56		56
30	Adair R													GORDON	56		56
31	Cox M													ROBERT	56		56
32	McLellan J
33	Carr L
34	Wilson A
35	Marsh K
36	Pickle C
37
38	GREEN
39	Gallacher C
40	Edwards G
41	Cameron J
42	Cowan L
43	Stewart G
44	Hutchison M
45	Innes A
46	Dawson D
47	Heaver D
48
49	BLUE
50	Adams J
51	Reid S
52	MacTaggart J
53	McLean S
54	Cobbett O
55	Bruce J
56	Dickson C
57	McGhee M
58	McDonald J
59
60					BLUE	RED	WHITE	GREEN	AMBER
61	COMPLETE				Adams J	Edgar C	Cameron O	Gallacher C	Beattie P
62	PENDING				Reid S	Paul A	Wann S	Edwards G	Stewart G
63	NO DATE				MacTaggart J	King G	Adair R	Cameron J	Speight C
64	N/A				McLean S	Teevan R	Cox M	Cowan L	Beattie S
65	CANCELLED				Cobbett O	Pirie B	McLellan J	Stewart G	MacKenzie C
66	R				Bruce J	Nelson S	Carr L	Hutchison M	Cameron
67	D				Dickson C	Hinks N	Wilson A	Innes A	Ovenstone J
68	N				McGhee M	Webster P	Marsh K	Dawson D	James A
69	AMBER				McDonald J	Corson L	Pickle C	Heaver D	Nimmo G
70	RED					Hughes A			Wands R
71	WHITE
72	GREEN				TOIL	TOIL	TOIL	TOIL	TOIL
73	BLUE				RED	WHITE	GREEN	AMBER	BLUE
74	AMBER WATCH				Edgar C	Cameron O	Gallacher C	Beattie P	Adams J
75	RED WATCH				Paul A	Wann S	Edwards G	Stewart G	Reid S
76	WHITE WATCH				King G	Adair R	Cameron J	Speight C	MacTaggart J
77	GREEN WATCH				Teevan R	Cox M	Cowan L	Beattie S	McLean S
78	BLUE WATCH				Pirie B	McLellan J	Stewart G	MacKenzie C	Cobbett O
79					Nelson S	Carr L	Hutchison M	Cameron	Bruce J
80					Hinks N	Wilson A	Innes A	Ovenstone J	Dickson C
81					Webster P	Marsh K	Dawson D	James A	McGhee M
82					Corson L	Pickle C	Heaver D	Nimmo G	McDonald J
83					Hughes A			Wands R
84					WHITE	GREEN	AMBER	BLUE	RED
85					Cameron O	Gallacher C	Beattie P	Adams J	Edgar C
86					Wann S	Edwards G	Stewart G	Reid S	Paul A
87					Adair R	Cameron J	Speight C	MacTaggart J	King G
88					Cox M	Cowan L	Beattie S	McLean S	Teevan R
89					McLellan J	Stewart G	MacKenzie C	Cobbett O	Pirie B
90					Carr L	Hutchison M	Cameron	Bruce J	Nelson S
91					Wilson A	Innes A	Ovenstone J	Dickson C	Hinks N
92					Marsh K	Dawson D	James A	McGhee M	Webster P
93					Pickle C	Heaver D	Nimmo G	McDonald J	Corson L
94							Wands R		Hughes A
95					GREEN	AMBER	BLUE	RED	WHITE
96					Gallacher C	Beattie P	Adams J	Edgar C	Cameron O
97					Edwards G	Stewart G	Reid S	Paul A	Wann S
98					Cameron J	Speight C	MacTaggart J	King G	Adair R
99					Cowan L	Beattie S	McLean S	Teevan R	Cox M
100					Stewart G	MacKenzie C	Cobbett O	Pirie B	McLellan J
101					Hutchison M	Cameron	Bruce J	Nelson S	Carr L
102					Innes A	Ovenstone J	Dickson C	Hinks N	Wilson A
103					Dawson D	James A	McGhee M	Webster P	Marsh K
104					Heaver D	Nimmo G	McDonald J	Corson L	Pickle C
105						Wands R		Hughes A
106					AMBER	BLUE	RED	WHITE	GREEN
107					Beattie P	Adams J	Edgar C	Cameron O	Gallacher C
108					Stewart G	Reid S	Paul A	Wann S	Edwards G
109					Speight C	MacTaggart J	King G	Adair R	Cameron J
110					Beattie S	McLean S	Teevan R	Cox M	Cowan L
111					MacKenzie C	Cobbett O	Pirie B	McLellan J	Stewart G
112					Cameron	Bruce J	Nelson S	Carr L	Hutchison M
113					Ovenstone J	Dickson C	Hinks N	Wilson A	Innes A
114					James A	McGhee M	Webster P	Marsh K	Dawson D
115					Nimmo G	McDonald J	Corson L	Pickle C	Heaver D
116					Wands R		Hughes A
FORMULA SHEET

Cell Formulas
Range	Formula
A4	A4	=INDEX($E$70:$I$125,ROWS(A$4:A60),$A$3)
O9:O18	O9	=SUM(--EXACT(N9, H4HM!$B$7:$B$106))
P9:P18	P9	=COUNTIFS(H4HM!$B$7:$B$106,"*"&'FORMULA SHEET'!N9&"*", H4HM!$I$7:$I$106,"*"&'FORMULA SHEET'!$A$62&"*")
Q9:Q18	Q9	=COUNTIFS(H4HM!$B$7:$B$106,"*"&'FORMULA SHEET'!N9&"*", H4HM!$I$7:$I$106,"*"&'FORMULA SHEET'!$A$63&"*")
O19:Q19	O19	=SUM(O9:O18)
E10	E10	=INDEX(D12:H20,MATCH(MIN(E12:E20),E12:E20,0),1)
F10	F10	=INDEX(D14:F20,MATCH(MIN(F14:F20),F14:F20,0),1)
G10	G10	=INDEX(D12:H13,MATCH(MIN(G12:G13),G12:G13,0),1)
H10	H10	=INDEX(D14:H16,MATCH(MIN(H14:H16),H14:H16,0),1)
I11,K11	I11	=TODAY()
J11,L11	J11	=INDEX(I12:J18,MATCH(MIN(J12:J18),J12:J18,0),1)
G12:G13	G12	=E12
F14,F17:F20	F14	=E14
H14:H16	H14	=E14
Q22:Q31	Q22	=O22-P22

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E12:E20	Other Type	Color scale		NO
E12:E20	Other Type	Color scale		NO
G12:G13	Other Type	Color scale		NO
D10	Expression	=TODAY<10	text	NO
Q9:Q18	Cell Value	>0	text	NO
P9:R18	Cell Value	>0	text	NO
L11	Expression	=TODAY<10	text	NO
J11	Expression	=TODAY<10	text	NO
F12:F20	Other Type	Color scale		NO
H14:H17	Other Type	Color scale		NO
E11:H11,F12:H20	Other Type	Color scale		NO
E11:H11,F12:H20	Other Type	Color scale		NO
J12:J18	Other Type	Color scale		NO
J12:J18	Cell Value	=$L$43	text	NO
L12:L18	Other Type	Color scale		NO
L12:L18	Other Type	Color scale		NO
L12:L18	Other Type	Color scale		NO
L12:L18	Cell Value	=$L$43	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A3	List	=$A$69:$A$73

 

[Sp8y8]

What exactly is in A3? Is it a number, or does it have the quotes like "1"?

A3 is a dropdown menu with 5 colours (data validation reference in A69:A73). see E60:I60 with the lists i would like to appear when one of these colours are selected.

 

[Fluff]

So why did you say that A3 had 1,2,3 etc? If you simplify your question you won't get something that works.
Try

=INDEX($E$61:$I$70,ROWS(A$4:A4),MATCH($A$3,$E$60:$I$60,0))

 

[Sp8y8]

So why did you say that A3 had 1,2,3 etc? If you simplify your question you won't get something that works.
Try

=INDEX($E$61:$I$70,ROWS(A$4:A4),MATCH($A$3,$E$60:$I$60,0))

Sincere apologies and humbled gratitude to you.
Thank you so very much, works perfect!