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 a drop 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 START and one sheet END and therefore understand that to add up all of the cells in 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!!

Hi Olly,

Am Mon, 4 Aug 2014 15:03:40 -0700 (PDT) schrieb :

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 START and one sheet END and therefore understand that to add up all of the cells in 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 above works fine with SUM, but not with SUMIF. On sheet Start in
column A write all the sheet names from the sheets between Start and
End.
So if your sheet names are in A1:A10 try:
=SUM((T(INDIRECT("'"&TRANSPOSE(Start!A1:A10)&"'!B" &ROW(20:31)))="Oliver Crook")*(N(INDIRECT("'"&TRANSPOSE(Start!A1:A10)&"' !P"&ROW(20:31)))))
and enter the formula with CTRL+Shift+Enter
The formula sums the values in P20:P31 if in B20:B31 is your name


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional