You're welcome, Paul - thanks for feeding back.

Pete

On Nov 11, 2:55*pm, Paul Moles
wrote:
Excellent Thank You
Does just what I wanted

Paul



"Pete_UK" wrote:
If you have something like:

=INDIRECT("'"&A1&"'!C2")

(the apostrophes allow for having spaces in the sheet names) and A1
contains Tom then this will return the value from Tom!C2. However, if
A1 is empty then it will return #REF. So, you will need to check for
this, like:

=IF(A1="",0,INDIRECT("'"&A1&"'!C2"))

So, you will need to build up your composite formula like this:

=IF(......) + IF(......) + IF(.....)

and so on, where each IF term is looking at a different sheet.

Hope this helps.

Pete

On Nov 11, 9:59 am, 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)!g 10.

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

.- Hide quoted text -

- Show quoted text -