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

[alexllap]

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.

 

[Eric W]

With your subject sheets as follows:

Book1
	A	B	C	D	E
1	Course	Area	AUT	WIN	SPR
2	100	BIOL	Liz	Liz	Liz
3	110	BIOL	Jenna		Tracey
Biol

Book1
	A	B	C	D	E
1	Course	Area	AUT	WIN	SPR
2	101	CHEM	Pete
3	105	CHEM		Pete
Chem

Book1
	A	B	C	D	E
1	Course	Area	AUT	WIN	SPR
2	146	MATH	Jack	Kenneth
3	147	MATH		Jack	Kenneth
Math

You can use the following formula on your Main sheet:

Book1
	A	B	C	D	E
1	Course	Area	AUT	WIN	SPR
2	100	BIOL	Liz	Liz	Liz
3	101	CHEM	Pete
4	105	CHEM		Pete
5	110	BIOL	Jenna		Tracey
6	146	MATH	Jack	Kenneth
7	147	MATH		Jack	Kenneth
Main

Cell Formulas
Range	Formula
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.

 

[alexllap]

That worked beautifully. Thank you!

 

[Eric W]

You're welcome!

 

[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

 

[Eric W]

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]

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]

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.

 

[Younis]

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

 

[Eric W]

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.