get one week at a time

[mduntley]

Hello. At work we are using a schedule on a excel. We have one sheet that has the year. But I want to create another sheet that only has one week listed when I put in the first day of the week. This is what I mean:
Note, this is exactly what is on the sheet, beside the name and the time. I am more focusing on getting the data from a11-a40. column A will be copy and paste in, so i only need the formula for coulmn B to F

Here is the master sheet
[TABLE="class: grid, width: 327"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]staff name[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]6/7/15[/TD]
[TD]6/8/15[/TD]
[/TR]
[TR]
[TD]a3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a4[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a5[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a6[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a7[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a8[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a9[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a10[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a12[/TD]
[TD]9a-9p[/TD]
[TD]9a-9p[/TD]
[/TR]
[TR]
[TD]a13[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[/TR]
[TR]
[TD]a14[/TD]
[TD]8a-4p[/TD]
[TD]8a-4p[/TD]
[/TR]
[TR]
[TD]a15[/TD]
[TD]5p-9p[/TD]
[TD]5p-9p[/TD]
[/TR]
[TR]
[TD]a16[/TD]
[TD]5p-5a[/TD]
[TD]5p-5a[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a19[/TD]
[TD]9a-9p[/TD]
[TD]9a-9p[/TD]
[/TR]
[TR]
[TD]a20[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[/TR]
[TR]
[TD]a21[/TD]
[TD]8a-4p[/TD]
[TD]8a-4p[/TD]
[/TR]
[TR]
[TD]a22[/TD]
[TD]5p-5a[/TD]
[TD]PTO[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Group 3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a25[/TD]
[TD]6a-6p[/TD]
[TD]6p-6a[/TD]
[/TR]
[TR]
[TD]a26[/TD]
[TD]6p-6a[/TD]
[TD]6a-6p[/TD]
[/TR]
[TR]
[TD]a27[/TD]
[TD]6p-6a[/TD]
[TD]6p-6a[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Group 4[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a30[/TD]
[TD]6p-6a[/TD]
[TD]6a-6p[/TD]
[/TR]
[TR]
[TD]a31[/TD]
[TD]6a-6p[/TD]
[TD]6p-6a[/TD]
[/TR]
[TR]
[TD]a32[/TD]
[TD]6p-6a[/TD]
[TD]6p-6a[/TD]
[/TR]
[TR]
[TD]a33[/TD]
[TD]4a-4p[/TD]
[TD]4a-4p[/TD]
[/TR]
[TR]
[TD]a34[/TD]
[TD]4p-4a[/TD]
[TD]4p-4a[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Group 5[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a37[/TD]
[TD]6p-6a[/TD]
[TD]6a-6p[/TD]
[/TR]
[TR]
[TD]a38[/TD]
[TD]8a-4p[/TD]
[TD]8a-4p[/TD]
[/TR]
[TR]
[TD]a39[/TD]
[TD]6p-6a[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a40[/TD]
[TD]6A-6P[/TD]
[TD]6a-6p[/TD]
[/TR]
</tbody>[/TABLE]
So on a different sheet, if I put in 6/7/15 in b2, i want the sheet to give me the whole column 6/7/15 from the master sheet. So I can then put 6/8/15 in c2 and get the whole column in C. I will then apply this to d2 to h2. If I change b2 to 9/9/15, i want to get the column of 9/9/15.

If you dont understand, then I can explain more

 

[onlyadrafter]

Hello,

in B3 on your destination sheet, enter this formula

=INDEX(Sheet1!3:3,0,MATCH(B$2,Sheet1!$2:$2,0))

and copy down and across as far as required.

Is this of any use?

 

[mduntley]

Thank you very much