totals #2

I've a workbook with 1 project management sheet and several sheets wit
testdata. The project management sheet I use for instance to show tota
figures. For example. I want to make totals for sheet1!A1 + sheet2!A1 
sheet3!A1. As you see the position of the cells remanes the same. Eas
you say....but what I don't know is: how many sheets shall I have, an
I don't want to adapt everytime my formulas after adding a sheet 
I there a possibility to say take all the sheets where the name of th
sheet begins with xxx and make a sum of cell A1?

thanks in advance.

davi

--
daol
-----------------------------------------------------------------------
daolb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2447
View this thread: http://www.excelforum.com/showthread.php?threadid=38150

0
6/23/2005 8:06:11 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
485 Views

Similar Articles

[PageSpeed] 13

Try:

=SUM(Sheet1:Sheet4!A1)

new sheets inserted should be between these 2 sheets in the formula.

Mangesh


-- 
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10470
View this thread: http://www.excelforum.com/showthread.php?threadid=381509

0
6/23/2005 8:41:42 AM
this solution is not quite what I want.

for instance 

sheet 1: scrn_fun_001    A1: 10
sheet 2: Scrn _use_001  A1: 10 
sheet 3: scren_fun_002  A1: 10
sheet 4: scrn_use_002   A1: 10


when I take your solution the total sum is 40, and I want only take
into account sheet1 and 3. You could say reorder the sequence of the
sheets.
Not a good solution, because sheets can be added later on, and I don't
want to adapt my formula.

I would like that the system takes all A1 cells where the sheet name
starts with scrn_fun or scrn_use.

david


-- 
daolb
------------------------------------------------------------------------
daolb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24478
View this thread: http://www.excelforum.com/showthread.php?threadid=381509

0
6/24/2005 7:08:32 AM
Here's a small UDF wihch will help you sum the same cell in all the
sheets.

Function mySum(rng)
For Each Sht In Worksheets
mySum = mySum + Sht.Range(rng.Address)
Next
End Function


A small if statement will help you weed out unwanted sheets, for
instance, lets say you have a summary sheet, and don't want to include
this sheet, then you could modify the above udf to:

Function mySum(rng)
For Each sht In Worksheets
If sht.Name <> "Summary" Then
mySum = mySum + sht.Range(rng.Address)
End If
Next
End Function


The UDF shold go in a standard module in VBE. Press Alt F11 to open
VBE. Insert > Module. And enter the above code.

Usage:
=mysum(A1)
to summ A1 from all sheets.


Mangesh


-- 
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10470
View this thread: http://www.excelforum.com/showthread.php?threadid=381509

0
6/24/2005 8:39:52 AM
Reply:

Similar Artilces:

Outlook (Office XP) #2
I am running Windows Xp. I have just installed office Xp on my computer and when I try to close down outlook i get an ERROR window come up that say Outlook has to shut down. CAN ANYONE HELP ME OUT. AppName: outlook.exe AppVer: 10.0.2616.0 ModName: msmapi32.dll ModVer: 10.0.2625.0 Offset: 0001e85d Thanks! ...

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

graphics in Publisher #2
When viewing my document in Print Preview, only graphics appear. Does the document print in toto? Have you gone to the manufacturer's web site of your printer model and looked around for an updated driver? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "cmeeker" <cmeeker@discussions.microsoft.com> wrote in message news:B1EF4D42-295C-4BCF-A7D9-97362174FECF@microsoft.com... > When viewing my document in Print Preview, only graphics appear. Yes, the document does print. No, I haven't gone to the printer...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

cannot access mailbox #2
Hi, I have a user who cannot access his mialbox. He is using outlook2003. Exchange server is 2003. Pst file is ok, no corruption. When he click his mialbox from outlook, the message said:" the set of folders count not be opened." I tried re-creating his profile and rebooted email server. It did not help. Any help? Hi Rick, try with OWA -- Regards, Sasa Milovanovic MCSE:Messaging sasa.milovanovic(at)exchangemaster.net "Rick" <Rick@discussions.microsoft.com> wrote in message news:3DB53257-2681-4892-A424-16952EADEEE7@microsoft.com... > Hi, I have a user wh...

Bookkeeper #2
I have MsMoney 04 but want to use a business software called 'Bookkeeper' Is anyone familiar on how to transfer all my check acct. register from money to bookkeeper. do you think it would be best to use quickbooks instead. thanks -- Jay FWIW, most versions of Microsoft Office Accounting 2008 are able import from Money as well as Excel and Quicken. If you can manage with the Express version, the price is right - FREE. "Jay" <Jay@discussions.microsoft.com> wrote in message news:BFB43C6D-D5CF-43CB-B9A6-AFBA000B8CF9@microsoft.com... > I have MsMoney 04 but want...

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

working with multiple worksheets #2
I would like to collect data from one specific cell (cell E3) in 90 worksheets and have this data placed in one column from 1-90 on one single worksheet. Is there a way to do this without doing it one by one? Hi! Are you using the default sheet names? (probably not, huh?) If there is some common sequence in the sheet names then a formula could probably be crafted to do this. Biff "Shaun" <Shaun@discussions.microsoft.com> wrote in message news:B8CEFE1A-1B25-41F9-B72F-103CB6AF476D@microsoft.com... >I would like to collect data from one specific cell (cell E3) in 90 &g...

POP message #2
The cable company I use does not support Outlook. I keep getting POP. messages to put in my name and password every minute or so. Cable company says I have to use Outlook Express. How do I turn off Outlook and make Express dominant. Keep getting errors when I attempt to switch over and mail keeps going to Outlook What version of Outlook do you have? What is the exact text of the error message you're getting? Outlook will work fine for receiving mail from your cable company - it's just that the company doesn't want to go to the trouble of telling you how to configure it p...

IF help #2
i have to calulate a Excel Spread sheet on a weekly basis so i know how to pay staff for product sales the file is output from a weird old dos program into a excel document I have 2 sheet inside a Excel document lets call them Sheet 1 and sheet 2 Sheet 1 has 3 columums Date Product and $Due etc 8/06/06 TABLE 8/06/06 CHAIR 8/06/06 TABLE $due Columum is not filled and i need this to auto fill Sheet 2 has a list of products in Columum 1 and 2 has the price on it PRODUCT $DUE Table $50 I need to do a if statement that if sheet 1 PRODUCT = SHEET 2 PRODUCT THEN sh...

attachments removed #2
I receive the following message when receiving emails with attachments: "OE removed access to the following unsafe attachments in your email: (file name)" Can this barrier be overcome? Open Outlook Express and select Tools | Options | Security tab and uncheck the second box from the top concerning attachments. "Lisa Fry" <lisafry@iprimus.com.au> wrote in message news:716101c483f0$94d281f0$a501280a@phx.gbl... >I receive the following message when receiving emails > with attachments: > "OE removed access to the following unsafe attachments in > yo...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

Alternatives to VBA in Excel #2
Hi, excuse my cross-posting, but I do not know which NG is the appropriate for my problem. I wrote an Excel-Macro in VBA which reads a text file, interprets this file and then does in Excel what has o be done: format cells, put values to it, storing files, inserting worksheets etc. This works well, as designed, but is not very fast. So I look for a way to increase speed. I have Visual C++ 6 in Visual Studio. Can I rewrite my macro in Visual C++ so that it gets faster? If yes, where do I find "easy to understand" descriptions how to do this? What do I need additionally? TIA Thomas Kre...

Line breaks #2
Using Outlook 2003....my problem is all the text runs together and the lines don't break in the proper place. Does anyone have any ideas???? Thanks in advance.... RMP RMP <rmp@fourthr-ohio.com> wrote: > Using Outlook 2003....my problem is all the text runs together and > the lines don't break in the proper place. Does anyone have any > ideas???? Do you have Tools>Options>E-mail Options>Remove extra line breaks in plain text messages selected? -- Brian Tillman ...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

Total Items
Hi - Is there a way to get a total number of items that are about to be tendered, on the POS screen ? Thanks. You can customize the status.htm file. Not sure exactly what the variable is that you want, but I know it cn be done. "NJS" <NJS@discussions.microsoft.com> wrote in message news:538F3CC7-F23D-43C7-8A5A-4319B8688E5B@microsoft.com... > Hi - > Is there a way to get a total number of items that are about to be > tendered, > on the POS screen ? > Thanks. Jason, Send me your email I have already customized this and will forward you. "Jason&qu...

Design Question #2
I am currently tasked at writing the code for an app that will have a lot of owner drawn buttons in it. There are two ways I figured I could go about this: 1) Dialogs: a)Just use multiple dialogs for all the buttons and options and have them popping up then closing and more popping up.... this seems like an unacceptable method based on user interaction confusion and that it would just look sloppy. b) One giant dialog that handled all of the subcatagory buttons and then center it in the middle of the screen and call it a day. I guess it could be done, but again would kind of seem unprofes...

Total of Totals
Hey there, I've set up a database consisting of four tables. I run a query which calculates a total for the item (unit price * quantity). Is it possible to calculate the final total of all this for a report? If that's possible, how can I get it to be at the bottom of all the records but only on the last page? Thanks Mathew On Aug 1, 12:48 am, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Yes. > Open the form in design view, click on menu VIEW - Report Header/Footer a= nd > add a text box in the footer with Control Source =3D[unit price] * > [quantit...

Manipulating totals and columns in Pivot Tables
Hi, I'm constructing a pivot table that has investors as columns, investments as rows, and the general data is values. Trouble is there is a column which I want to only display half of each value. I know that I can make a formula that divides a column "column A/2", and then hide the unwanted columns. But I would also like the hidden columns to not be included in the grand totals. How do I make this happen? Thanks! Hi, So let me understand what you want, something like this in the Pivot Table: A B C Name Jp ...

Bank Trx. error #2
Hello, User entered a bank transaction. This transaction I can view in Smart list audit trial code also available (no errors indicated). But in account enquiry and trial balance, this transaction not available. Please help me to solve this prob. Hi Syam, Please review whether the transaction has been post through General Ledger by double checking any unposted journal entries. If there is none, please double the cash posting account used in bank transaction and verify in the detail account inquiry whether similar amount has been posted on the fiscal period the bank transaction has ...

PA Invoice Format Project Summary Page 2
Hi: I am modifying PA Invoice Format Project Summary Page 2, in project accounting. I need to add Category IDs and their costs in the Billing Sections of the Project. At this time only summary information is printing, i.e Vendor Invocies and Other Charges. I want to show the cost categories aginst which these invoices and charges are. I added cost category table into the tables list, created one new section by using cost category Id, Added the Cost Category field in the new section. This section is underneeth of H3, where it should be. Now when I run the report, it does not run. It gi...

Pole display Sub Total
Hi All, When a client of mine scans items the amount displaying is the sub total. Is there any way to display the total (ie including tax amount) Regards Amanda ...

Not enough system resources to display completely #2
I get the notification "Not enough system resources to display completely" whenever I try to open a fairly large (4MBytes) and complex (tons of formulaes)Excel file. The file is opened but the display is garbled. I have created this file over a period of time myself, so now suddenly why am I unable to open it. I tracked the memory usage around the time I get this error and it was at around 58 MBytes. I am using Excel 2003, which is supposed to have a per instance limit of 1GBytes. I have a lot of critical data in there which I do not want to loose. What should I do? Do al...

Activecell problem #2
I have a really basic question I am trying to print a certain area which starts at a certain cell which may differ every week. I have the code to get to the bottom right of the area I want to print but then I want to select the area, from the active cell to A3: 'go to the bottom cell in colum L Range("L100").End(xlUp).Select ???????? what goes in here?????? 'Print the selected area ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Any help appreciated. remove your select line and add range(Range("A3"),Range("L100").End(xlUp)).Select -...

printing #2
i am trying to print a flyer that has a picture i created myself. what i did was make a square, put 4 separate lines on top of it, then put the same square (except made it somewhat transparent by way of the fill options) and put it on top of it to so it would be transparent enough to be able to see a hint of the lines underneath...however, when I print it, it does not print what i see...it prints a the full solid background as if it were not transparent. how can i print it with the transparent effect i want? >-----Original Message----- >Stephanie what version of Publisher are y...