Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding same cells across multiple worksheets
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 P&L statement for each different department within the company, and another worksheet that rolls all the department totals into one consolidated P&L. The consolidated worksheet adds the balances of each individual worksheet to calculate the company total. If each worksheet is called "Dept1", "Dept2", etc., and the consolidated worksheet is called "Total", my formula in "Total" 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('Dept1:Dept10'!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 "locks" 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: http://www.excelforum.com/member.php...o&userid=30381 View this thread: http://www.excelforum.com/showthread...hreadid=502742 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding same cells across multiple worksheets
I don't think this is possible.
"LACA" wrote in message ... 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 P&L statement for each different department within the company, and another worksheet that rolls all the department totals into one consolidated P&L. The consolidated worksheet adds the balances of each individual worksheet to calculate the company total. If each worksheet is called "Dept1", "Dept2", etc., and the consolidated worksheet is called "Total", my formula in "Total" 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('Dept1:Dept10'!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 "locks" 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: http://www.excelforum.com/member.php...o&userid=30381 View this thread: http://www.excelforum.com/showthread...hreadid=502742 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding same cells across multiple worksheets
Laca,
try the following: =SUM(INDIRECT("'Dept"&ROW(1:10)&"'!A5") which is an *array* formula (you must commit with Shift+Ctrl+Enter). This one will definitely ignore irrelevant sheets. HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text repeating on multiple worksheets | Excel Discussion (Misc queries) | |||
Adding an accumulator for multiple cells | Excel Worksheet Functions | |||
changing cells in multiple worksheets | Excel Worksheet Functions | |||
adding duplicate text to multiple cells | Excel Discussion (Misc queries) | |||
Assigning Cells in worksheets to other data in other worksheets. | Excel Discussion (Misc queries) |