Help Please - if range contains text then copy a different cell to another sheet

[A_N_I_T_A]

First time poster here,

I need a bit of help...
Basically I have a list of which days children attend and different teachers are responsible for each child's documentation so I want to make sure that each teacher has the correct number of children to report on.

What I want to do is if in Sheet 1 (attendance) column H3 to H19 contains the text "Teacher 1" then copy the text (child's name) from Sheet 1 B3:19 to Sheet 2 under the correct teacher. If it contains teacher 2 then copy that child's name to the teacher 2 list in Sheet 2.

I hope that makes sense ... any suggestions appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. It will generally get you faster and better responses.

See if this does what you want.

My sample data

A_N_I_T_A.xlsm
	A	B	C	D	E	F	G	H
1
2
3		Child 1						Teacher 2
4		Child 2						Teacher 1
5		Child 3						Teacher 2
6		Child 4						Teacher 3
7		Child 5						Teacher 3
8		Child 6						Teacher 2
9		Child 7						Teacher 2
10		Child 8						Teacher 1
11		Child 9						Teacher 3
12		Child 10						Teacher 2
13		Child 11						Teacher 2
14		Child 12						Teacher 1
15		Child 13						Teacher 4
16		Child 14						Teacher 3
17		Child 15						Teacher 3
18		Child 16						Teacher 3
19		Child 17						Teacher 4
Attendance

Copy the B3 formula across and the other results should automatically 'spill' down the columns.

A_N_I_T_A.xlsm
	A	B	C	D	E
1
2		Teacher 1	Teacher 2	Teacher 3	Teacher 4
3		Child 2	Child 1	Child 4	Child 13
4		Child 8	Child 3	Child 5	Child 17
5		Child 12	Child 6	Child 9
6			Child 7	Child 14
7			Child 10	Child 15
8			Child 11	Child 16
9
Documentation

Cell Formulas
Range	Formula
B3:B5,E3:E4,C3:D8	B3	=FILTER(Attendance!$B$3:$B$19,Attendance!$H$3:$H$19=B$2,"")
Dynamic array formulas.

 

[A_N_I_T_A]

THANK YOU SO MUCH!!!!

Thanks Peter, I'll have to look in to how that formula works but for now i'm going to copy and paste it and use it because it works (and I don't really need to understand how right now ).
I'll also look in to getting XL2BB for future questions.

-Anita

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.