Forum Discussion
Solved
Link 2 sheets, then sort column A on sheet 2 alphabetically and match correct data
I have two sheets on the same spreadsheet, and I want to pull in the data from 4 different columns to the second sheet and have it automatically update when there are changes or additions to the firs...
I just wanted to update that upon posting in another forum, I was given a solution to the problem, which I then was able to breakdown and modify with help. The first code, followed by the final code.
=LET( a, CHOOSECOLS(FILTER('Unit Vacancy Tracking'!B2:Q20,BYROW('Unit Vacancy Tracking'!B2:Q20<>"",LAMBDA(r, OR(r)))),1,3,15,16), SORT(IF(a<>"",a,""),1,1))=LET(a,CHOOSECOLS(FILTER(Vacancy,(LEN(Vacancy[Unit])>0)*(INDEX(Vacancy,,5)="Vacant")),1,3,15,16),SORT(IF(a<>"",a,""),1,1))
I just wanted to update that upon posting in another forum, I was given a solution to the problem, which I then was able to breakdown and modify with help. The first code, followed by the final code.
=LET( a, CHOOSECOLS(FILTER('Unit Vacancy Tracking'!B2:Q20,BYROW('Unit Vacancy Tracking'!B2:Q20<>"",LAMBDA(r, OR(r)))),1,3,15,16), SORT(IF(a<>"",a,""),1,1))=LET(a,CHOOSECOLS(FILTER(Vacancy,(LEN(Vacancy[Unit])>0)*(INDEX(Vacancy,,5)="Vacant")),1,3,15,16),SORT(IF(a<>"",a,""),1,1))
Hi MeretrixRex
Thanks much for updating the case with your solution 👍- too few people do this 🙁. Couple of comments if I may:
- With CHOOSECOLS(..., 1, 3, 15, 16) the column indexes are hard-coded
==> If you later insert/move/delete column(s) before column index #16 in your table the formula won't work anymore - INDEX(Vacancy,,5) - does the same as CHOOSECOLS(Vacancy, 5)
==> Same comment as above - CHOOSECOLS is done after FILTERing all table columns
==> A bit inefficient
If you're interested & provide the current names of columns 1, 3, 15, 16 in table Vacancy, a more flexible solution exists...