Hi there
I am a newbie and have a problem with getting a detailed data table into a pivot table format. Basically I have 150 staff members in a column and they are allocated across 100 project codes going across a row. So each staff member is allocated against several projects. I have many empty cells where staff are not allocated as well. The data is in one table is hours which is then used to allocated a value in another table, this 2nd bit is fine as if I can allocate the hours properly I can write a formula to calculate the value. However I need to be able to get the staff member in one column and the hours on whichever project they are allocated to in another column. The data is also dynamic as a new member of staff can be added, the hours allocation can change but also I may need to add a new project at some point too. HELP! I tried using IF formulas to pick out the data but I cannot get any further can you help me? Below is a basic illustration of the data table:
----A---------B---------C-----------D------
1)---------Project A--Project B--Project C
2)Staff 1 5 30
3)Staff 2 1 34
4)Staff 3 35
How do I get this to be:
Staff 1--Project A--5
Staff 1--Project B--30
Staff 2--Project A--1
Staff 2--Project C--34
Staff 3--Project B--35
I hope this makes it easier to understand what I am trying to do. I fear I may need a macro of some sort which I don't know how to write, if you can help with this that would be great.
Thank you in advance for your help.
Andrew
Bookmarks