Collating a new sheet of info from multiple sheets

Data Midwife

New Member
Joined
Apr 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,758
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
2,433
Office Version
  1. 2016
Platform
  1. Windows
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
AB
1CustomerCaesarean
2Ms ANo
3Ms BYes
4Ms CYes
5Ms DYes
6Ms EYes
Karen

Data-Midwife.xlsx
AB
1CustomerCaesarean
2Ms FYes
3Ms GNo
4Ms HYes
5Ms JNo
6Ms KYes
7
Ellen

Data-Midwife.xlsx
AB
1CustomerCaesarean
2Ms LNo
3Ms MNo
4Ms NNo
5Ms PYes
6Ms QYes
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
ABCDEFG
1MidwifeNo. of CaesareansRunning TotalIndexMidwifeCustomer
2Ellen331EllenMs F
3Lisa251EllenMs H
4Karen491EllenMs K
5  2LisaMs P
6  2LisaMs Q
7  3KarenMs B
8  3KarenMs C
9  3KarenMs D
10  3KarenMs E
11     
12     
Caesareans
Cell Formulas
RangeFormula
B2:B12B2=IF(A2="","",COUNTIF(INDIRECT("'"&A2&"'!$B2:B9999"),"Yes"))
C2:C12C2=IF(ISTEXT(B1),B2,IF(OR(C1="",C1=SUM($B$2:$B$99)),"",C1+B2))
E2:E12E2=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:F12F2=IF(E2="","",INDEX($A$2:$A$99,E2))
G2:G12G2=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

New Member
Joined
Apr 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
2,433
Office Version
  1. 2016
Platform
  1. Windows
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!
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,611
Messages
5,765,414
Members
425,285
Latest member
andypandypoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top