Had a look at it again..You can avoid referring to an odd cell.

'Again array entered...Use Ctrl+Shift+Enter to apply the formula.

=SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"&
{"C2","B20","D8","M19","F18","G10"}, CELL("address",A1:A6) )),"<0"))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Since you are using this formula in Total the sheet reference to 'Total' is
not needed.

=SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"&
{"C2","B20","D8","M19","F18","G10"},"Z65536")),"< 0"))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hi Paul

With data arranged in the below way in ColA in Total Sheet; try this

Col A
TOM
DICK
HARRY
PETER
BILL
JOHN

--Make sure the sheet names do not have a space after or before the entry
--Check whether the cell references for the names are in the order
C2,B20,D8,M19,F18,G10. If not correct the formula
--The formula sheet is named as 'Total' and I assume Total!Z65536 do not
have any entries.

'The contribution piece
="Contribution by " & TRIM(A1&" " & A2& " " & A3&" " & A4& " " & A5& " " & A6)

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"& {"C2","B20","D8","M19","F18","G10"},"Total!Z65536" )),"<0"))


If this post helps click Yes
---------------
Jacob Skaria


"Paul Moles" wrote:

I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL.

Simple point and click calculation provides
=+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John !G10, however this allows
no flexibility to add in only part of the team. eg combined results for DICK
PETER JOHN

I had expected to be able to write
=INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+ INDIRECT(A4)!m19+INDIRECT(A5)!f18+INDIRECT(A6)!g10 .

And then by temporarily deleting the unwanted names from the listing in A1 -
A6, provide a revised Total.
NB: As each sheet is different I appreciate the names need to be in the
correct order A1 - A6 for the cells to be correctly referenced.

Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6
nicely provides a heading for the revised report but I am struggling with the
INDIRECT function and does INDIRECT allow for some references to be blank. A2
has been temporarily deleted.

I have tried the Help file but it doesn't seem to allow for a scenario using
sheet names.

Any help appreciated.

Many Thanks

Paul Moles