Don't think I made myself quite clear,
I can see how this checks if the referred cell is blank but not how it then
gets the Sheet Name (TOM DICK or HARRY etc) into the formula if it is not
blank.
I expected the function to be able to use sheet names INDIRECTLY, from the
list in cells A1, A2, A3 etc of the Total sheet to generate the completed
formula, with the cell reference from that sheet.

Effectively =indirect("TOTAL!a1"))c19 is equivalent to =+Tom!C19.
Your IF statement allows for cell TOTAL A1 to be blank but where am I going
wrong with the INDIRECT formula?

Appreciate the help
Thanks
Paul

"Gary''s Student" wrote:

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