Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 07-16-2017, 01:53 PM
cjoyce73 cjoyce73 is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jul 2017
Posts: 3
cjoyce73 is on a distinguished road
Default Drop down lists and pulling data from worksheet based on drop down selection


I am in need of some assistance. I am trying to implement a new staff directory for Special Education staff assigned to schools for the very large school district I am employed by. I have created a data worksheet (Master) with all 180 schools and up to 13 staff members in various roles at all schools ranging from cells B2 to O183. I have created a drop down list on the primary worksheet (SESStaff) along with fields that I need to pull staff names from and populate from the (Master) data set and put into those fields for administrators to view based on their drop down selection. I know this is MUCH easier in Access, but the system requires me to do this in Excel. Any help is MUCH appreciated!
Reply With Quote
  #2  
Old 07-16-2017, 07:58 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 397
NoSparks is on a distinguished road
Default

Quote:
I am in need of some assistance............ the system requires me to do this in Excel. Any help is MUCH appreciated!
Any chance of you attaching a dummied-up sample workbook so we actually have something to help with ?
Reply With Quote
  #3  
Old 07-17-2017, 04:44 AM
cjoyce73 cjoyce73 is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jul 2017
Posts: 3
cjoyce73 is on a distinguished road
Default Sample attached

Certainly, I did not think of doing that, new to the help forum!

The attached screen shots are the two worksheets in the spreadsheet I am creating.

In 'frontend' you will see a drop down list and when a school site is selected I need it to pull data from the other worksheet 'Master' and populate the cells marked with the red arrows under the staff position title for that school.

Thanks again for any suggestions or ideas!
Attached Images
File Type: png frontend.PNG (17.8 KB, 9 views)
File Type: png master.PNG (14.9 KB, 9 views)
Reply With Quote
  #4  
Old 07-17-2017, 04:52 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,914
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Hi
it's best to attach a workbook/sheet so that we can work on it. (screen shots do not help much)
Thanks
Reply With Quote
  #5  
Old 07-17-2017, 04:57 AM
NBVC's Avatar
NBVC NBVC is offline Windows 10 Office 2013
A Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 121
NBVC is on a distinguished road
Default

You can use INDEX/MATCH formulation:

e.g.

In A8 of your front end sheet, enter formula:

=INDEX('Master'!$A:$F,MATCH($A$5,'Master'!$A:$A,0) ,MATCH(A7,'Master'!$A$1:$F$1,0))


You may have to replace the F column references to suit your Master sheet as we can't see from your images how far the sheet extends.

for the other cells in the frontend sheet, you just need to change the A7 reference to suit the profession you are looking for in row 1 of Master sheet.
Reply With Quote
  #6  
Old 07-17-2017, 07:40 AM
cjoyce73 cjoyce73 is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jul 2017
Posts: 3
cjoyce73 is on a distinguished road
Default It worked!!

NBVC, that formula you provided worked perfectly, thank you so much for the assistance with this issue!
Reply With Quote
Reply

Tags
drop down list, extracting data

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to make sections hidden/appear based on selection in Cont. Ctrl. Drop-down list Irimi-Ai Word 5 04-25-2017 09:31 AM
Drop-down menu in Excel, filling up the lists according to selection blackarrow Excel 18 01-18-2017 08:04 AM
Hide/Unhide Text Based on Drop Down Selection gw1500se Word 3 02-19-2015 12:17 PM
Dynamically changing drop-down list based on selection? (Word Form) laurarem Word 1 02-21-2013 10:17 PM
Ho to perform multi selection in drop down lists? nashville Word 0 09-29-2010 07:10 AM


All times are GMT -7. The time now is 08:33 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft