I'm coding a Solution where a Shift Lead needs to set up a dynamic list of 10-25 people to cover that shift's teaching assignments. The people fall under four categories—FT Staff, PT Staff, Volunteers, and Interns, with a max of 10 people per category—and I have separate columns for each, using drop-down menus from a Master Staff Sheet. IE, the Volunteer Column drop-down only shows volunteers, the Intern Column drop-down only shows interns, etc. We've got about 150 people and these schedules are highly variable.
I need to aggregate these four columns into a single "All Staff" column that can then be used as the source for a dropdown menu in another sheet. The technique I used works, but feels very crude. It uses two helper columns:
Helper Column #1 uses Index/Match formulae, coded row by row, to aggregate all 10 rows from each of those four columns, whether they are populated or not. So I end up with an aggregate column of 40 rows with a lot of spaces ("AM Everyone Spaced").
Helper Column #2 uses an Index/Collect formula on data from Helper Column #1, only passing data from rows that are not blank ("AM Everyone")
Is there a way I could consolidate this into a single Helper Column, getting rid of the irritating #1 with blanks?
Let me know if more info is needed. And thanks as always to the Community for its support, looking forward to the day I know enough to help instead of just ask.
