Drawing data from sheet to sheet

[Musculous]

Hello, how can I automatically add each row from Sheet1 to Sheet2 that has a certain text written in it's R column? Is there a function that allows me to do it? Thank you for help!

[#2]

@Musculous  To "write" it to sheet 2 would require a macro but to "include" it on sheet 2 would have a number of options.  You could create a pivot table or query or with the new dynamic arrays you can add a simple FILTER() statement. For example:

=FILTER('Sheet 1'!A:Q,'Sheet 1'!R:R="include this row")

without the dynamic arrays it can also be done just not as simple.

[#3]

@mtarler can it also be done in Excel 2013 without any addons?

[#4]

@Musculous Yes it can.  Without the actual sheet the formula would be something like this:

=IFERROR(INDEX('Sheet 1'!A:A,AGGREGATE(15,7,ROW('Sheet 1'!$Q:$Q)/('Sheet 1'!$Q:$Q="Add This Row"),COUNTA($A$1:$A1))),"")

Paste this in cell A2 of sheet 2 (add some sort of header/titles in row 1 and then copy across to column P and down farther than the number of rows you need.

I think this should work for you.