Forum Discussion
Link 2 sheets, then sort column A on sheet 2 alphabetically and match correct data
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))
To clarify additional info that perhaps I should have included prior - I did try to create a dummy sheet and things just started going wrong trying to share it with a non-company associated account to make a copy to then share the link for, so I gave up and did it the more difficult way after spending about 40 minutes fighting it.
Secondly, I created conditional formatting rules to color the row if a certain selection was made, for easier tracking, and then I used the custom sort filtration option offered by Excel to group each color (which denotes the status of the process) together. I would definitely prefer not sacrificing the ease of this for the ease of auto-population if at all possible.
I appreciate you taking your time to answer and do completely understand that I have added complexity to my desired task with my formatting and filtration choices on Sheet 1.
That's interesting, for sure. I'm still not sure I'd use that ability with what is essentially a database that you also want to extract things from. Absolutely on a "front end" presentation sheet, such tools are wonderful.