(SOLVED) Copying entire row to other worksheet if text in column ="true"

[brian01tj]

I have limited excel knowledge and no experience using VB or macros. So ultimately Id like to complete this using formulas only, if possible.

For every occurrence that a cell in column A contains "X", I need to have that entire row copied onto sheet "Needs Cat". Same for column B, with those rows being copied onto sheet "Needs Call". If both column A and B contain an "X", I need those rows copied onto both "Needs Cat" and "Needs Call". I currently have a total of 400+ rows and I need the formula to copy all rows that contain an "X" to be copied onto the appropriate sheet. It's also important to know that the "pipeline" sheet is always being updated. Today A7 could be "X" and tomorrow it could be blank. I need the "needs call" and "needs cat" sheets to automatically add and remove rows without me needing to do anything further.

Thank you for your time!

forum.jpg

Here is a copy if the workbook. Thank you again!

Copying Entire Rows - HELP.xlsx

[FlameRetired]

Array enter this formula in cell A2 of Needs Cat. Fill down and across far enough to exhaust your data in Pipeline.

Please Login or Register  to view this content.

Then array enter this formula in cell A2 of Needs Call. Fill down and across as above.

Please Login or Register  to view this content.

Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

BTW: The INDEX address (Pipeline!$A$7:$A$50) will need to be adjusted as new data in Pipeline exceeds row 50.

A file is attached.

[brian01tj]

Okay, that is exactly what I was looking for!

I do have a couple more needs. So the array formula, which I have never done before, doesn't seem to want to copy down so that both sheets "Needs Call" and "Needs Cat" is expanded through cells A7:V400. I've read that I need to select a cell within the current range and once changed, hit Ctrl+Shift and enter. I've also tried using F2, but still no luck.

Now I additionally want to perform the same style array on two more sheets "Recent Call" and "Recent Cat". RecentCall! needs to copy rows based on a "True" in Column S and Column R for sheet RecentCat!. Based on the formula you provided above AND the fact that I need to have the formula throughout A7:V400....would this formula be correct for RecentCall?

=IFERROR(INDEX(Pipeline!A$7:A$400,SMALL(IF(Pipeline!$S$7:$S$400="true",ROW(Pipeline!$A$7:$A$400)-MIN(ROW(Pipeline!$A$7:$A$400))+1),ROWS($6:6))),"")

If so, could you please help describe how I would input this into the new sheet? I have attached the new workbook (that you provided) and made some formatting changes based on my need. NOTE: there are a few hidden columns on pretty much every sheet.

I've also already added the two new sheets with the formatting I need. AGAIN, THANK YOU!!

Copy entire row LATEST.xlsx

[brian01tj]

UPDATE**

I was finally able to input the array into Recent Call!A6 and copy it across to V7. It copied the correct row if Column S = "TRUE". But, when I tried to copy the array down to Row 400, no further rows from Pipeline! were transferred over. So basically I only have one line copied down even though they meet teh criteria.

Thoughts?

forum 2.jpg

[brian01tj]

UPDATE**

I was finally able to input the array into Recent Call!A6 and copy it across to V7. It copied the correct row if Column S = "TRUE". But, when I tried to copy the array down to Row 400, no further rows from Pipeline! were transferred over. So basically I only have one line copied down even though they meet teh criteria.

Thoughts?

forum 2.jpg

[FlameRetired]

Your formula works fine in RecentCall at my end. Ditto RecentCat (with the Pipeline!$S$7:$S$400="true" changed to Pipeline!$R$7:$R$400="true")

I've read that I need to select a cell within the current range and once changed, hit Ctrl+Shift and enter. I've also tried using F2, but still no luck.

Given the relative cell column addressing in INDEX(Pipeline!A$7:A$400 you will want to be sure to start by entering / editing and committing Ctrl + Shift + Enter in the upper left most cell of the target range…then fill down and across.
Most recent workbook is attached.
BTW: I unhid the columns so I could see results of the formulas more clearly.

[FlameRetired]

Your formula works fine in RecentCall at my end. Ditto RecentCat (with the Pipeline!$S$7:$S$400="true" changed to Pipeline!$R$7:$R$400="true")

I've read that I need to select a cell within the current range and once changed, hit Ctrl+Shift and enter. I've also tried using F2, but still no luck.

Given the relative cell column addressing in INDEX(Pipeline!A$7:A$400 you will want to be sure to start by entering / editing and committing Ctrl + Shift + Enter in the upper left most cell of the target range…then fill down and across.
Most recent workbook is attached.
BTW: I unhid the columns so I could see results of the formulas more clearly.

[brian01tj]

Solved.
Thank you again for all the help.

[FlameRetired]

You're welcome. Glad it's worked out. And thanks for the rep!