# Thread: Aggregate data from multiple sheets and pull based on value in row and column headers Thanks:  1 Post #5311089 (1) Likes: 0

1. ## 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. ## 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.

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

That worked beautifully. Thank you!

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

You're welcome!

5. ## 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. ## Re: Aggregate data from multiple sheets and pull based on value in row and column headers

Originally Posted by Younis
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)

7. ## 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. ## 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.

9. ## 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. ## 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.

## User Tag List

#### Posting Permissions

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