I have a three-sheet workbook intended to distribute tasks to employees. Sheet1 is a dashboard, Sheet2 is a table with primary tasks (some 1000), Sheet3 is a table with secondary tasks (some 1300). The employees are imported from another workbook via a PW-protected button as new sheets that they download, fill out--a wishlist w. dropdown menus made up of the same task lists--and return. This import sheet contains their wish for tasks, their initials, and a task-sheet template that fetches the tasks they get assigned to them on sheets 2+3.
The tables on Sheet 2+3 have the tasks in column B, and "Requested by" in column J. This is what I'm asking for help with. I'm cracking for a solution (VBA or no) which will display the initials of all employees who have requested a given task in column J. I can't see that a classic index/match or vlookup would work, because the lookup ranges (the imported sheets) aren't there to begin with--and are wildly variable (so-so retention, lots of substitutes throughout the year, and so on).
So: what I gather is needed is formula or code which will accomplish the following for cells in column J on Sheets2+3:
1) lookup the corresponding B-cell in the wish-list range (D5:D18 for sheet2 and D21:D29 for sheet3) on any and all sheets after Sheet 3,
2) on match, copy the employee initals (cell A1 on the imported sheets) into the J-cell. Insofar as several matches are possible they need to be separated by commas.
I've tried cracking this in and outside of VBA for a few days at this point. I have a bunch of scattered VBA-code snippets and botched formula sketches--I don't know if it's of any help posting it here though as it's largely uncogent. I'm posting anonymized versions of sheet2 and an example of an import sheet (where you can see the filled-out wishlist on the right, and the self-filling task sheet on the left).
All insight is very much appreciated, thank you!
The tables on Sheet 2+3 have the tasks in column B, and "Requested by" in column J. This is what I'm asking for help with. I'm cracking for a solution (VBA or no) which will display the initials of all employees who have requested a given task in column J. I can't see that a classic index/match or vlookup would work, because the lookup ranges (the imported sheets) aren't there to begin with--and are wildly variable (so-so retention, lots of substitutes throughout the year, and so on).
So: what I gather is needed is formula or code which will accomplish the following for cells in column J on Sheets2+3:
1) lookup the corresponding B-cell in the wish-list range (D5:D18 for sheet2 and D21:D29 for sheet3) on any and all sheets after Sheet 3,
2) on match, copy the employee initals (cell A1 on the imported sheets) into the J-cell. Insofar as several matches are possible they need to be separated by commas.
I've tried cracking this in and outside of VBA for a few days at this point. I have a bunch of scattered VBA-code snippets and botched formula sketches--I don't know if it's of any help posting it here though as it's largely uncogent. I'm posting anonymized versions of sheet2 and an example of an import sheet (where you can see the filled-out wishlist on the right, and the self-filling task sheet on the left).
All insight is very much appreciated, thank you!
