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