Sum on multiple Worksheets

Hi Everyone,

I need to sum the values from same addresses (e.g. "C1") on multiple sheets. This is how I do it:

Please Login or Register  to view this content.

The problem is that when I enter the sum range in the formula manually, it works fine, but when I use a reference cell (either a result of a formula or just a String value), It does not work.

Any help would be highly appreciated.

You probably need to use INDIRECT. What does your reference cell contain?

Pete

Hi Pete,

Thank you for your question.

The reference cell contains a result of the ADDRESS function:

Formula:

Please Login or Register  to view this content.

Where in "L6" cell I have a first and final sheet names - 12517185:12517187

Yes, but what is the result of that formula - what shows in the cell?

Pete

Pete,

'12517185:12517187'!C1 - That is a result of my formula.

Okay, well assuming your reference cell is R6, try this:

=SUM(INDIRECT(R6))

Hope this helps.

Pete

I have already found a solution:

Please Login or Register  to view this content.

Where:

Range is a dynamic named range which contains all required worksheet names;
L7 contains a target cell address (e.g. "C1")

Thank you very much for your support, Pete.