Variable Sheets - Want Grand Total At End

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott Viney - 19 Jun 2006 23:39 GMT

Morning All,

We have a program that produces an excel file with multiple sheets (IE
Sheet1, Sheet2, Sheet3......). The number of sheets it produces is
variable. Could be 1, could 20, could be 50. On each sheet we have been
able to sum a column to give us a page total. What we need now is a grand
total of all the sheets on the last sheet.

How can we achieve this when we dont know the number of sheets in any given
file that is output? Is it via VBA? Or is there a simpler solution?

Cheers for any help,
Scott

Reply to this Message

Gord Dibben - 19 Jun 2006 23:52 GMT

Scott

Insert a dummy sheet at beginning and name it Start

Insert a dummy sheetsecond to last and name it End

In summary sheey sheet(last) enter =Start:End!A50

This assumes all sheets have column total in same cell.

Gord Dibben MS Excel MVP

>Morning All,
>
[quoted text clipped - 9 lines]
>Cheers for any help,
>Scott

Reply to this Message

Scott Viney - 20 Jun 2006 00:30 GMT

Mornning Gord,

Thanks for your help. We tried your suggestion. The program uses a xlt file
to start with, which the user selects from within the program. The xlt file
only has one sheet in it. By adding another sheet to the xlt file it screws
everything up as far as the program goes.

I think that anything we do has to stick within this one sheet in the xlt
file.

Any other ideas??

Regards,
Scott V

> Scott
>
[quoted text clipped - 26 lines]
>>Cheers for any help,
>>Scott

Reply to this Message