Training Needs Analysis Formula - Awesome (If I can crack this problem)

[Morpheus2022]

Hi Community.

Wondered if a GURU can help me. I am in the process of developing a very smart Training Needs Analysis.
The document contains multiple sheets, but this is what I need.

PROJECT SCOPE:
I'm currently working for a prestigious client as a consultant.
They are performing a data migration from Google Docs to Microsoft One Drive.
As Principal L&D Consultant, I am trying to create a Training Needs Analysis for 4500 people.
What I need it to do is as follows:

a). I need to be able to show learning paths by each employee
b). Calculate the amount of time required per employee
c). Calculate the number of sessions each employee needs to attend.

Items B and C are complete. Item A is where I am a little stuck.

Sheet 1 cell ranges A4 to I 50 Contains the user data. (Name, Department, Job Title, Email, Business Location, Line Manager Name, Contact Telephone, Engagement Status) I'm using Employee Name 1 to 50 in this example.

Sheet 1 cell ranges K4 to R4 contains the Topics of learning, for Project 1. S4 to AA4 contains the topics of learning for Project 2. AB4 to AJ4 contains the topics of learning for Project 3.

Sheet 1 cell ranges K5 to AJ50 contains the durations of time to complete each topic for projects 1, 2, and 3.
Each employee when they filter by there name will see a duration for Each Course Project Topic. E.g., Project 1 Topic 1 = 30minutes Topic 2 = 20minutes. Topic 3. 45 Minutes etc... Project 2 Topic 1 = 15 Minutes and so on.

What I need
On Sheet 2 labelled "Custom Learning Path".
I want to select a drop down from Cell A5, which contains the full listing of Employees Names on Sheet 1.
When the name is selected, I need a formula in the cell next to the name chosen on Sheet 2, that returns the Project Name in Column B5. Then lists all of the topics for that Project beneath the Project Name in Column B but only if a duration is detected on Sheet 1.
Column C6 returns the topic durations for Project 1 Topics.
Column D6 to D? returns Project 2 name and topics in order
Column E6 returns the topic durations for Project 2
Columns F6 to F? return Project 3 name and topics in order
Column H6 returns the topic durations for Project 3

I can then total the duration of learning at the bottom of each list. Provide a custom learning path by employee and schedule courses accordingly.

If anyone is able to assist, I'd be most grateful.

Kind Regards

Mark Jones.

 

[6StringJazzer]

So, you are asking for free help for your paid consulting engagement?

 

[awoohaw]

to add onto @6StringJazzer 's thought. Have you made an attempt at doing the work?
Mr. Excel has a tool, the xl2bb add in (link below) that allows you to post mini worksheets (you can anonymize your data for privacy concerns).
Post your raw data, and your attempted calculations. Also post some expected results.

Mr. Excel does have referrals for paid consulting services here:

Consulting Services

Are you stuck doing a tedious manual process day after day? This can be automated using VBA macros behind your Excel worksheets. For over twenty years, these Excel consultants work with customers via e-mail or telephone to build a solution.

www.mrexcel.com

Best wishes