I have a collection of worksheets, each with the same format of report, and a
summary sheet which shows the name of each worksheet.

I want to create a selection sheet, where I can mark each of the required
sheets and have the selection sheet show the summed data.

I have tried :
=sumproduct(SheetA:SheetZ!C10,SheetA:SheetZ!$A$1), but it returns a #REF error
(A1 in each sheet holds a 1 if the sheet is required to be included.)
(C10, plus a number of other cells, holds the relevant data on each worksheet)

Any help would be appreciated

Sadly, SUMPRODUCT does not support 3D referencing. You can quickly do a
search in XL help file to find what formulas do.

Sorry I wasn't more helpful.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Paul" wrote:

I have a collection of worksheets, each with the same format of report, and a
summary sheet which shows the name of each worksheet.

I want to create a selection sheet, where I can mark each of the required
sheets and have the selection sheet show the summed data.

I have tried :
=sumproduct(SheetA:SheetZ!C10,SheetA:SheetZ!$A$1), but it returns a #REF error
(A1 in each sheet holds a 1 if the sheet is required to be included.)
(C10, plus a number of other cells, holds the relevant data on each worksheet)

Any help would be appreciated

C10, plus a number of other cells, holds
the relevant data on each worksheet

Specifically Define: "plus a number of other cells".

Are the cells in question in a contiguous range?

--
Biff
Microsoft Excel MVP


"Paul" wrote in message
...
I have a collection of worksheets, each with the same format of report, and
a
summary sheet which shows the name of each worksheet.

I want to create a selection sheet, where I can mark each of the required
sheets and have the selection sheet show the summed data.

I have tried :
=sumproduct(SheetA:SheetZ!C10,SheetA:SheetZ!$A$1), but it returns a #REF
error
(A1 in each sheet holds a 1 if the sheet is required to be included.)
(C10, plus a number of other cells, holds the relevant data on each
worksheet)

Any help would be appreciated

Excel 2007
Consolidate selected worksheets.
No formulas or code used.
http://www.mediafire.com/file/xtm24d...12_19_08a.xlsx