Dropdown list to show data from another table/column?

Sp8y8

New Member
Joined
Mar 28, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
How about, in A4 dragged down
Excel Formula:
=INDEX($E$70:$I$125,ROWS(A$4:A4),$A$3)
 

Sp8y8

New Member
Joined
Mar 28, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
In what way didn't it work?
 

Sp8y8

New Member
Joined
Mar 28, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
What exactly is in A3? Is it a number, or does it have the quotes like "1"?
 

Sp8y8

New Member
Joined
Mar 28, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

E-A1 2022 FINAL.xlsx
ABCDEFGHIJKLMNOPQ
101/01/28KEY 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
3AMBERUse 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.
5Stewart G
6Speight C
7Beattie S
8MacKenzie CRunners PositionsDETACHED DUTY ROTADUTY ROTA'S H4H'S TOTAL PENDING NO DATE
9Cameron1 =OIC1ALLFIREYCREWDRIVERDUTY FIREFIGHTERDUTY COOK Beattie P ---
10Ovenstone J2 =DVR1GARYSTEVIEGARYSTEVIETodayNEXT:TodayNEXT: Stewart G ---
11James A3 =BA1PADDY2/10/21CHRIS2/10/21CHRIS Speight C ---
12Nimmo G4 =BA2GARY27/09/2127/09/21CHRIS13/4/21CHRIS13/4/21 Beattie S ---
13Wands R5 =ECO1COLIN30/09/2130/09/21ADAM14/4/21ADAM14/4/21 Cameron J ---
146 =OIC2STEVIE27/09/2127/09/2127/09/21JC15/4/21JC15/4/21 James A ---
15TOIL7 =DVR2CHRIS28/09/2130/09/2128/09/21OVY16/4/21OVY16/4/21 MacKenzie C ---
16RED8 =BA2OVY29/09/2130/09/2129/09/21GOGS17/4/21GOGS17/4/21 Ovenstone J ---
17Edgar C9 =BA2ADAM30/09/2130/09/21STEVIE18/4/21STEVIE18/4/21 Nimmo G ---
18Paul A10 =ECO2JC01/10/2101/10/21ROBERT19/4/21ROBERT19/4/21 Wands R ---
19King G11 =SPR1GORDON02/10/2102/10/21Input todays date into oldest date (CTRL :) TOTALS ---
20Teevan R12 =SPR2ROBERT03/10/2103/10/21ORANGE HOURS
21Pirie BUSE 1ST COLUMN FOR INPUT, 2ND,3RD & 4TH COLUMN AS GUIDENAMESTARTTO DATETOTAL
22Nelson SPADDY561442
23Hinks NA1 ABSENCE CODESH4HM SHEETGARY5656
24Webster PSMSICKNESS MANAGEMENTDDDETACHED DUTIES (+STATION)TBCTo be confirmedCOLIN5656
25Corson LCCOURSE LEAVEOPR(N)ORANGE DAY INN/AWhen TOIL selected.STEVIE5656
26Hughes ASPLSPECIAL LEAVEOT(?)OVERTIME IN + (WATCH INITIAL)PAOPre Arranged OvertimeCHRIS5656
27WHITERLROTA LEAVEISOISOLATION (COVID-19)OVY5656
28Cameron OCPLCOMPASSIONATE LEAVECVDOFF DUE TO COVIDADAM5656
29Wann SROFFROTA OFFJC5656
30Adair RGORDON5656
31Cox MROBERT5656
32McLellan J
33Carr L
34Wilson A
35Marsh K
36Pickle C
37
38GREEN
39Gallacher C
40Edwards G
41Cameron J
42Cowan L
43Stewart G
44Hutchison M
45Innes A
46Dawson D
47Heaver D
48
49BLUE
50Adams J
51Reid S
52MacTaggart J
53McLean S
54Cobbett O
55Bruce J
56Dickson C
57McGhee M
58McDonald J
59
60BLUEREDWHITEGREENAMBER
61COMPLETEAdams JEdgar CCameron OGallacher CBeattie P
62PENDINGReid SPaul AWann SEdwards GStewart G
63NO DATEMacTaggart JKing GAdair RCameron JSpeight C
64N/AMcLean STeevan RCox MCowan LBeattie S
65CANCELLEDCobbett OPirie BMcLellan JStewart GMacKenzie C
66RBruce JNelson SCarr LHutchison MCameron
67DDickson CHinks NWilson AInnes AOvenstone J
68NMcGhee MWebster PMarsh KDawson DJames A
69AMBERMcDonald JCorson LPickle CHeaver DNimmo G
70REDHughes AWands R
71WHITE
72GREENTOILTOILTOILTOILTOIL
73BLUEREDWHITEGREENAMBERBLUE
74AMBER WATCHEdgar CCameron OGallacher CBeattie PAdams J
75RED WATCHPaul AWann SEdwards GStewart GReid S
76WHITE WATCHKing GAdair RCameron JSpeight CMacTaggart J
77GREEN WATCHTeevan RCox MCowan LBeattie SMcLean S
78BLUE WATCHPirie BMcLellan JStewart GMacKenzie CCobbett O
79Nelson SCarr LHutchison MCameronBruce J
80Hinks NWilson AInnes AOvenstone JDickson C
81Webster PMarsh KDawson DJames AMcGhee M
82Corson LPickle CHeaver DNimmo GMcDonald J
83Hughes AWands R
84WHITEGREENAMBERBLUERED
85Cameron OGallacher CBeattie PAdams JEdgar C
86Wann SEdwards GStewart GReid SPaul A
87Adair RCameron JSpeight CMacTaggart JKing G
88Cox MCowan LBeattie SMcLean STeevan R
89McLellan JStewart GMacKenzie CCobbett OPirie B
90Carr LHutchison MCameronBruce JNelson S
91Wilson AInnes AOvenstone JDickson CHinks N
92Marsh KDawson DJames AMcGhee MWebster P
93Pickle CHeaver DNimmo GMcDonald JCorson L
94Wands RHughes A
95GREENAMBERBLUEREDWHITE
96Gallacher CBeattie PAdams JEdgar CCameron O
97Edwards GStewart GReid SPaul AWann S
98Cameron JSpeight CMacTaggart JKing GAdair R
99Cowan LBeattie SMcLean STeevan RCox M
100Stewart GMacKenzie CCobbett OPirie BMcLellan J
101Hutchison MCameronBruce JNelson SCarr L
102Innes AOvenstone JDickson CHinks NWilson A
103Dawson DJames AMcGhee MWebster PMarsh K
104Heaver DNimmo GMcDonald JCorson LPickle C
105Wands RHughes A
106AMBERBLUEREDWHITEGREEN
107Beattie PAdams JEdgar CCameron OGallacher C
108Stewart GReid SPaul AWann SEdwards G
109Speight CMacTaggart JKing GAdair RCameron J
110Beattie SMcLean STeevan RCox MCowan L
111MacKenzie CCobbett OPirie BMcLellan JStewart G
112CameronBruce JNelson SCarr LHutchison M
113Ovenstone JDickson CHinks NWilson AInnes A
114James AMcGhee MWebster PMarsh KDawson D
115Nimmo GMcDonald JCorson LPickle CHeaver D
116Wands RHughes A
FORMULA SHEET
Cell Formulas
RangeFormula
A4A4=INDEX($E$70:$I$125,ROWS(A$4:A60),$A$3)
O9:O18O9=SUM(--EXACT(N9, H4HM!$B$7:$B$106))
P9:P18P9=COUNTIFS(H4HM!$B$7:$B$106,"*"&'FORMULA SHEET'!N9&"*", H4HM!$I$7:$I$106,"*"&'FORMULA SHEET'!$A$62&"*")
Q9:Q18Q9=COUNTIFS(H4HM!$B$7:$B$106,"*"&'FORMULA SHEET'!N9&"*", H4HM!$I$7:$I$106,"*"&'FORMULA SHEET'!$A$63&"*")
O19:Q19O19=SUM(O9:O18)
E10E10=INDEX(D12:H20,MATCH(MIN(E12:E20),E12:E20,0),1)
F10F10=INDEX(D14:F20,MATCH(MIN(F14:F20),F14:F20,0),1)
G10G10=INDEX(D12:H13,MATCH(MIN(G12:G13),G12:G13,0),1)
H10H10=INDEX(D14:H16,MATCH(MIN(H14:H16),H14:H16,0),1)
I11,K11I11=TODAY()
J11,L11J11=INDEX(I12:J18,MATCH(MIN(J12:J18),J12:J18,0),1)
G12:G13G12=E12
F14,F17:F20F14=E14
H14:H16H14=E14
Q22:Q31Q22=O22-P22
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E12:E20Other TypeColor scaleNO
E12:E20Other TypeColor scaleNO
G12:G13Other TypeColor scaleNO
D10Expression=TODAY<10textNO
Q9:Q18Cell Value>0textNO
P9:R18Cell Value>0textNO
L11Expression=TODAY<10textNO
J11Expression=TODAY<10textNO
F12:F20Other TypeColor scaleNO
H14:H17Other TypeColor scaleNO
E11:H11,F12:H20Other TypeColor scaleNO
E11:H11,F12:H20Other TypeColor scaleNO
J12:J18Other TypeColor scaleNO
J12:J18Cell Value=$L$43textNO
L12:L18Other TypeColor scaleNO
L12:L18Other TypeColor scaleNO
L12:L18Other TypeColor scaleNO
L12:L18Cell Value=$L$43textNO
Cells with Data Validation
CellAllowCriteria
A3List=$A$69:$A$73
 

Sp8y8

New Member
Joined
Mar 28, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=INDEX($E$61:$I$70,ROWS(A$4:A4),MATCH($A$3,$E$60:$I$60,0))
 
Solution

Sp8y8

New Member
Joined
Mar 28, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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
Excel Formula:
=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!
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,313
Members
425,272
Latest member
Umba

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