Multi-variable lookup across range of variable sheet count

Prishmael

New Member
Joined
Nov 24, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Attachments

  • employeeimport, example.png
    employeeimport, example.png
    52.7 KB · Views: 13
  • Primarytasks.png
    Primarytasks.png
    44.9 KB · Views: 14

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top