sum across worksheets

[Alexander Bogomolny]

Hello:

I am new to Excel, although not new to programming.

I am to use Excel from a Java application in which the user defines
attributes, like products and business areas. (The latter are groups of
products and are very transient.)

Originally, I am supplied with a workbook with two worksheets: one for
products, the other for groups of products. I copy and delete them as
products or the groups are created or removed.

When I create a product group worksheet, I have to modify some cells
that refer to products in that group. For example, I may have a cell
like

=product1!A1 + product3!A1 + product12!A1, where

{product1, product3, product12) constitute the group at hand.

I can of course scan all the cells in a group worksheet and, if
necessary, modify them accordingly. However, I am certain there must be
a way to specify that the sum is across worksheets product1, product2,
product12 without checking and modifying every single cell. Perhaps it
could be possible to list the products (i.e. worksheet names) in a table
in an invisible worksheet and assign a variable to it and then use that
variable in the sum. So question number 1 is Is that possible? A
question number 2 is Is there another way to achieve my goals?

Many thanks,
Alex

 

[Frank Kabel]

Hi alexander
one way to sum all values <>0 from your worksheets would be the
following:
1. Create a range with your worksheet names. e.g. A1:A4
2. Enter the following formula on this sheet
=SUMPRODUCT(SUMIF(INDIRECT("'" & A1:A4 & "'!A1:A1"),"<>0",INDIRECT("'"
& A1:A4& "'!A1:A1")))

 

[Alexander Bogomolny]

Frank, thank you for your prompt reply.

I may not have explained my need correctly.

In my origional text the word "product" means a result of "industrial
production", not the mathematical operation. What I deal with is cells
like:

(1) =SUM(sheet1:sheet12!A1),

except I would like the (sheet) ranges not to be contiguous, like

(2) = sheet1!A1 + sheet3!A1 + sheet12!A1

In my generic "product area" worksheet, there are cells like, e.g.

(3) = SUM(A1).

When the user would like to consider the group of products, say, "cars,
boats, bikes" each of which by that time would have a worksheet of its own
with the same name, I'll have to convert (3) to

(2') = cars!A1 + boats!A1 + bikes!A1

which, in itself, is not difficult but time intensive and does not appear
very intelligent. I seek a way to create a variable, say, V(vehicles) with
three possile values "cars, boats, bikes" in the hope that there is a SUM
function in Excel, like

SUM(V:A1),

which would be equivalent to (2'). If that's possible, then instead of
going through the whole worksheet and modifying all cells in the form (3)
to (2'), I'd just change the value of V. Would be very nice to have such a
feature. Does not Excel have it?

Thank you,
Alex

 

[Frank Kabel]

Hi Alexander
the formula I provided to you would nearly do that.
Put in the range the sheet names (e.g. sheet1, sheet3, sheet12). You
may then define a name for this range (goto 'Insert - Name - Define)
and use this name in the formula. e.g. you define the name 'wslist'
=SUMPRODUCT(SUMIF(INDIRECT("'" & wslist &
"'!A1:A1"),"<>0",INDIRECT("'"& wslist & "'!A1:A1")))

This formula would sum all cells A1 from the worksheets which are
within your defined range wslist

 

[Alexander Bogomolny]

Frank, thank you. I am learning.

Alex

Hi Alexander
the formula I provided to you would nearly do that.
Put in the range the sheet names (e.g. sheet1, sheet3, sheet12). You
may then define a name for this range (goto 'Insert - Name - Define)
and use this name in the formula. e.g. you define the name 'wslist'
=SUMPRODUCT(SUMIF(INDIRECT("'" & wslist &
"'!A1:A1"),"<>0",INDIRECT("'"& wslist & "'!A1:A1")))

This formula would sum all cells A1 from the worksheets which are
within your defined range wslist