You might have 12 monthly sheets and an annual summary or 26 department sheets and a consolidation.
I don't normally design spreadsheets that require this as it tends to go against my advice on how to structure your data.
I would generally advise one data input sheet that covers all months (or all departments) and a report that will present the information for any particular month (or department).
However, I see many spreadsheets that are structured like this and there are times when it is appropriate. In this post, I will show you a quick way to do it.
A reader asked me about this last week as she thought I had written about it in the past, and I had to look it up (as I said, I don't normally have a need for it).
Before I looked it up, I would enter a formula such as:
=Worksheet1!A1+Worksheet2!A1+Worksheet3!A1+Worksheet4!A1+Worksheet5!A1
...to add cell A1 from 5 different worksheets.
This could get very tiresome, of you had, say, 100 worksheets!
The much more efficient way to do it is:
=SUM('Worksheet1:Worksheet5'!A1)
Note that it is the position of the worksheets that matters, not their name. The formula will sum all worksheets between Worksheet1 and Worksheet5 inclusive - left to right.
The reader, when I showed her this, remembered this useful tip from wherever she had read it. Use 'bookend' worksheets if the worksheets to add may change, e.g. have worksheets named First and Last, and place any sheets to be included in the sum in between them.
My thanks to the post that helped me to answer the question:
http://www.ozgrid.com/forum/showthread.php?t=73877
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".
Thank you for this tip. It was just what I needed, and saved me a lot of time.
ReplyDeleteand did you know you can also use wildcards?
ReplyDeleteeg just enter
=SUM('work*'!A1)
and Excel will do all the work for you to create the formula that sums A1 in all sheets beginning with the word "work" (bar the current one)
No I didn't! That is clever!
DeleteIt is true about: =SUM('work*'!A1), but if you have let's say work1 and work2, excel populates the formula with the actual sheet names. For example, when you put in the formula box: =SUM('work*'!A1), after the enter you get: =SUM('work1:work2'!A1). Is there any way to keep the original formula, so it can be still valid when adding more worksheet with the same pattern name)? Thanks in advance. David
DeleteRather than use the wildcard, have a dummy worksheet at the end included in the sum, then just inswrt the new worksheets before that one.
DeleteRather than use the wildcard, have a dummy worksheet at the end included in the sum, then just inswrt the new worksheets before that one.
DeleteHow can we also add the current sheet as well?
ReplyDeleteIf the current sheet is within the range Worksheet2, say, in our example, then it should work no differently. If not, just add it as below:
Delete=SUM('Worksheet1:Worksheet5'!A1)+A1
I was looking for a solution to this problem, however, my sheets are all date named... like 21st March so the formula was not working very well.
ReplyDeleteThe best way to deal with that is to create a 'bookstart' and a 'bookend' sheet either side of all your sheets then hide those sheets so they do not bother you.
Now my formula bookstart:bookend z11 works wonders and also auto adds all new sheets.
Hi There, I'm struggling with this. When I enter the formula, and appropriate cell I need added across sheets, it opens a file finder window . . . I'm a bit lost! I'm using a Mac with Excel.
ReplyDeleteCan you please show the actual formula that is not working? Also, I am unsure of any differences for a Mac - maybe you need to include the full filepath, rather than just the sheet name. I don't have access to a Mac to test that.
Delete