Hi,

I'm new to excel and as well as learning a lot on formulas I'm also finding my way when it comes to best working practices. I can usually figure out with a bit of trial and error the best way forward but I have one example that has me going round in circles.

I have a gantt chart which is updated by a project manager and I want to pull data from this that will allow an overview of how staff are allocated on any given week on which activity, the activity being identified by a unique ID.

Amongst the columns the ones I am thinking relevant would be the Activity UID, Start Week and End Week to calculate the week. Not sure on how to do the maths on that yet but I am more interested in how to structure the table first. There are also a number of columns showing the staff member on a given task. One column per staff member and on this current project there can be up to 5 staff on 1 activity.

Ideally I would rather have a table which the project manager enters a record of who worked on what in which week but this hasn't gone down well so I am looking to explore possibilities of something dynamic fed from the gantt chart.

The problem. If I create a table with say staff names across the top then whatever I put down the side whether it is week number or Activity ID, there will normally be more than 2 results. For example John Smith on week one worked on Activity 1, 2 and 3. No matter how I change the headers and rows around I can't get away from this. It's as if I need the table to see that another row is required for that week and have it automatically create one.

Can this be done or has anyone any ideas that can get me out of this circle I'm running in?

Thanks in advance for any input.