Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Specific Excel Formula


  • Please log in to reply
5 replies to this topic

#1 garnetwr

garnetwr

  • Members
  • 44 posts
  • OFFLINE
  •  
  • Local time:07:47 AM

Posted 18 April 2007 - 10:17 AM

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.

Edited by garnetwr, 18 April 2007 - 11:51 AM.


BC AdBot (Login to Remove)

 


#2 Mr Alpha

Mr Alpha

  • Members
  • 1,875 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Finland
  • Local time:03:47 PM

Posted 18 April 2007 - 10:51 AM

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

Edited by Mr Alpha, 18 April 2007 - 10:52 AM.

"Anyone who cannot form a community with others, or who does not need to because he is self-sufficient [...] is either a beast or a god." Aristotle
Intel Core 2 Quad | XFX 780i SLI | 8GB Corsair | Gigabyte GeForce 8800GTX | Auzentech X-Fi Prelude| Logitech G15 | Logitech MX Revolution | LG Flatron L2000C | Logitech Z-5500 Digital

#3 garnetwr

garnetwr
  • Topic Starter

  • Members
  • 44 posts
  • OFFLINE
  •  
  • Local time:07:47 AM

Posted 18 April 2007 - 10:58 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...

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


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.

Edited by garnetwr, 18 April 2007 - 11:52 AM.


#4 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,568 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:08:47 AM

Posted 18 April 2007 - 01:30 PM

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.

#5 projectfocus

projectfocus

  • Members
  • 474 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Brighton
  • Local time:01:47 PM

Posted 23 April 2007 - 06:51 AM

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.
Posted Image

#6 Zack Barresse

Zack Barresse

  • Members
  • 3 posts
  • OFFLINE
  •  
  • Local time:04:47 AM

Posted 01 May 2007 - 06:40 PM

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.


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
___________
Regards, Zack B. (If you would like comments in any code, please say so.)

OfficeArticles.com :|: Extreme Excel Tutorial :|: Excel Articles by Ken Puls

What is a Microsoft MVP?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users