SOLVED

Copy cells to second sheet based on condition on one cell from first cell

Copper Contributor

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.

 

 

18 Replies
best response confirmed by MSTS0022 (Copper Contributor)
Solution

@MSTS0022 

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)

@Hans Vogelaar Thanks you .

 

I tried it however it did not work.

MSTS0022_0-1715280913384.png

Could you please look into that again.

@MSTS0022 

Which version of Excel do you have? The formula that I posted will work in Excel in Microsoft 365 (and Excel Online).

I tried in both.

I am using Microsoft® Excel® for Microsoft 365 MSO (Version 2403 Build 16.0.17425.20176) 64-bit

@MSTS0022 

It should work - I have attached a small demo workbook.

If you cannot get it to work, could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

Thanks much for helping @Hans Vogelaar 

 

I can share my test sheet with you. It is similar as the sheet I am working with.

 

@MSTS0022 

I have added the formula to your workbook.

Thanks much again @Hans Vogelaar 

It worked for me.

I have one more question - what change I need to make in the rule if I need all columns from Sheet 1( with Shipping Status - Ready To Ship or Ready To P/U) to Sheet 2 copied automatically, not only specific ones.

@MSTS0022 

Change the formula in A2 from

 

=CHOOSECOLS(FILTER('New Job Final Progress Sheet Te'!$A$2:$O$1000, ('New Job Final Progress Sheet Te'!$I$2:$I$1000="Ready to Ship")+('New Job Final Progress Sheet Te'!$I$2:$I$1000="Ready for P/U"), ""), 2, 9, 10, 11, 12, 13, 14, 15)

 

to

 

=FILTER('New Job Final Progress Sheet Te'!$A$2:$O$1000, ('New Job Final Progress Sheet Te'!$I$2:$I$1000="Ready to Ship")+('New Job Final Progress Sheet Te'!$I$2:$I$1000="Ready for P/U"), "")

Thanks very very much for helping @Hans Vogelaar

Have a good day and weekend ahead!

@Hans Vogelaar 

Additional question - 

In Sheet 2 consists empty cells with zeros. I need the cells do not have data in sheet 1 to show blank's.

 

MSTS0022_0-1715356726961.png

 

 

 

 

@MSTS0022 

As you have found, you have to format the cells on the second sheet yourself. Formulas do not transfer the formatting of the cells they refer to.

 

Change the formula to

 

=LET(s, CHOOSECOLS(FILTER('New Job Final Progress Sheet Te'!$A$2:$O$1000, ('New Job Final Progress Sheet Te'!$I$2:$I$1000="Ready to Ship")+('New Job Final Progress Sheet Te'!$I$2:$I$1000="Ready for P/U"), ""), 2, 9, 10, 11, 12, 13, 14, 15), IF(s="", "", s))

 

or for ALL columns:

 

=LET(s, FILTER('New Job Final Progress Sheet Te'!$A$2:$O$1000, ('New Job Final Progress Sheet Te'!$I$2:$I$1000="Ready to Ship")+('New Job Final Progress Sheet Te'!$I$2:$I$1000="Ready for P/U"), ""), IF(s="", "", s))

Can I change order of the columns -

From
=CHOOSECOLS(FILTER('New Job Final Progress Sheet'!$A$2:$O$999, ('New Job Final Progress Sheet'!$I$2:$I$999="Ready To Ship")+('New Job Final Progress Sheet'!$I$2:$I$999="Ready for P/U"), ""), 2, 9, 10, 11, 12, 13, 14, 15)

To

=CHOOSECOLS(FILTER('New Job Final Progress Sheet'!$A$2:$O$999, ('New Job Final Progress Sheet'!$I$2:$I$999="Ready To Ship")+('New Job Final Progress Sheet'!$I$2:$I$999="Ready for P/U"), ""), 10, 9, 2, 11, 12, 13, 14, 15)

@MSTS0022 

Yes - as @Sergei Baklan mentions, you can specify any order you like.

Thanks much appreciated! It worked.
1 best response

Accepted Solutions
best response confirmed by MSTS0022 (Copper Contributor)
Solution

@MSTS0022 

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)

View solution in original post