Copy a few Columns from one sheet to another so they can be sorted and updated automatically

[DeGeorge]

Hi

I have a table in Sheet 1 with 6 columns. I want to copy columns 2, 4, and 5 to Sheet 2, sort them, and have them automatically update if the related cells in Sheet 1 are updated.

It's probably my inability to word the search criteria with the correct syntax, but I couldn't find anything. Please help. Thanks!

 

[SunnyAlv]

hi @DeGeorge

Can you insert an example picture or table to make it easier for someone to understand the desired achievement?

 

[DeGeorge]

Data on Sheet 1, columns A, B, C

Ticket# Table# Name
1 2 Joe
2 3 Pete
3
4
5 1 Harry


Desired results on Sheet 2, Column B and C, only if B isn't blank, then I want to be able to sort.

Table# Name
2 Joe
3 Pete
1 Harry


I've tried =FILTER(IF(Table1="","",Table1),Table1[Name]<>"") but it returns the whole table and I cannot sort.

 

[SunnyAlv]

Try this

=LET(T,SORTBY($B$3:$C$7,B3:B7,1,C3:C7,1),FILTER(T,CHOOSECOLS(T,1)<>"",""))

 

[DeGeorge]

Possibly I'm not understanding the syntax, or my sample data is not sufficient. I cannot post real data and it's too complicated to make dummy values.

Table1 is the table/array/range on Sheet 1 from which I want to copy columns A, B, C, and G. And then be able to sort them by Column B.

I tried this...

=LET(Table1,SORTBY(Table1,Sheet1!A5:A234,1,Sheet1!B5:B234,1,Sheet1!C5:C234,1,Sheet1!G5:G234,1),FILTER(Table1,CHOOSECOLS(Table1,1)<>"",""))

It returned all of Table 1 from Sheet 1 on Sheet 2

 

[SunnyAlv]

Try This

=LET(T,SORTBY(Sheet1!A5:G234,Sheet1!A5:A234,1,Sheet1!B5:B234,1,Sheet1!C5:C234,1,Sheet1!G5:G234,1),FILTER(T,CHOOSECOLS(T,2)<>"",""))

 

[DeGeorge]

That is still including columns D, E, and F as well.

 

[SunnyAlv]

Ahh i seeeee

Try this

=LET(T,CHOOSECOLS(SORTBY(Sheet1!A5:G234,Sheet1!A5:A234,1,Sheet1!B5:B234,1,Sheet1!C5:C234,1,Sheet1!G5:G234,1),1,2,3,7),FILTER(T,CHOOSECOLS(T,2)<>"",""))

 

[DeGeorge]

We have a winner! Thank you!!!