BleepingComputer.com: Specific Excel Formula

I recently learned there is a function that allows you to sum across worksheets (tabs) ONLY IF each tab is laid out EXACTLY THE SAME as the other tabs. What is this function?

I'm not talking about =SUM(Sheet1!A2+Sheet2!A2+...) as the number of tabs creates a situation where the formula is too long. The formula I'm referencing is ONLY USABLE if the layout on EACH TAB IS EXACTLY THE SAME AS THE OTHER TABS BEING TOTALLED.

This post has been edited by garnetwr: 18 April 2007 - 11:51 AM

Do you mean =SUM(Sheet1:Sheet3!A1) ?

This post has been edited by Mr Alpha: 18 April 2007 - 10:52 AM

Nope, it was something I would have never guessed...
Your suggestion might work though... I'll let you know.

In the meantime, if anyone knows that function, I'd like to knwo what it is...

Mr Alpha, on Apr 18 2007, 10:51 AM, said:

By the way, this worked on the mock-up I had, but am waiting to see if it works on the actual spreadsheet. Thanks in advance for you assistance and I will let you know if it did work on the actuals.

This post has been edited by garnetwr: 18 April 2007 - 11:52 AM

Yes, it will work. To avoid being locked into specific sheet names or count thereof, a suggestion: insert two sheets to 'anchor' references to the existing sheets. Let's say we call those sheets sL and sR. Existing worksheets in the middle, sL on the left, sR on the right. Now, formula
=SUM('sL:sR'!A2) or
=SUM('sL:sR'!$A$2)
in any cell, in any sheet will do what you want, and permits adding,removing, renaming sheets inbetween L and R.There is nothing to stop you from summing data ranges, for instance
=SUM('sL:sR'!A2:C15)
Single quotes may not be needed.
It would not be a bad idea to protect the structure of the workbook, so that users can't delete those sL & sR worksheets. Hiding both is not a bad idea either.

There is the way of highlighting all of the cells on each sheet you wish to sum and give the cells a ref name. This c an be done by changing the cell ref on the top left of the sheet. Normally says A1 or the like. When you have named the cell ranges then you use the formular =Sum(xxx) where xxx is the name you assigned.

tos226, on Apr 18 2007, 11:30 AM, said:

This is the preferred way to do it. If you do not, however, want to create two extra sheets to just hang around, you can always position them where you want them and then hide the sheets (first and last sheets). Results will not change and position is not affected.

HTH