PDA

View Full Version : Solved: How To Sum Worksheets



drums4monty
02-18-2011, 02:22 AM
Hi All

I have a workbook with 63 worksheets, they are all named with club names, I also have a Summary sheet. If say membership Cards is on all of the sheets and the total issues for the month is at D9, is there an easy way to sum all of the D9 entries from the 63 sheets onto D9 on the Summary Sheet? At the moment I use the fomula Durham D9+London D9+Leeds D9 and so on 63 times.

p45cal
02-18-2011, 02:36 AM
yes, use something like
=SUM('london:new york'!D9)
where in this case, london is the name of the leftmost sheet tab and new york is the name of the rightmost sheet tab (of the tabs you want to sum). Arrange the sheets so that the sheets you want to sum are contiguous, and have your summary sheet outside that contiguous range.

Bob Phillips
02-18-2011, 03:28 AM
A typical approach is to insert a sheet before London, call it _First say, insert a sheet after the last town and call it _Last say, hide both sheets, and then use =SUM('_First:_Last'!D9). This way, if you add new sheets they automatically get covered.

drums4monty
02-18-2011, 06:48 AM
Thanks both of you problem solved. I opted for the version from xld as the workbook does get sheets added/delted from time to time.