Forum Discussion
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 first sheet. I have a couple issues making this happen - the main one is that the data in my first sheet is custom sorted by color, but I want the new sheet to take the data from column B on sheet 1 and sort it alphabetically, and take the other columns and match them correspondingly to the correct values they match to from column B on the first sheet. Secondly, for blank cells, I want it to leave them blank instead of inputting zero, or any dates.
I don't know if this is all even possible, or if I should just remove my custom filters from sheet 1 by cell color and just link it that way. I've spent hours trying to find formulas that work. Please see attached images for reference on what I've tried, and for more clarification as to exactly what I'm requesting. Sorry for the copious redacting, I wanted to be safe rather than sorry with identifiable info regarding this. Any help would be greatly appreciated.
6 Replies
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...
- With CHOOSECOLS(..., 1, 3, 15, 16) the column indexes are hard-coded
For future reference, it would be more helpful if, rather than redacted images--though that was a good move, you created a duplicate with dummy data where necessary. Especially possible given that there really aren't that many rows of data to begin with.
That said, the formulas that are in evidence in those images suggest to me that you're actually well acquainted with the dynamic array functions that would be needed. Leaving me to wonder if I can just give you some words of encouragement to "play" or experiment and find your own solution.
So let me just offer an observation and some corresponding suggestions:
- You mention that the first sheet is sorted by color, and, significantly, you even entertain the thought, "[I wonder] if I should just remove my custom filters from sheet 1 by cell color and just link it that way."
- First: I didn't even know that it was possible to "sort by color"; certainly it's not something I've ever done. In general it's best to leave "raw data" in its rawest state. Adding "pretty" features can get in the way of subsequent processes. I have had the experience of having difficulties in downstream analyses when I've, with all good intentions, applied a sort to my raw data.
- If you want to have a basis for sorting--which in itself is entirely valid--add a field with some datapoint that serves as the basis for the desired sort(s). But even then, I'd suggest you leave the actual sorting to your downstream extractions.
All of that having been said, and not knowing exactly how you've accomplished the "sort by color", My suggestion is that, yes, you do an experiment and remove both the sort and the color. Get to the point where your raw data is just that: raw. I strongly suspect that the alterations to the raw data base may be hindering the kind of extractions you want to do, and appear to be fully able to do on your own.
- The checkboxes on the 1st picture suggests 365 is in use
- An anonymized sample takes less than 5 minutes to put in place
- Order of the data in the source Table doesn't matter
With 365 that could be something like (once required/target columns are known):
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.
- You mention that the first sheet is sorted by color, and, significantly, you even entertain the thought, "[I wonder] if I should just remove my custom filters from sheet 1 by cell color and just link it that way."
