Sum across multiple sheets; adding new sheets

Hello,

I have a excel file which has 5 sheets. The first 4 sheets are for people to enter their monthly work totals. The fifth sheet is a tally sheet which sums the data entered into sheets 1 through 4.

An example of the formula being used in each cell on sheet 5 is as follows:

=SUM('sheet1'!F18+'sheet2'!F18+'sheet3'!F18+'sheet4'!F18)

I now need to modify the workbook so that I have 6 people entering in data and have it tally in the final sheet. Therefore, I have added the two additional sheets for each person. However, I am stuck on how to dynamically update the formula shown above.

I was hoping there was a fancy find/replace strategy that I could use to update each formula without re-typing an updated formula into a few hundred cells. The workbook has 17 columns and 250 rows. I thought that I could just make the change 17 times (at the top of each row) and drag the formula down (the + cursor). However, because of line breaks in several rows, I can not change the first row and drag the formula down each column. I can only change 3 at a time due to the break in rows.

Any help will be greatly appreciated.

Thanks!

First, create two new worksheets and call them First and Last. Then, place all relevant sheets between these two new sheets. Lastly, use the following formula...

=SUM(First:Last!F18)

Each time a new sheet is created, simply place it between First and Last.

Originally Posted by martindwilson

if there is a break is the formula to go
=f1
=f2
=f3
space
=f4
=f5
=f6
space

or
=f1
=f2
=f3
space
=f5
=f6
=f7
space

Well, I should have been more specific. Sorry. When I drag the formula down the column, I get a message that says "This operation require the cell to be identically sized".

Thanks.

I have attached an image to show you where I have troubles. When I drag the formula down the column, I get the message listed above.

When I put the SUM formula in B7, I can not get past row 11 and 12. If I type into B13, I can not drag past row 15 and 16. I hope the image helps demonstrate where I am having trouble.

I was hoping to not have to re-type the formula for every section.

Thanks for all the help.

Originally Posted by martindwilson

no need to look its coz cells are merged, unmerge all cells before you try(always avoid merged cells it may look nice but as you have found out it makes life difficult)
but the question is still there would you like to extend the vales as in first or second example. if its the second you can use auto filter and drag the formula down and it will do the visible cells only.
but while you are at it you should consider replacing =SUM('sheet1'!F18+'sheet2'!F18+'sheet3'!F18+'sheet4'!F18)
with domenics suggested 3D formula to stop this happening in the future

Yes, I have updated the formula so that this issue does not happen again in the future. So it looks like I am good to go now.

Many Thanks!