Drawing data from sheet to sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1589321%22%20slang%3D%22en-US%22%3EDrawing%20data%20from%20sheet%20to%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1589321%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20how%20can%20I%20automatically%20add%20each%20row%20from%20Sheet1%20to%20Sheet2%20that%20has%20a%20certain%20text%20written%20in%20it's%20R%20column%3F%20Is%20there%20a%20function%20that%20allows%20me%20to%20do%20it%3F%20Thank%20you%20for%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1589321%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1589470%22%20slang%3D%22en-US%22%3ERe%3A%20Drawing%20data%20from%20sheet%20to%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1589470%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F759890%22%20target%3D%22_blank%22%3E%40Musculous%3C%2FA%3E%26nbsp%3B%20To%20%22write%22%20it%20to%20sheet%202%20would%20require%20a%20macro%20but%20to%20%22include%22%20it%20on%20sheet%202%20would%20have%20a%20number%20of%20options.%26nbsp%3B%20You%20could%20create%20a%20pivot%20table%20or%20query%20or%20with%20the%20new%20dynamic%20arrays%20you%20can%20add%20a%20simple%20FILTER()%20statement.%20For%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DFILTER('Sheet%201'!A%3AQ%2C'Sheet%201'!R%3AR%3D%22include%20this%20row%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewithout%20the%20dynamic%20arrays%20it%20can%20also%20be%20done%20just%20not%20as%20simple.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1589614%22%20slang%3D%22en-US%22%3ERe%3A%20Drawing%20data%20from%20sheet%20to%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1589614%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Bcan%20it%20also%20be%20done%20in%20Excel%202013%20without%20any%20addons%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1589849%22%20slang%3D%22en-US%22%3ERe%3A%20Drawing%20data%20from%20sheet%20to%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1589849%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F759890%22%20target%3D%22_blank%22%3E%40Musculous%3C%2FA%3E%26nbsp%3BYes%20it%20can.%26nbsp%3B%20Without%20the%20actual%20sheet%20the%20formula%20would%20be%20something%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIFERROR(INDEX('Sheet%201'!A%3AA%2CAGGREGATE(15%2C7%2CROW('Sheet%201'!%24Q%3A%24Q)%2F('Sheet%201'!%24Q%3A%24Q%3D%22Add%20This%20Row%22)%2CCOUNTA(%24A%241%3A%24A1)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EPaste%20this%20in%20cell%20A2%20of%20sheet%202%20(add%20some%20sort%20of%20header%2Ftitles%20in%20row%201%20and%20then%20copy%20across%20to%20column%20P%20and%20down%20farther%20than%20the%20number%20of%20rows%20you%20need.%3C%2FP%3E%3CP%3EI%20think%20this%20should%20work%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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!

3 Replies
Highlighted

@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.

Highlighted

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

Highlighted

@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.