+ Reply to Thread
Results 1 to 7 of 7

Auto populating cells with corresponding data based on Drop-down list.

  1. 07-27-2015, 05:54 PM #1
    Colortoned
    Colortoned is offline
    Registered User
    Join Date
    07-27-2015
    Location
    California, USA
    MS-Off Ver
    MS Office for MAC 2011
    Posts
    3

    Auto populating cells with corresponding data based on Drop-down list.

    Hi Community,

    I'm a teacher at a small Charter school in California with about 100 students. I'm trying to create an excel sheet that is able to not only track and calculate their attendance (which I already have in place), but the harder part is to be able to create a printout with corresponding data. I need some assistance on how to do that last part.

    So (on the "Printout" sheet) if Student 1 is selected in the drop down list, how do I pull specific data from the raw data sheet for that student into the necessary cell(s)?

    I'll take any help you're willing to offer. Thank you everyone. I've attached the workbook.Attachment 410102
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-27-2015, 07:20 PM #2
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto populating cells with corresponding data based on Drop-down list.

    Given AA:AI, can you help map which number would go into which Cell on the front page?

    So far I have A19:
    Please Login or Register  to view this content.
    B19:
    Please Login or Register  to view this content.
    D19:
    Please Login or Register  to view this content.
    And some array formulas for row 26, starting with A26:

    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!
    Reply With QuoteRegister To Reply
  3. 07-28-2015, 05:58 PM #3
    Colortoned
    Colortoned is offline
    Registered User
    Join Date
    07-27-2015
    Location
    California, USA
    MS-Off Ver
    MS Office for MAC 2011
    Posts
    3

    Re: Auto populating cells with corresponding data based on Drop-down list.

    Quote Originally Posted by daffodil11 View Post
    Given AA:AI, can you help map which number would go into which Cell on the front page?
    Thank you so much daffodil! The A19:D19 formulas work perfectly. For the array formulas, I'm not quite sure how it supposed to work. I think I figured out that it goes in A:23 and it matches with the selected "Week" and calculates how many cells in that "Week" are populated, so I named the rest of the "Weeks." It pumped out correct possible days for each "Week" as long as the cells in that week were populated with something.

    For AA:AI and correlating columns per "week", based off of selected week,

    CmlvSem1 Printout
    AA would = A23
    AB would = B23
    AD would = D23
    AE would = C23

    AI would = B27
    AI of previous Week = A27 (except in AI's case there is no previous week).

    Hope this makes sense. I've attached an updated form.
    Attached Files Attached Files
    Last edited by Colortoned; 07-28-2015 at 06:04 PM.
    Reply With QuoteRegister To Reply
  4. 07-28-2015, 07:48 PM #4
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto populating cells with corresponding data based on Drop-down list.

    Quote Originally Posted by Colortoned View Post
    CmlvSem1 Printout
    AA would = A23
    AB would = B23
    AD would = D23
    AE would = C23

    AI would = B27
    AI of previous Week = A27 (except in AI's case there is no previous week).
    That's exactly what I was looking for, something to let me where the answers are that I need to reach.
    I'll bend =INDEX(CmlvSem1!$B$4:$NJ$34,MATCH(B14,CmlvSem1!$A$4:$A$34,0),MAX(COLUMN(INDIRECT(SUBSTITUTE(B21," ",""))))) to meet your needs in the morning.
    Reply With QuoteRegister To Reply
  5. 07-29-2015, 11:50 AM #5
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto populating cells with corresponding data based on Drop-down list.

    And done!

    A22:
    Please Login or Register  to view this content.
    B22:
    Please Login or Register  to view this content.
    C22:
    Please Login or Register  to view this content.
    D22:
    Please Login or Register  to view this content.
    A27:
    Please Login or Register  to view this content.
    B27:
    Please Login or Register  to view this content.
    These are all array formulas.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 07-29-2015, 03:03 PM #6
    Colortoned
    Colortoned is offline
    Registered User
    Join Date
    07-27-2015
    Location
    California, USA
    MS-Off Ver
    MS Office for MAC 2011
    Posts
    3

    Re: Auto populating cells with corresponding data based on Drop-down list.

    Excellent!!!! It's gorgeous! Exactly what I was looking for! Thank you so much daffodil! For your time and help! You've just made the lives of the registrars and teachers here at our school 20x easier and efficient.
    Reply With QuoteRegister To Reply
  7. 07-29-2015, 04:15 PM #7
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto populating cells with corresponding data based on Drop-down list.

    Happy times for everyone! Come back if you need more helps!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Auto-update cells based on a cell drop-down list and a table
    By Castillb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-27-2015, 07:19 PM
  2. Auto populating cell based on data in 2 other cells
    By CCrumpler in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2014, 10:40 AM
  3. auto populating based on a drop down selection
    By a_sherriff in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-04-2013, 04:43 PM
  4. Auto populating data in Excel after selecting from a Drop Down list?
    By sadkha in forum Excel General
    Replies: 8
    Last Post: 08-07-2013, 12:04 PM
  5. Excel. Drop-down list and auto-populating another cell.
    By [email protected] in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2013, 10:45 PM
  6. Populating feilds based off drop down list.
    By hooligansholiday in forum Excel General
    Replies: 3
    Last Post: 06-18-2012, 12:54 PM
  7. Auto complete cells after choosing data in drop down list
    By leonardsway in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-27-2008, 03:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1