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

LailaT

New Member
Joined
Apr 1, 2019
Messages
9
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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD]Comments[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]lorem ipsum[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Sam Smith[/TD]
[TD]kajdlkajdlkfjlkdjlak[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Try

Book1
ABC
1NameCommentsActive
2John Smithlorem ipsumX
3name 5coment7
4Beth Bopsome comentx
5Rick Gipcoments here
6Sam Smithkajdlkajdlkfjlkdjlak
Sheet1



Cell Formulas
RangeFormula
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Detailed:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Comments[/TD]
[TD]Active[/TD]
[TD]Appointment[/TD]
[/TR]
[TR]
[TD]09/04/2019[/TD]
[TD]Matt Otway[/TD]
[TD]Comment 1[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/04/2019[/TD]
[TD]Andrew M[/TD]
[TD]Comment 2[/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]11/04/2019[/TD]
[TD]Peter Shields[/TD]
[TD]Comment 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/05/2019[/TD]
[TD]Kate Yardy[/TD]
[TD]Comment 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Snapshot:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Comments[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter Shields[/TD]
[TD]Comment 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kate Yardy [/TD]
[TD]Comment 4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0

Book1
ABCDE
1DateNameCommentsActiveAppointment
29/4/2019Matt OtwayComment 1X
310/4/2019Andrew MComment 2X
411/4/2019Peter ShieldsComment 3
511/5/2019Kate YardyComment 4
6
7Start dateEnd date
811/4/201911/5/2019
9
10NameComments
11Peter ShieldsComment 3
12Kate YardyComment 4
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,223,996
Messages
6,175,858
Members
452,676
Latest member
woodyp

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
Back
Top