Need help with formula #5

I have a workbook that has about 20-30 worksheets in it. There is one column 
in every worksheet that has a total, is there a way to have all the totals 
from the worksheets show up on one worksheet as a grand total.

Thanks in advance 


0
no6472 (24)
1/17/2006 9:52:21 PM
excel 39879 articles. 2 followers. Follow

2 Replies
389 Views

Similar Articles

[PageSpeed] 41

If the sheets were named 1-30, and your cell with total was A1, use
this formula:

=SUM('1:30'!A1)


-doodle

0
ADraughn (8)
1/17/2006 9:58:23 PM
Hi

Insert 2 new sheets. Name one as first the other as last.
Drag them to positions which encompass the range of 30 sheets you want 
total, with your summary sheet outside of this "sandwich".
=SUM(First:Last!A100)   or whatever cell on each sheet is holding your 
column total.
Moving the positions of first and last will allow you to summarise 
whatever range of sheets you want, without having to change the formula.

-- 
Regards

Roger Govier


"Just Me" <no@isp.com> wrote in message 
news:%23XJaLB7GGHA.3056@TK2MSFTNGP09.phx.gbl...
>I have a workbook that has about 20-30 worksheets in it. There is one 
>column in every worksheet that has a total, is there a way to have all 
>the totals from the worksheets show up on one worksheet as a grand 
>total.
>
> Thanks in advance
> 


0
roger5293 (1125)
1/17/2006 10:01:39 PM
Reply:

Similar Artilces:

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Drop-down list #5
I created a drop-down list in a separate sheet in my workbook. I named it Vehicles. Now I want to add to the list, but I can't figure out how to do it. I know ig must be so easy, but I'm stumped. Please help. If it's a one-time occurrence, you can press Ctrl+F3, click on the name, and extend the formula. Otherwise, I would make the range dynamic. You can learn how to do that here: http://www.contextures.com/xlNames01.html#Dynamic HTH Jason Atlanta, GA >-----Original Message----- >I created a drop-down list in a separate sheet in my workbook. I named it >Vehicles...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Trouble with Excel 2000 Help file
Running Excel 2000. When I click on "See Also" at the top left of my Help screen, I get an internet explorer error message: "An error has occurred in the script on this page" Line1, Character 1. It goes on with some other stuff. Also, at the top of the page, I don't see hyperlinks anything else, but I see two little blue squares with a dot in the middle of each, which I usually associate with images that don't download. I seem to have my help files confused. I've tried reinstalling Office, but that doesn't help. I've gone to the microsoft support w...

Event ID: 8270 ,LDAP Error--Need help to fix it..
Event Type: Error (win2000 Srv+exchange2000) Event Source: MSExchangeAL Event Category: LDAP Operations Event ID: 8270 Computer: Exchange_2000_Server_Name Description: LDAP returned the error [b] Administration Limit Exceeded. dn: "GUID=8F37136AFCEE0B41BDD353B78F8E6067" changetype: Modify mail:USER1@Domain_Name.Domain_Suffix proxyAddresses:MS:NET/PO/User1 : CCMAIL:User1, PO at NET : SMTP:USER1@EMAIL.Domain_Name.Domain_Suffix : X400:c=US;a= ;p=Exchange_Org_Name;o=Exchange_Site_Name;s=Last_Name;g=First_Name; : smtp:USER1@MAILLIST.Domain_Name.Domain_...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

crm consultant needed asap
i am looking for a crm consultant who has a lot of experience with form customization, crm implimentation, heavy work flow, and activities. ideally someone in arizona but not required. telecommute will be considered for the right person. looking for someone for a possible 2 month (i am guessing here....) project. if you are available and have this experience please email me at j-e-f-f@mag-en-ta-tech.c-o-m (remove the -). we are looking for someone to start like next week at the latest (this message was posted 08-08-2004). This message was posted 8/4, not 8/8. And I have some swamp land in f...

Offline Folders #5
I am trying to get an outlook client setup so that it automatically opens offline without prompting the user. Does anyone know if this is possible, if so how to do it. Thanks Phil pearson In the properties of the e-mail account (Tools, Options, Mail Setup, E-mail Accounts, View or Change existing e- mail accounts) if this is an Exchange e-mail account, highlight it and hit Change, then More Settings. Select Manually Control Connection State, then select Work Offline and Use Dialup Networking. This should give you the desired effect. >-----Original Message----- >I am trying ...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Moving Exchange #5
I am needing to move my Exchange server off of the SBS box that it is currently on and move it to another, new server. I cannot keep the servers the same name as I need the old server to continue to run SQL. Are there white papers on how to do this? TIA Scott T. On Fri, 18 Aug 2006 08:43:40 -0700, scottdog129 <scottdog129@discussions.microsoft.com> wrote: >I am needing to move my Exchange server off of the SBS box that it is >currently on and move it to another, new server. I cannot keep the servers >the same name as I need the old server to continue to run SQL. Are...

request to solve my problem #5
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

GETPIVOTDATA data_field help
When I enter a reference to a cell, eg A2, as the value for the data_field item in the GETPIVOTDATA I get a #REF error. However, when I instead type the value of A2, eg 2003, manually into the GETPIVOTDATA formula, it works. Can anyone please help--I want this formula to vary based on what is in A2. Cheers This function is so variable in action, and difficult to handle, that have given it up in favour of VLOOKUP. In fact, having no problem i use with pivot tables -- Message posted from http://www.ExcelForum.com Try copying the headings from the pivot table, and pasting them into the ce...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

Run time error 5 : HQClient
When I double click HQ Client I have message box "Run time error 5 : Invalid procedure call or argument" how should I do? I used RMS 2.0 -- TOY2TOY ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGr...

Saving #5
Is there a way of speeding up saves in excel or a way to condense the size of a workbook. Jason, This link can help with the size issue... http://www.contextures.on.ca/xlfaqApp.html#Unused Item 2d is the one you want. Regards, Jim Cone San Francisco, USA "Jason Zischke" <JasonZischke@discussions.microsoft.com> wrote in message news:18CCEA1F-2479-4ED9-A5F6-21B781D0F403@microsoft.com... > Is there a way of speeding up saves in excel or a way to condense the size of > a workbook. ...

Help!!!!
I am running Exchange 5.5 on NT 4.0 sp6a. Norton Anti- virus deleted my edb.log file. I have done an on-line restore with Arcserve 2000 and now my directory service won't start. getting 1166 internal processing error. I have been to support and could find anything that helped. Thanks in advance You may want to run eseutil /mh <drive and path to your dir.edb and include the dir.edb > c:\edb.dmp. Find this file and see if your dir is also inconsistent. If so you will need to restore your dir. -- Nan Bennett (MSFT) Microsoft Exchange Support Please do not send e-mail...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Formula to display nearest following Thursday in mm/dd/yyyy format
Hello, I have been reading and trying different suggestions here to no avail. What I need is a formula to calculate the nearest following thursday, and display it in mm/dd/yyyy format. To be clear, I have a column of varying dates. I need a formula to return the next thursday for each of those dates. To illustrate, say I have 05/22/2010, 05/23/201, 05/24/2010, & 05/26/2010 in cells A1 through A4. In cells B1 through B4, I would like to see 05/27/2010, 05/27/2010, 05/27/2010, & 05/27/2010 representing the following thursday. Thank you for your help! BW T...

Error #5
I've tried to save my transaction data to my desktop. However, each time I try this I get the error "Error reading or writing file "." " Could someone tell me what's going on? ...