I'm working on a project that has two sheets. The first is what gets filled in manually, called "Sheet1", and looks like this:
<tbody>
</tbody>
"Sheet2" is the raw data, and will actually be hidden. It looks like this:
<tbody>
</tbody>
The process I'm working on is for a sports coach to fill out Sheet1, telling me when his team will be absent. Once that is populated, I'd like to add a third Sheet that takes everyone in Sheet2 who plays that sport, and returns their name, sport, the day the will miss, and what class they have for the periods they will miss.
Let's take the example that the football team is going to miss A Period and B Period on June 30th. Here's what I would like Sheet3 to look like:
<tbody>
</tbody>
I've got the formula below to work for Sheet3!A2, but it only works for one person. I would need it to populate more than one line, if that makes sense. Maybe I'm not using the correct functions?
=IF(ISBLANK(Sheet1!A2),"",INDEX(Sheet2!A:A,MATCH(Sheet1!A2,Sheet2!B:B,0)))
Sport | What period(s) will they miss? | What day(s) will they miss? |
Football | A Period, B Period | June 30th, 2016 |
Soccer | A Period, C Period, D Period | July 1st, 2016 |
<tbody>
</tbody>
"Sheet2" is the raw data, and will actually be hidden. It looks like this:
Name | Sport | A Period | B Period | C Period |
John Smith | Football | English | Math | History |
Matt Roberts | Soccer | Math | English | History |
<tbody>
</tbody>
The process I'm working on is for a sports coach to fill out Sheet1, telling me when his team will be absent. Once that is populated, I'd like to add a third Sheet that takes everyone in Sheet2 who plays that sport, and returns their name, sport, the day the will miss, and what class they have for the periods they will miss.
Let's take the example that the football team is going to miss A Period and B Period on June 30th. Here's what I would like Sheet3 to look like:
Name | Sport | What day(s) will they miss? | A Period | B Period | C Period |
John Smith | Football | June 30th, 2016 | English | Math | |
Clayton Clark | Football | June 30th, 2016 | Math | Science | |
Ryan Austin | Football | June 30th, 2016 | Science | History |
<tbody>
</tbody>
I've got the formula below to work for Sheet3!A2, but it only works for one person. I would need it to populate more than one line, if that makes sense. Maybe I'm not using the correct functions?
=IF(ISBLANK(Sheet1!A2),"",INDEX(Sheet2!A:A,MATCH(Sheet1!A2,Sheet2!B:B,0)))
Last edited: