Formula to pull in name based on criteria from one worksheet to another

[LailaT]

Hello

I would like to pull in the fields "Name" and "Comments" from one worksheet into another based on the criteria "where Active = X").

So John Smith and his comments would get pulled into the new worksheet.

Thank you

Name	Comments	Active
John Smith	lorem ipsum	X
Sam Smith	kajdlkajdlkfjlkdjlak

<tbody>
</tbody>

 

[Scott T]

Try

Book1
	A	B	C
1	Name	Comments	Active
2	John Smith	lorem ipsum	X
3	name 5	coment7
4	Beth Bop	some coment	x
5	Rick Gip	coments here
6	Sam Smith	kajdlkajdlkfjlkdjlak
Sheet1

Book1
	A	B	C
1	Name	Comments	Active
2	John Smith	lorem ipsum	X
3	Beth Bop	some coment	x
4
5
6
7
8
9
Sheet2

Cell Formulas
Range	Formula
A2	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")C$2:C2),"",INDEX(Sheet1!A$2:A$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A2))))}
A3	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")C$2:C3),"",INDEX(Sheet1!A$2:A$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A3))))}
A4	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")C$2:C4),"",INDEX(Sheet1!A$2:A$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A4))))}
A5	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")C$2:C5),"",INDEX(Sheet1!A$2:A$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A5))))}
A6	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")C$2:C6),"",INDEX(Sheet1!A$2:A$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A6))))}
A7	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")C$2:C7),"",INDEX(Sheet1!A$2:A$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A7))))}
A8	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")C$2:C8),"",INDEX(Sheet1!A$2:A$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A8))))}
A9	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")C$2:C9),"",INDEX(Sheet1!A$2:A$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A9))))}
B2	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")D$2:D2),"",INDEX(Sheet1!B$2:B$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B2))))}
B3	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")D$2:D3),"",INDEX(Sheet1!B$2:B$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B3))))}
B4	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")D$2:D4),"",INDEX(Sheet1!B$2:B$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B4))))}
B5	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")D$2:D5),"",INDEX(Sheet1!B$2:B$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B5))))}
B6	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")D$2:D6),"",INDEX(Sheet1!B$2:B$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B6))))}
B7	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")D$2:D7),"",INDEX(Sheet1!B$2:B$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B7))))}
B8	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")D$2:D8),"",INDEX(Sheet1!B$2:B$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B8))))}
B9	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")D$2:D9),"",INDEX(Sheet1!B$2:B$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B9))))}
C2	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")E$2:E2),"",INDEX(Sheet1!C$2:C$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(C$2:C2))))}
C3	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")E$2:E3),"",INDEX(Sheet1!C$2:C$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(C$2:C3))))}
C4	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")E$2:E4),"",INDEX(Sheet1!C$2:C$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(C$2:C4))))}
C5	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")E$2:E5),"",INDEX(Sheet1!C$2:C$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(C$2:C5))))}
C6	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")E$2:E6),"",INDEX(Sheet1!C$2:C$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(C$2:C6))))}
C7	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")E$2:E7),"",INDEX(Sheet1!C$2:C$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(C$2:C7))))}
C8	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")E$2:E8),"",INDEX(Sheet1!C$2:C$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(C$2:C8))))}
C9	{=IF(COUNTIF(Sheet1!$C$2:$C$6,"x")E$2:E9),"",INDEX(Sheet1!C$2:C$6,SMALL(IF(Sheet1!$C$2:$C$6="x",ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS(C$2:C9))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[LailaT]

Hi Scott

Thanks for the info.
So I have to put a separate formula for each row? I
s there a way to put one formula that covers the entire spreadsheet, which is approx 300 rows at the moment?

 

[Scott T]

You can copy the formula down. it is the same formula you just need to make sure you copy it down far enough to return all the results. The formula will display blank if you drag it down pass where you have a result.

 

[LailaT]

Thank you!
It works!

 

[LailaT]

Detailed:

Date	Name	Comments	Active	Appointment
09/04/2019	Matt Otway	Comment 1	X
10/04/2019	Andrew M	Comment 2		X
11/04/2019	Peter Shields	Comment 3
11/05/2019	Kate Yardy	Comment 4

<tbody>
</tbody>

Snapshot:

Name	Comments
Peter Shields	Comment 3
Kate Yardy	Comment 4

<tbody>
</tbody>

Would you be able to help me again?

I need a formula to pull in the fields Name and Comments only based on the criteria of specific date range (11/04/2019 - 11/05/2019) as well as the fields Active and Appointment being blank.

 

[Fazza]

I see you've asked for a formula solution. For your information, there are non-formula solutions. Such as parameterised queries.
Here is one example, [URL]http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/[/url]
Suggest you google for more if interested. regards

 

[Scott T]

Book1
	A	B	C	D	E
1	Date	Name	Comments	Active	Appointment
2	9/4/2019	Matt Otway	Comment 1	X
3	10/4/2019	Andrew M	Comment 2		X
4	11/4/2019	Peter Shields	Comment 3
5	11/5/2019	Kate Yardy	Comment 4
6
7	Start date	End date
8	11/4/2019	11/5/2019
9
10	Name	Comments
11	Peter Shields	Comment 3
12	Kate Yardy	Comment 4
Sheet1

Cell Formulas
Range	Formula
A11	{=IF(ROWS($A$11:A11)>SUM(($A$2:$A$5>=$A$8)*($A$2:$A$5<=$B$8)*ISBLANK($D$2:$D$5)*ISBLANK($E$2:$E$5)),"",INDEX(B$2:B$5,SMALL(IF(($A$2:$A$5>=$A$8)*($A$2:$A$5<=$B$8)*ISBLANK($D$2:$D$5)*ISBLANK($E$2:$E$5),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$11:A11))))}
A12	{=IF(ROWS($A$11:A12)>SUM(($A$2:$A$5>=$A$8)*($A$2:$A$5<=$B$8)*ISBLANK($D$2:$D$5)*ISBLANK($E$2:$E$5)),"",INDEX(B$2:B$5,SMALL(IF(($A$2:$A$5>=$A$8)*($A$2:$A$5<=$B$8)*ISBLANK($D$2:$D$5)*ISBLANK($E$2:$E$5),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$11:A12))))}
B11	{=IF(ROWS($A$11:B11)>SUM(($A$2:$A$5>=$A$8)*($A$2:$A$5<=$B$8)*ISBLANK($D$2:$D$5)*ISBLANK($E$2:$E$5)),"",INDEX(C$2:C$5,SMALL(IF(($A$2:$A$5>=$A$8)*($A$2:$A$5<=$B$8)*ISBLANK($D$2:$D$5)*ISBLANK($E$2:$E$5),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$11:B11))))}
B12	{=IF(ROWS($A$11:B12)>SUM(($A$2:$A$5>=$A$8)*($A$2:$A$5<=$B$8)*ISBLANK($D$2:$D$5)*ISBLANK($E$2:$E$5)),"",INDEX(C$2:C$5,SMALL(IF(($A$2:$A$5>=$A$8)*($A$2:$A$5<=$B$8)*ISBLANK($D$2:$D$5)*ISBLANK($E$2:$E$5),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$11:B12))))}
Press CTRL+SHIFT+ENTER to enter array formulas.