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

alexllap

New Member
Joined
Jan 29, 2014
Messages
11
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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,911
With your subject sheets as follows:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Course</td><td style=";">Area</td><td style=";">AUT</td><td style=";">WIN</td><td style=";">SPR</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">100</td><td style=";">BIOL</td><td style=";">Liz</td><td style=";">Liz</td><td style=";">Liz</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">110</td><td style=";">BIOL</td><td style=";">Jenna</td><td style="text-align: right;;"></td><td style=";">Tracey</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Biol</p><br /><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Course</td><td style=";">Area</td><td style=";">AUT</td><td style=";">WIN</td><td style=";">SPR</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">101</td><td style=";">CHEM</td><td style=";">Pete</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">105</td><td style=";">CHEM</td><td style="text-align: right;;"></td><td style=";">Pete</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Chem</p><br /><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Course</td><td style=";">Area</td><td style=";">AUT</td><td style=";">WIN</td><td style=";">SPR</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">146</td><td style=";">MATH</td><td style=";">Jack</td><td style=";">Kenneth</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">147</td><td style=";">MATH</td><td style="text-align: right;;"></td><td style=";">Jack</td><td style=";">Kenneth</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Math</p><br /><br />

You can use the following formula on your Main sheet:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Course</td><td style=";">Area</td><td style=";">AUT</td><td style=";">WIN</td><td style=";">SPR</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">100</td><td style=";">BIOL</td><td style=";">Liz</td><td style=";">Liz</td><td style=";">Liz</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">101</td><td style=";">CHEM</td><td style=";">Pete</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">105</td><td style=";">CHEM</td><td style=";"></td><td style=";">Pete</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">110</td><td style=";">BIOL</td><td style=";">Jenna</td><td style=";"></td><td style=";">Tracey</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">146</td><td style=";">MATH</td><td style=";">Jack</td><td style=";">Kenneth</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">147</td><td style=";">MATH</td><td style=";"></td><td style=";">Jack</td><td style=";">Kenneth</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Main</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">INDIRECT(<font color="Green">$B2&"!$C$2:$E$100"</font>),MATCH(<font color="Green">$A2,INDIRECT(<font color="Purple">$B2&"!$A$2:$A$100"</font>),0</font>),MATCH(<font color="Green">C$1,$C$1:$E$1,0</font>)</font>),""</font>)&""</td></tr></tbody></table></td></tr></table><br />

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.
 

Younis

New Member
Joined
Jun 23, 2015
Messages
4
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
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,911
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)
 

Younis

New Member
Joined
Jun 23, 2015
Messages
4
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?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,911
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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,911
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,593
Messages
5,487,751
Members
407,610
Latest member
bellakim00

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top