SOLVED

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

Copper Contributor

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

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

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

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)

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

@Hans Vogelaar Thanks you .

I tried it however it did not work.

Could you please look into that again.

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

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

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

I tried in both.

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

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

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.

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

Thanks much for helping @Hans Vogelaar

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

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

I have added the formula to your workbook.

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

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.

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

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"), "")

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

Thanks very very much for helping @Hans Vogelaar

Have a good day and weekend ahead!

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

And the same to you!

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

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

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

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))

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

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)

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

It could be any order you wish

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

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

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

Thanks much appreciated! It worked.

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

@MSTS0022 , you are welcome

1 best response

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

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

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)