O
ollycrook
Hello
I am producing a utilisation spreadsheet for my company.
Each project we are working upon will have an individual tab.
We need to be able to add tabs as we add projects over time.
I have produced a Master Template, which has a Personnel List defined and adrop down attached, so the user will be able to select the relevant team members (currently in cells B20 to B31) - we should never have more than 10/11 individuals working on a project.
This then links through a LOOKUP in column L to the hourly rates for these individuals, and automatically places these in.
On a daily basis, the time taken by each individual is then placed into columns P onwards.
PROBLEM ONE - I have then done a weekly COST summary at the top which multiplies up the time for each level with the hourly rate. BUT unless all of the rows are completed, it comes up with a VALUE error.
The template can then be copied to provide one sheet for every project we are working upon.
I then need to do a Utilisation summary, which takes all of the time by each member of staff, for each day, and inserts them into an overall summary.
PROBLEM TWO - we need to be able to add new sheets when we win new projects- I understand the concept of "3D formulas" so have called one sheet STARTand one sheet END and therefore understand that to add up all of the cellsin say P20, I would do =SUM(START:END!P20). This works fine, and if I then place projects between the START and END tabs, the formula works.
HOWEVER, I need to combine the above, with a WHATIF style formula, in that I need it to look at all the sheets, on all the rows, and "IF B20:31" on all sheets (START:END) equals say "OLIVER CROOK" then it adds these up, but not if the name doesn't match.
The names will often be in different orders and not necessarily always on every project, so I would like it to use the LOOKUP function.
ANY HELP ANYONE COULD GIVE WOULD BE GREAT - I CAN DESCRIBE THE PROBLEMS IN MORE DETAILS IF NECESSARY.
THANK YOU FOR YOUR HELP - IM GETTING DESPERATE!!
I am producing a utilisation spreadsheet for my company.
Each project we are working upon will have an individual tab.
We need to be able to add tabs as we add projects over time.
I have produced a Master Template, which has a Personnel List defined and adrop down attached, so the user will be able to select the relevant team members (currently in cells B20 to B31) - we should never have more than 10/11 individuals working on a project.
This then links through a LOOKUP in column L to the hourly rates for these individuals, and automatically places these in.
On a daily basis, the time taken by each individual is then placed into columns P onwards.
PROBLEM ONE - I have then done a weekly COST summary at the top which multiplies up the time for each level with the hourly rate. BUT unless all of the rows are completed, it comes up with a VALUE error.
The template can then be copied to provide one sheet for every project we are working upon.
I then need to do a Utilisation summary, which takes all of the time by each member of staff, for each day, and inserts them into an overall summary.
PROBLEM TWO - we need to be able to add new sheets when we win new projects- I understand the concept of "3D formulas" so have called one sheet STARTand one sheet END and therefore understand that to add up all of the cellsin say P20, I would do =SUM(START:END!P20). This works fine, and if I then place projects between the START and END tabs, the formula works.
HOWEVER, I need to combine the above, with a WHATIF style formula, in that I need it to look at all the sheets, on all the rows, and "IF B20:31" on all sheets (START:END) equals say "OLIVER CROOK" then it adds these up, but not if the name doesn't match.
The names will often be in different orders and not necessarily always on every project, so I would like it to use the LOOKUP function.
ANY HELP ANYONE COULD GIVE WOULD BE GREAT - I CAN DESCRIBE THE PROBLEMS IN MORE DETAILS IF NECESSARY.
THANK YOU FOR YOUR HELP - IM GETTING DESPERATE!!
31 if in B20:B31 is your name