If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Autofill formulas b/t various worksheets
Hello-
I have a workbook w/ 25 worksheets. I need to sum the same cell from each worksheet and put it on a separate summary worksheet. I tried to use the formula ='Sheet1'B3+'Sheet2'B3+'Sheet3'B4 and then tried to autofill that formula all the way down to get the sum for all 25 sheets. However, all this did was repeat the same 3 numbers over & over. Does anyone have any suggestions? I need to do this for many different cells and it would take forever to select each cell. Thanks for your help. Lee ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Autofill formulas b/t various worksheets
You could try:
=SUM(Sheet1:Sheet25!B3) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "lbenson" wrote in message ... Hello- I have a workbook w/ 25 worksheets. I need to sum the same cell from each worksheet and put it on a separate summary worksheet. I tried to use the formula ='Sheet1'B3+'Sheet2'B3+'Sheet3'B4 and then tried to autofill that formula all the way down to get the sum for all 25 sheets. However, all this did was repeat the same 3 numbers over & over. Does anyone have any suggestions? I need to do this for many different cells and it would take forever to select each cell. Thanks for your help. Lee ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Autofill formulas b/t various worksheets
Thanks. That worked great.
Is there any way I can show each cell in a summary column or get the sum for more than one cell (but still the same cells on each worksheet)?? Thanks again. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Autofill formulas b/t various worksheets
In your "Main" or "Summary" sheet, enter this formula in any column,
starting in *Row1*: =INDIRECT("SHEET"&ROW()&"!B3") Now, select and drag down and copy for 25 rows. This should return the values in cell B3 of each of your 25 WSs. If you cannot start in Row1 of your summary, you must adjust the formula to reflect the starting row number so as to return the various sheet numbers. For example, if you start in Row5, use this: =INDIRECT("SHEET"&ROW()-4&"!B3") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "lbenson" wrote in message ... Thanks. That worked great. Is there any way I can show each cell in a summary column or get the sum for more than one cell (but still the same cells on each worksheet)?? Thanks again. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|