Just test if you little table has a blank:

=IF(A1="",0,INDIRECT("Sheet1!C1"))+IF(A2="",0,INDI RECT("Sheet2!C1"))+IF(A3="",0,INDIRECT("Sheet3!C1" ))

Just change the names in the indirect to match the names in the table.

You can also adjust the cell addresses.
--
Gary''s Student - gsnu200908


"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