Finding sum of A1 in all tabs

[Caulerpa]

Hello!

Thanks to the help of the wonderful people on this forum I've been able to do a lot of things with excel that has to do with counting and finding sums based on dates and other criteria. Most of the files I've been using them on are only used for one year and therefore no new tabs for new years are created. This project I'm working on now references the A1 cell in tabs that are each designated for a year (One tab is "2012", another is "2011", etc). Well, I was just wondering if there was a way to reference A1 on all tabs created without having to enter in the name of the tab. Right now I'm using the formula =(('2012'!A1)+('2011'!A1)+...) but it's going to be a pain to change all of the formulas every year (I won't be referencing just A1). Is there a way to pull the sum of all of the A1's in all the tabs regardless of name and number of tabs? I hope this makes sense.

Any help offered would be greatly appreciated. Have a wonderful day!

[abousetta]

This can be done very easily using a macro or a user-defined function (both use vba). Also if you want a formula then you can create a macro that will write the formula once. You can then just run it again when you add new sheets.

Let us which option you prefer.

abousetta

[jeffreybrown]

Try

=SUM(Sheet1:Sheet5!A1)

Change the sheet names to match your requirements

or better yet

Make two sheet First and Last

Put all sheets in between those two sheets.

then

=SUM(First:Last!A1)

[Paul]

Hi Caulerpa,

If you want to sum a cell across multiple sheets, those sheets should be contiguous in the tab order from left to right. For example, if your sheets are 2012, 2011, 2010 and 2009, and if you want to sum cell A1 on those four sheets, you could use:

=SUM(2012:2009!A1)

If you have sheets physically in between these sheets that you don't want counted, this won't work. (E.g. your sheets are 2012, 2012revised, 2011, 2011 revised, etc. - this formula would sum cell A1 on all of the sheets physically between sheets 2012 and 2009.)

This doesn't work for a number of functions, so if your formulas get more complex you may need to resort to UDF (User-Defined Functions) or macros.

[abousetta]

Jeff and Paul give great examples. One small addition, if there is certain sheets you don't want added then you can use something like (borrowing a mix of Jeff and Paul's examples):

=SUM(First:Last!A1)-2012revised!A1

Hope this helps.

abousetta

[Caulerpa]

Thank you everyone for your help! Every comment was very useful! I'll try to ask less questions and answer some posts if I can. Have a wonderful weekend!