Forum Discussion
Solved
Copy cells to second sheet based on condition on one cell from first cell
Good Afternoon all!
I have Sheet1 with columns from A to O and the I column is Shipping Status. If I column value is Ready To Ship or Ready for P/U, I need columns/cells B, I , J, K, L M, N, O to be copied in Sheet2 from Sheet1 and refresh every time Shipping Status to Ready To Ship or Ready for P/U. If any new record also added in Sheet1 with Shipping Status(either Ready To Ship or Ready for P/U), the record needs to be reflected or added in Sheet2. If Shipping Status is deleted, it should also reflect.
In A2 on Sheet2:
=CHOOSECOLS(FILTER(Sheet1!$A$2:$O$1000, (Sheet1!$I$2:$I$1000="Ready to Ship")+(Sheet1!$I$2:$I$1000="Ready for P/U"), ""), 2, 9, 10, 11, 12, 13, 14, 15)
18 Replies
In A2 on Sheet2:
=CHOOSECOLS(FILTER(Sheet1!$A$2:$O$1000, (Sheet1!$I$2:$I$1000="Ready to Ship")+(Sheet1!$I$2:$I$1000="Ready for P/U"), ""), 2, 9, 10, 11, 12, 13, 14, 15)
Which version of Excel do you have? The formula that I posted will work in Excel in Microsoft 365 (and Excel Online).
