Aggregate data from multiple sheets and pull based on value in row and column headers
Results 1 to 10 of 10

Thread: Aggregate data from multiple sheets and pull based on value in row and column headers

  1. #1
    New Member
    Join Date
    Jan 2014
    Location
    Washington
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Aggregate data from multiple sheets and pull based on value in row and column headers

    I'm trying to help out a professor who is in charge of keeping track of teaching assignments. He's using a spreadsheet with a sheet that lists all courses and separate sheets depending on the subject. But sometimes he may forget to update the main sheet when a change is made to a particular subject. The main sheet lists each course number on it's own row, and in the next column, the subject. The next three column headers are for the term (AUT, WIN, SPR), and if a particular course is scheduled to teach, there's an instructor name listed. There are then individual sheets for each subject, all having the same column headers as the main sheet, but with only courses in that subject. I've named each table and their respective sheet by the subject, e.g., there's a "BIOL" sheet with the table named "BIOL".

    I'm hoping to create a template for him to use so that when a change is made on the subject sheets, the main sheet will automatically update. Here's a visual:




    I've tried using a combination of INDEX, MATCH, and INDIRECT, but can't seem to figure it out. I know I could use a VLOOKUP/INDIRECT combination, but I'm trying to better understand INDEX and MATCH, and all the youtube videos I've watched haven't seemed to help. Any help is appreciated.

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,345
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Aggregate data from multiple sheets and pull based on value in row and column headers

    With your subject sheets as follows:

    ABCDE
    1CourseAreaAUTWINSPR
    2100BIOLLizLizLiz
    3110BIOLJennaTracey

    Biol





    ABCDE
    1CourseAreaAUTWINSPR
    2101CHEMPete
    3105CHEMPete

    Chem





    ABCDE
    1CourseAreaAUTWINSPR
    2146MATHJackKenneth
    3147MATHJackKenneth

    Math





    You can use the following formula on your Main sheet:

    ABCDE
    1CourseAreaAUTWINSPR
    2100BIOLLizLizLiz
    3101CHEMPete
    4105CHEMPete
    5110BIOLJennaTracey
    6146MATHJackKenneth
    7147MATHJackKenneth

    Main



    Worksheet Formulas
    CellFormula
    C2=IFERROR(INDEX(INDIRECT($B2&"!$C$2:$E$100"),MATCH($A2,INDIRECT($B2&"!$A$2:$A$100"),0),MATCH(C$1,$C$1:$E$1,0)),"")&""



    As you mentioned, the VLOOKUP might be slightly easier since you don't have to do an extra MATCH. Let me know if you have any questions.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Jan 2014
    Location
    Washington
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Aggregate data from multiple sheets and pull based on value in row and column headers

    That worked beautifully. Thank you!

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,345
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Aggregate data from multiple sheets and pull based on value in row and column headers

    You're welcome!

  5. #5
    New Member
    Join Date
    Jun 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Aggregate data from multiple sheets and pull based on value in row and column headers

    I am trying to automate consolidating data from different sheets into a master sheet. The source sheets have same columns size and names but vary in the number of rows. The source sheets also has space between the rows. I am currently using the below formula with indirect function but when I copy the formula to the next row in the master sheet I get the same result as the range within the formula wouldn't change.

    =INDEX(INDIRECT("RCSA_"&$E36&"_"&$F36&"!$C$25:$CV$502"),AGGREGATE(15,6,(ROW(INDIRECT("RCSA_"&$E36&"_"&$F36&"!$AM$25:$AM$502"))-ROW(INDIRECT("RCSA_"&$E36&"_"&$F36&"!$AM$25"))+1)/(INDIRECT("RCSA_"&$E36&"_"&$F36&"!$AM$25:$AM$502")<>""),ROWS(INDIRECT("RCSA_"&$E36&"_"&$F36&"!AM$25:AM25"))),MATCH('Consolidated RCSAs'!I$35,INDIRECT("RCSA_"&$E36&"_"&$F36&"!$C$22:$CV$22"),0))

    I have two questions:
    1-Is there a better way to automate extraction of data from several sheets into one without using VBA or the above formula
    2-What can I do to fix the fixed range problem

    Thanks

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,345
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Aggregate data from multiple sheets and pull based on value in row and column headers

    Quote Originally Posted by Younis View Post
    I am trying to automate consolidating data from different sheets into a master sheet. The source sheets have same columns size and names but vary in the number of rows. The source sheets also has space between the rows. I am currently using the below formula with indirect function but when I copy the formula to the next row in the master sheet I get the same result as the range within the formula wouldn't change.

    =INDEX(INDIRECT("RCSA_"&$E36&"_"&$F36&"!$C$25:$CV$502"),AGGREGATE(15,6,(ROW(INDIRECT("RCSA_"&$E36&"_"&$F36&"!$AM$25:$AM$502"))-ROW(INDIRECT("RCSA_"&$E36&"_"&$F36&"!$AM$25"))+1)/(INDIRECT("RCSA_"&$E36&"_"&$F36&"!$AM$25:$AM$502")<>""),ROWS(INDIRECT("RCSA_"&$E36&"_"&$F36&"!AM$25:AM25"))),MATCH('Consolidated RCSAs'!I$35,INDIRECT("RCSA_"&$E36&"_"&$F36&"!$C$22:$CV$22"),0))

    I have two questions:
    1-Is there a better way to automate extraction of data from several sheets into one without using VBA or the above formula
    2-What can I do to fix the fixed range problem

    Thanks
    1) Possibly Power Query, but I don't know enough about that to advise you.
    2) The ROWS part of your formula determines which element from the array to choose, and as you said, since it's a string in an INDIRECT, it's fixed and will always give the same value. Instead, use the range from the master sheet where the formula is entered. For example, if your formula is in F5, change the part in red to

    ROWS($F$5:$F5)
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    New Member
    Join Date
    Jun 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Aggregate data from multiple sheets and pull based on value in row and column headers

    Thanks for this solution. This could work but by the time the formula finishes copying it from the first sheet it should jump to copying from the second sheet and I need to reset this counter. Do you have any suggestion for this?

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,345
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Aggregate data from multiple sheets and pull based on value in row and column headers

    That's a much trickier problem. My first thought would be to create a work column for each source sheet on your master sheet. Then it's possible to create a formula that concatenates those columns into one column. Hard to say without seeing your layout, or knowing how many sheets you have. You might want to open a new thread so that more eyes will see it, and maybe one of the resident Power Query gurus will chime in.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  9. #9
    New Member
    Join Date
    Jun 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Aggregate data from multiple sheets and pull based on value in row and column headers

    Thanks Eric, I am not sure how to open a new thread

  10. #10
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,345
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Aggregate data from multiple sheets and pull based on value in row and column headers

    To open a thread, click on Forum on the upper left, then select the particular forum you want, probably "Excel Questions", then on the left side, just below the header is a button that says: "+Post New Thread". Follow the prompts from there.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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