Determine List of People at Each Location on Each Day

Spreadsheet is attached.

I have a list of 3 people (Tom, **** and Harry).

I have 3 possible sites for each of them to be on any particular day (Site1, Site2 and Site3).

So, I have a worksheet set up with a row for each day of the year; then 3 columns for Tom, **** and Harry. For each day, I put in which site they are working at. What I want to do is have 3 subsequent worksheets for each site.

In the Site1 worksheet, I would have 3 columns (Person 1, Person 2, Person 3) and a row for each day of the year. What I'd like to do is have a formula to look in the main worksheet (titled "Calendar" in the attached spreadsheet) and for each day, look to see who is listed for each site and place them in the Person 1, Person 2, and Person 3 columns, if applicable. In the attached spreadsheet, I manually put the names into the Site1, Site2 and Site3 worksheets, but I'm hoping there is a way to get this with a formula.

Thanks for the reply. When I put that into D2 of the Site1 worksheet, the cell was blank once I hit return

Thanks; that worked, but it does the opposite of what I was hoping to do.

I want to fill the Calendar worksheet, and then autofill the Site1, Site2 and Site3 worksheets using a formula.

That does work; the only thing is I want the formula to look through each row and list the person for each site.

I changed your example (attached), but notice how in the Site1 worksheet, it should have Harry as the Person 2 for January 1 and January 2, but the Person 2 column is only looking in the second column of the Calendar worksheet.

Yes, that was my mistake; I had entered those in manually and didn't change when you put the formulas into the Site1 worksheet. This is corrected now in the attached document. So, the Site2 worksheet shows how I'd like it to look, or in the case of the Site1 worksheet (with your formula), the Harry instances on January 1 and 2 should be under the Person 2 column.