PD, as usual, you've come up with a killer formula. I took different
approach after giving up on a one-formula solution:

The sheet was divided into short sections, one for each of the other sheets.
I used 2 helper columns for each day of the week in each section. In the
day entry to get the name from the source sheet, I used a formula like this:
=IF(IF(H3=0,"",OFFSET(Bacchus!$A$6,SUM(H$3:H3),0)) =0,"",IF(H3=0,"",OFFSET(Bacchus!$A$6,SUM(H$3:H3),0 )))

The two helper columns for that entry look like this, but it takes 2 rows to
get a look at how it works

H3
=IF(ISNA(MATCH("yes",Sheet1!D$7:D$46,0)),0,MATCH(" yes",Sheet1!D$7:D$46,0))
I3 had no formula, then in the next row, and continued to end of section list

H4 =IF(ISNA(MATCH("yes",INDIRECT(I4),0)),0,MATCH("yes ",INDIRECT(I4),0))
I4 ="Sheet1!" & ADDRESS(
MATCH("yes",Sheet1!D$7:D$46,0)+7+SUM(H$3:H3)-(H$3),4) & ":D$46"

The names were listed on each sheet starting with A7 and continuing to A46
and days of the week began at column D over to J.

Seems to work also.
"PapaDos" wrote:

Just for the fun of it, assuming your sheets are named "Week 1" to "Week 5",
enter this array formula in cell A2 of your report sheet:
{=IF((ROW()-1)<=COUNTA('Week 1'!B$2:B$6),INDEX('Week
1'!$A$1:$A$6,SMALL(('Week 1'!B$2:B$6="Yes")*ROW('Week
1'!B$2:B$6),ROW()-1+COUNTBLANK('Week 1'!B$2:B$6))),IF((ROW()-1)<=COUNTA('Week
1:Week 2'!B$2:B$6),INDEX('Week 2'!$A$1:$A$6,SMALL(('Week
2'!B$2:B$6="Yes")*ROW('Week 2'!B$2:B$6),ROW()-1-COUNTA('Week
1'!B$2:B$6)+COUNTBLANK('Week 2'!B$2:B$6))),IF((ROW()-1)<=COUNTA('Week 1:Week
3'!B$2:B$6),INDEX('Week 3'!$A$1:$A$6,SMALL(('Week 3'!B$2:B$6="Yes")*ROW('Week
3'!B$2:B$6),ROW()-1-COUNTA('Week 1:Week 2'!B$2:B$6)+COUNTBLANK('Week
3'!B$2:B$6))),IF((ROW()-1)<=COUNTA('Week 1:Week 4'!B$2:B$6),INDEX('Week
4'!$A$1:$A$6,SMALL(('Week 4'!B$2:B$6="Yes")*ROW('Week
4'!B$2:B$6),ROW()-1-COUNTA('Week 1:Week 3'!B$2:B$6)+COUNTBLANK('Week
4'!B$2:B$6))),IF((ROW()-1)<=COUNTA('Week 1:Week 5'!B$2:B$6),INDEX('Week
5'!$A$1:$A$6,SMALL(('Week 5'!B$2:B$6="Yes")*ROW('Week
5'!B$2:B$6),ROW()-1-COUNTA('Week 1:Week 4'!B$2:B$6)+COUNTBLANK('Week
5'!B$2:B$6))),"")))))}

Fill right and down as needed.

I know, it is pretty ugly... LOL
--
Festina Lente


" wrote:

Hi all,

I'm trying to generate a report and am having trouble with the formula.

Any help would be greatly appreciated.


Here is what I need to be able to do.


I have 6 worksheets in the same workbook, worksheets 1 - 5 are
identically laid out and the sixth is the one I want to generate a
report on. Column A is for peoples names, Columns B-H are labelled
Monday - Sunday on top. If the person in A2 is working on Monday then I

would type "Yes" into B2 and so on for the rest of the week and for the

rest of the rows also. It is the case that not everyone will work
everyday, therefore I want the report page to be a kind of roster
report detailing who is working on what days. The report page is laid
out as follows. Weekdays in A1-G1. What I want to do is have worksheets

1-5 checked and for instance bring back everyone's name who is
working on Monday to worksheet 6 A2-XX (XX being whatever the last
individuals name working that day is) I would ideally like the names to

be sequential as there are 40 people that could potentially be working
but only 15-20 may work on the Monday (I don't want the report to be
spread over 41 rows if possible)


I hope I have explained this well enough. Any help you might be able to

give at all would really be appreciated! I've started pulling my hair
out trying it at this stage : -)


Thanks again!


Bill