Collating a new sheet of info from multiple sheets

[Data Midwife]

Hi, Hoping someone can help me.

I'd like to create a worksheet that is a collation of all those entries who were 'yes' in a specific column on multiple previous pages. Is that possible?

Example: If ten midwives have a worksheet each - titled their name - with their own caseload on in a document called 'Community Caseload' could I create a sheet called 'Caesarean' that every entry on pages called 'Karen' 'Ellen' 'Lisa' etc who has a Yes in Column G titled 'Caesarean?' would automatically populate to a collated sheet so we could see a list of all women in the whole caseload who had a caesarean.

Please let me know if I haven't explained that correctly.

Thanks for your help.

 

[Marc L]

Hi,​

​

should be possible just with Excel basics like a filter or better an advanced filter but depending on how smart is the worksheets design,​

as it's easier to help with a workbook attachment with the expected result …​

 

[Toadstool]

Hi Data Midwife,

Your later version of Excel (I just have 2016) may support a simpler solution, but here's my convoluted attempt.

Here's my three sheets for Karen, Ellen and Lisa:

Data-Midwife.xlsx
	A	B
1	Customer	Caesarean
2	Ms A	No
3	Ms B	Yes
4	Ms C	Yes
5	Ms D	Yes
6	Ms E	Yes
Karen

Data-Midwife.xlsx
	A	B
1	Customer	Caesarean
2	Ms F	Yes
3	Ms G	No
4	Ms H	Yes
5	Ms J	No
6	Ms K	Yes
7
Ellen

Data-Midwife.xlsx
	A	B
1	Customer	Caesarean
2	Ms L	No
3	Ms M	No
4	Ms N	No
5	Ms P	Yes
6	Ms Q	Yes
Lisa

This requires the sheet names are entered into A2 to A99 of the Caesareans sheet. Formulae in B and C should be copied down to row 99. Formulae in E, F and G to be copied down 9999 rows or the maximum number of Customers who may have had Caesareans.

Data-Midwife.xlsx
	A	B	C	D	E	F	G
1	Midwife	No. of Caesareans	Running Total		Index	Midwife	Customer
2	Ellen	3	3		1	Ellen	Ms F
3	Lisa	2	5		1	Ellen	Ms H
4	Karen	4	9		1	Ellen	Ms K
5					2	Lisa	Ms P
6					2	Lisa	Ms Q
7					3	Karen	Ms B
8					3	Karen	Ms C
9					3	Karen	Ms D
10					3	Karen	Ms E
11
12
Caesareans

Cell Formulas
Range	Formula
B2:B12	B2	=IF(A2="","",COUNTIF(INDIRECT("'"&A2&"'!$B2:B9999"),"Yes"))
C2:C12	C2	=IF(ISTEXT(B1),B2,IF(OR(C1="",C1=SUM($B$2:$B$99)),"",C1+B2))
E2:E12	E2	=IF(E1="","",IF(ISTEXT(E1),1,IF(ROW()-ROW($E$1)>MAX($C$2:$C$99),"",IF(ROW()-ROW($E$1)>INDEX($C$2:$C$99,E1),E1+1,E1))))
F2:F12	F2	=IF(E2="","",INDEX($A$2:$A$99,E2))
G2:G12	G2	=IF(F2="","",INDEX(INDIRECT("'"&F2&"'!$a$2:$A$9999"),AGGREGATE(15,6,ROW($E$2:$E$9999)-1/(INDIRECT("'"&F2&"'!$b$2:$b$9999")="Yes"),COUNTIF($F$1:F2,F2))))

Is that the type of thing you wanted?

 

[Data Midwife]

Blimey Toadstool that's a load of work you put in to that answer - I really appreciate it. Its good to know it can be done but I think its might be too complicated for me to manage. I'll go away and hack at it with my limited ability and let you know if I have success. The way you've laid out even step is really useful.

Even if I don't manage it I'm really grateful for the time you put in to helping me.

Thank you

 

[Toadstool]

Blimey Toadstool that's a load of work you put in to that answer - I really appreciate it. Its good to know it can be done but I think its might be too complicated for me to manage. I'll go away and hack at it with my limited ability and let you know if I have success. The way you've laid out even step is really useful.

Even if I don't manage it I'm really grateful for the time you put in to helping me.

Thank you

You're welcome!