+ Reply to Thread
Results 1 to 7 of 7

Populate some of the student data from the main table to each of the student tables by Stu

  1. #1
    Registered User
    Join Date
    08-30-2019
    Location
    NYC
    MS-Off Ver
    2013
    Posts
    4

    Populate some of the student data from the main table to each of the student tables by Stu

    Hello,

    In an Excel workbook, I have a tab/worksheet with a main table of all students and corresponding tabs/worksheets of smaller tables for each student. I'm trying to populate some of the student data from the main table to each of the student tables by Student ID. I'm tried all kinds of INDEX/MATCH combos and learned about the SMALL function with IF and to add the Arrays "{}". However, I keep getting all types of errors and when I created a formula that worked, it repeated the same 1st row value of the Student ID or skipped the 1st of the Student ID. Please help. Below are attach examples.

    All Student Activity Listing.jpg

    Abioye Adeboyejo.jpg

    Addie J Stevenson.jpg
    Last edited by Pepe Le Mokko; 08-30-2019 at 11:14 AM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,449

    Re: Formula Frustration with INDEX/MATCH/ROW/SMALL and its Arrays

    Hi rjennings1 and welcome to the forum,

    Instead of using formulas, have you tried to use the Advanced Filter tool on the Data Tab. Start on a Student page and create the Advanced filter from that sheet. In the data selection go back to your "All Students" tab. Do the "Copy To:" as "A4" on the Student sheet and click OK. No formula needed...

    NOW - If you create a Dynamic Named Range for your data on the All Student Sheet, the range of all students will grow and your Advanced Filter won't need to change.

    If you add students and then REFRESH all your advanced filters it should work correctly. I hope this helps..

    https://www.excel-easy.com/examples/...med-range.html
    https://support.office.com/en-us/art...8-3f16abdff32b
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,130

    Re: Populate some of the student data from the main table to each of the student tables by

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    I changed it for you this time, just using one of your own sentences...

  4. #4
    Registered User
    Join Date
    08-30-2019
    Location
    NYC
    MS-Off Ver
    2013
    Posts
    4

    Re: Formula Frustration with INDEX/MATCH/ROW/SMALL and its Arrays

    Hi,
    Thanks for responding quickly. I've successfully created the Dynamic Names Ranges, but is this Advance Filter settings correct?

    Attachment 639535

  5. #5
    Registered User
    Join Date
    08-30-2019
    Location
    NYC
    MS-Off Ver
    2013
    Posts
    4

    Re: Populate some of the student data from the main table to each of the student tables by

    Thanks for alerting me to this.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,449

    Re: Populate some of the student data from the main table to each of the student tables by

    Hi rjennings,

    See if the attached example workbook makes sense to you.

    If you can make it work by hand then we can put some VBA behind the worksheets to do the Advanced Filter each time you click on a student worksheet.

    Advanced Filter with DNR for Student Sheets.xlsx

  7. #7
    Registered User
    Join Date
    08-30-2019
    Location
    NYC
    MS-Off Ver
    2013
    Posts
    4

    Re: Populate some of the student data from the main table to each of the student tables by

    Hi MarvinP,

    Thanks for the spreadsheet. I figured out what I was doing wrong in the Advanced Filter Ė not including all the column headers in the criteria and range. Also, the VBA is a great idea. Since I know how to record macros, I was able to add Advanced Filter VBAs easily. Thanks again for all your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Incorporating SMALL into a INDEX and MATCH Formula
    By Scola16 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-04-2018, 04:15 PM
  2. [SOLVED] using index match small formula not working
    By jpbisani in forum Excel General
    Replies: 4
    Last Post: 04-25-2018, 07:13 AM
  3. [SOLVED] formula to extract data using small , index and match functions
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-22-2017, 02:07 PM
  4. [SOLVED] Index, Match, Row, Small functions are properly not set in my formula
    By rajeshn_in in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-09-2016, 04:24 AM
  5. Excel Formula: IFERROR, INDEX, SMALL, MATCH
    By Jenn12788 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2014, 10:03 PM
  6. [SOLVED] Confusion and frustration with complex formula involving ROW, INDEX & SMALL
    By JC_LA_1979 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-29-2014, 09:46 AM
  7. Large, Small, Index, Match formula combos
    By taudano in forum Excel General
    Replies: 2
    Last Post: 06-30-2011, 01:53 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1