software

Posted: Thu Jan 19, 2006 12:51 am Post subject: Adding same cells
across multiple worksheets

--------------------------------------------------------------------------------

I have a file with 10 worksheets, each of which contains a Pamp;L
statement for each different department within the company, and another
worksheet that rolls all the department totals into one consolidated
Pamp;L.

The consolidated worksheet adds the balances of each individual
worksheet to calculate the company total.

If each worksheet is called quot;Dept1quot;, quot;Dept2quot;, etc., and the
consolidated worksheet is called quot;Totalquot;, my formula in quot;Totalquot; for
each line was this:

= Dept1!A5 Dept2!A5 Dept3!A5...and so on.

I then changed it to this because it's much shorter:

=SUM('Dept1ept10'!A5)

The only problem is that there are other worksheets in this file as
well, and the users tend to move the placement of the worksheets around
to suit their needs, which would obviously make the second formula
inadequate if they moved one of the department worksheets out of the
listed range.

Is there a formula that quot;locksquot; the worksheet names so that all ten
worksheets will be included in the total, regardless of where they are
moved within the file?--
LACA
------------------------------------------------------------------------
LACA's Profile: www.excelforum.com/member.php...oamp;userid=30381
View this thread: www.excelforum.com/showthread...hreadid=502742I don't think this is possible.quot;LACAquot; gt; wrote in message
...
gt;
gt; Posted: Thu Jan 19, 2006 12:51 am Post subject: Adding same cells
gt; across multiple worksheets
gt;
gt; --------------------------------------------------------------------------------
gt;
gt; I have a file with 10 worksheets, each of which contains a Pamp;L
gt; statement for each different department within the company, and another
gt; worksheet that rolls all the department totals into one consolidated
gt; Pamp;L.
gt;
gt; The consolidated worksheet adds the balances of each individual
gt; worksheet to calculate the company total.
gt;
gt; If each worksheet is called quot;Dept1quot;, quot;Dept2quot;, etc., and the
gt; consolidated worksheet is called quot;Totalquot;, my formula in quot;Totalquot; for
gt; each line was this:
gt;
gt; = Dept1!A5 Dept2!A5 Dept3!A5...and so on.
gt;
gt; I then changed it to this because it's much shorter:
gt;
gt; =SUM('Dept1ept10'!A5)
gt;
gt; The only problem is that there are other worksheets in this file as
gt; well, and the users tend to move the placement of the worksheets around
gt; to suit their needs, which would obviously make the second formula
gt; inadequate if they moved one of the department worksheets out of the
gt; listed range.
gt;
gt; Is there a formula that quot;locksquot; the worksheet names so that all ten
gt; worksheets will be included in the total, regardless of where they are
gt; moved within the file?
gt;
gt;
gt; --
gt; LACA
gt; ------------------------------------------------------------------------
gt; LACA's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30381
gt; View this thread: www.excelforum.com/showthread...hreadid=502742
gt;
Laca,
try the following:

=SUM(INDIRECT(quot;'Deptquot;amp;ROW(1:10)amp;quot;'!A5quot;)

which is an *array* formula (you must commit with Shift Ctrl Enter).
This one will definitely ignore irrelevant sheets.

HTH
Kostis Vezerides