I am new to Excel and is currently struggling to get intermediate formulas right.
Here is what I'm trying to achieve.
A) I have three sheets,
- EMPLOYEE, 2. PROJECTS and 3. ENGAGEMENT
B) EMPLOYEE sheet contains information about an employee. Employee ID (EMP_ID) is generated from the name entered.
C) PROJECTS sheet contains information about the projects handled or being handled. Project ID is generated from the name of the project. Most important part is the column "Resource 1" through "Resource 20" for now. As of now, a project can have maximum of 20 resources allocated to it. One can select resources from the drop-down list.
What I am trying to achieve is,
On "ENGAGEMENT" sheet, all the projects where a resource is engaged needs to appear under "Project 1" through "Project 10" columns (assuming that a person can work on maximum of 10 projects).
I need to show stats for projects between START and END date in ENGAGEMENT sheet. However, this is not of highest priority.
Please note that, I want to achieve this with formulae only, not script or macro.
Please look at the attached screenshots and the sheet I've attached.
Thanks, Dave
Screenshots and references can be downloaded from below given links.
[1]: https://i.stack.imgur.com/kTcjW.jpg
[2]: https://i.stack.imgur.com/yLyZI.jpg
[3]: https://i.stack.imgur.com/WeoTj.jpg
[4]: https://i.stack.imgur.com/LgDiW.jpg
[5]: https://docs.google.com/spreadsheets/d/1qikvz4X6bvKO8PyB_nrmZA64x6StUMlh/edit?usp=sharing&ouid=109764009612506156621&rtpof=true&sd=true
CodePudding user response：
=ARRAYFORMULA(IFERROR(SPLIT(IFNA(VLOOKUP(TRIM(A3:A),
SPLIT(SUBSTITUTE(TRIM(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(QUERY(FLATTEN(IF(DAYS(
IF((PROJECTS!D2:D4="")*(PROJECTS!C2:C4<>""), TODAY(), PROJECTS!D2:D4), PROJECTS!C2:C4)>
SEQUENCE(1, 1000, ), ROW(PROJECTS!A2:A4)&"×"&PROJECTS!C2:C4 SEQUENCE(1, 1000, ) 1, )),
"where Col1 is not null")&"×"&TRANSPOSE(QUERY(FLATTEN(
IF(PROJECTS!I2:4="",,ROW(PROJECTS!A2:A4)&"×"&PROJECTS!I2:4&"×"&PROJECTS!A2:A4)),
"where Col1 is not null"))), "×"), "select max(Col5) where Col1=Col3 "&
IF(D1="",," and Col2>="&VALUE(D1))&
IF(F1="",," and Col2<="&VALUE(F1))&
" group by Col5 pivot Col4"),,9^9))), " ", "×", 1), "×"), 2, 0)), " ")))
