Summary Sheet Totals

I have a workbook that tallys values from 30 or so separate sheets.  I
there an easy way to have a summary sheet that looks at specific cell
on each sheet?  Right now, I have to do a simple SUM function where 
manually click on each cell on each sheet I want in the total.  I'
thinking there is a way to do this with a formula/array. 

Thanks,

Gar

--
gwhit
-----------------------------------------------------------------------
gwhite's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2507
View this thread: http://www.excelforum.com/showthread.php?threadid=38589

0
7/10/2005 2:58:54 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
431 Views

Similar Articles

[PageSpeed] 4

Here you go:
http://www.officearticles.com/excel/sum_the_same_cell_in_multiple_microsoft_excel_worksheets.htm

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"gwhite" <gwhite.1rxoaa_1120964701.6203@excelforum-nospam.com> wrote in
message news:gwhite.1rxoaa_1120964701.6203@excelforum-nospam.com...
>
> I have a workbook that tallys values from 30 or so separate sheets.  Is
> there an easy way to have a summary sheet that looks at specific cells
> on each sheet?  Right now, I have to do a simple SUM function where I
> manually click on each cell on each sheet I want in the total.  I'm
> thinking there is a way to do this with a formula/array.
>
> Thanks,
>
> Gary
>
>
> -- 
> gwhite
> ------------------------------------------------------------------------
> gwhite's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=25073
> View this thread: http://www.excelforum.com/showthread.php?threadid=385898
>
>


0
ng1 (1444)
7/10/2005 3:17:44 AM
Are they always in the same position (summing A1 of all the sheets or b22 of all
sheets)?

If yes...

You create two "helper" worksheets.  One named Start and one named Finish. 
(Lock all the cells and protect these two worksheets--just so no one uses them.)

Put all your "data" worksheets (and no others!) between those two worksheets.

Then you can use this on the summary sheet (outside that "sandwich")

=sum(Start:Finish!a1)

The users could even play what-if games.  If they drag a data worksheet out of
the Start:Finish sandwich, then that worksheet's values will be ignored.

gwhite wrote:
> 
> I have a workbook that tallys values from 30 or so separate sheets.  Is
> there an easy way to have a summary sheet that looks at specific cells
> on each sheet?  Right now, I have to do a simple SUM function where I
> manually click on each cell on each sheet I want in the total.  I'm
> thinking there is a way to do this with a formula/array.
> 
> Thanks,
> 
> Gary
> 
> --
> gwhite
> ------------------------------------------------------------------------
> gwhite's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25073
> View this thread: http://www.excelforum.com/showthread.php?threadid=385898

-- 

Dave Peterson
0
petersod (12004)
7/10/2005 10:51:00 AM
Great Stuff!  Works perfectly - thanks for the tip!


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

0
7/11/2005 1:28:10 AM
Reply:

Similar Artilces:

daily sales summary info
I have a small clothing boutique that recently moved locations. We would like to know if our traffic has increased, and if our average sale has increased. Is there any way to access this information in a report? Paging through every day of Z reports is pretty tedious. MP There's a custom report you can download called "average sales by store". It's just summary data that you can filter by date ranges. It includes your average transaction $ amount, avg # of units/transaction, profit marging % and avg # transactions/day. Good luck. "MP" wrote: > I ha...

Copy data from column that begins with data to new sheet
Hello, I'm currently trying to copy data from a row in excel based on what's in colum B to it's own sheet. I've found some good macros but need some help on how to modify it to fit my needs. The one macro that is almost perfect is AdvFilterRepFiltered.zip on http://www.contextures.com/excelfiles.html#CondFormat That being said, Column B consists of something like this: 3960020262 3960020263 4550050473 4550050474 I want to be able to sort 39600 and 45500 to their own sheet. The macro I mentioned above works great but it will sort everything to it's own sheet that doesn...

totaling to a different sheet
I have 2 work sheets, 1 is weekly time card, the other expenses. I want to keep a running total of hours worked from sheet one, and milage driven, material expenses, and reimbursement from sheet 2, listed on sheet 3. can this be done? -- -Brian-H- ------------------------------------------------------------------------ -Brian-H-'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5665 View this thread: http://www.excelforum.com/showthread.php?threadid=465762 Brian, Lets say that sheet1 cell a1 contains (hours) 45 and that sheet2 cell a2 contains (expenses...

How can I keep a running total of expenses in Excel?
I post on one sheet, but I would like to keep a running total on a separate sheet. Can anyone help me? ...

Total Return YTD
Does anyone know the detail behind the calcualtion for Total Retrun - YTD. It says Curr MKT Value less Beg Mkt Value divided by Beg Mkt Value. But mine is not coming out right. Any insight would be great. In microsoft.public.money, JSJ wrote: >Does anyone know the detail behind the calcualtion for >Total Retrun - YTD. It says Curr MKT Value less Beg Mkt >Value divided by Beg Mkt Value. But mine is not coming out >right. Any insight would be great. Did your holdings in the security change since the beginning of the year? That would affect the calculation. ...

Sorting sheets by name
Is there a possibility to put sheets inside of a workbook in alphabet order? I can't find that anywhere. It would be quite useful if one would have a large number of sheets named after different people. Thanks! There is no built-in way to do this. For a VBA macro to do it, see www.cpearson.com/excel/sortws.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Eternity" <zabo@ravi.com> wrote in message news:bu7c0h$dtm$1@bagan.srce.hr... > Is there a possibility to put sheets inside of a workbook in alphabet orde...

How do I find the total number of the same number/letter in a row
I am not trying to find the sum of numbers but want to evaluate how many number 1s, 2s etc that I have without having to count manually. Also would like to do the same with letters. Any suggestions would be much appreciated. =COUNTIF(2:2,1) =COUNTIF(2:2,"A") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Anne-Marie" <Anne-Marie@discussions.microsoft.com> wrote in message news:12225659-9167-438F-A8F4-A6B227F4A677@microsoft.com... > I am not trying to find the sum of numbers but want to evaluate how many > number 1s, 2s etc th...

Rebulid Inventory Summary from Period Summary
If you fail to close the Inventory module on the correct date, all Summary values for an inventory item are summed incorrectly. I would like to see a rebuild of the summary values in an item be rebuilt form the period summary that is correct. ---------------- 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&...

Problem with sheets that mirror each other
I'm trying to setup that mirror each other at all times. I am currently handling "Worksheet_Change()" in my sheets, so any time one sheet gets modified, I am trying to copy the contents of one sheet to the other. The shortened code looks as follows: ' ' Sheet 1 ' Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value End Sub ' ' Sheet 2 ' 'Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0,...

SOP Summary Breakdown report vs SOP Summary Breakdown Reprint repo
Hi, My client has just upgraded to vs10 and they noticed that the SOP Summary Breakdown report now prints at a document level instead of at an account level as the SOP Summary Breakdown Reprint Report does. In vs8 it apparantly worked the same as the SOP Summary Breakdown Report. I have had a look and the report uses the SOP Distribution temp table, whereas the SOP Summary Breakdown Reprint Report using the SOle.P Distribution Open and History table. I tried adding a relation to the Account Master table from the SOP Distribution temp, but this then just corrupts the report and caus...

Group and ungroup protected sheets
I need help with grouping and ungrouping protected sheets. I have 3 sheets in a workbook that must be protected. With protection on I can't use the group and ungroup function. Is there a solution for this ? If anyone could help that would be great. -- Regards Bob. Do you mean grouping as in Data|Group (outlining) or grouping worksheets (click on one worksheet tab, then ctrl-click on subsequent tabs)??? If you meant the Data|Group (or even Data|Subtotals).... If you already have the outline applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Expl...

Security for viewing Excel rows or sheets
I am working on creating a kind of gradebook to use online. I'd like to be able to enter student grades and then give each of them a code to allow them to view only their own grades. I've tried protecting cell ranges, but that only stops users from changing the cells. I've also tried putting one student's grades per sheet, but I can't seem to find a way to get Excel to ask for a password when navigating to that sheet. Here's what I'd like to do: Student opens workbook, and there's a cover sheet with instructions. From there, they navigate to either a single sh...

Filtering for multiple sheets
I need to filter out information from 1 sheet to another to take out duplication. Does anyone have a formula that I can use?? I need it to sort out 2 columns of information. Thanks for the help! Maybe you could use data|filter|advanced filter. Debra Dalgleish has some notes at: http://contextures.com/xladvfilter01.html#FilterUR JVDM wrote: > > I need to filter out information from 1 sheet to another to take out > duplication. Does anyone have a formula that I can use?? I need it to sort > out 2 columns of information. Thanks for the help! -- Dave Peterson ...

Copy cell values across separate sheets
Hi I currently have a Main sheet with a list of cell values in Col A e.g ABC, ADF, AVD etc. I want to copy these to cell A1 in sequential sheets in the workbook. Any ideas? Thanks, Bertie. -- claytorm ------------------------------------------------------------------------ claytorm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11610 View this thread: http://www.excelforum.com/showthread.php?threadid=382352 one: copy the cells two: group the sheets you want to paste to by holding control button and clicking onto the sheet tabs three: p...

Delete the sheets in the current workbook which are not have colored in the tab
i need a vba code to delete all the sheets whose sheet name are not coloured ....... Sub DelNonColouredTabs() Dim i As Long Dim s As String Dim shot As Object If Val(Application.Version) <= 9 Then MsgBox "Coloured tabs N/A in this Excel version" Exit Sub End If ReDim arrNames(1 To ActiveWorkbook.Sheets.Count) For Each sht In ActiveWorkbook.Sheets If VarType(sht.Tab.Color) = vbBoolean Then i = i + 1 arrNames(i) = sht.Name End If Next If i = ActiveWorkbook.Sheets.Count Then ...

Tender Summary report
The tender summary report, by definition, should give a summary of all tenders taken for a given time period. The report in RMS, however, joins the TenderEntry table to the Transaction table, and consequently only shows tender information for sales transactions. Payments to Account and deposits are excluded from this report. The result is a partial tender summary report that doesn't give a clear picture of all the tenders taken in a given time period. Does anyone know of a way to create a comprehensive tender report that will provide the details of ALL cash, checks, credit cards...

Jump from cell to new sheet?
Is there a way to click on a cell to cause a specified sheet to open? What I want to do is put a thumbnail image in a cell and then have the full image in another sheet; then when I click on the thumbnail, the other sheet will open and the full image will be displayed. Excel 2003, SP3 Windows XP Pro, SP2 Thanks, Don Culp On Aug 7, 8:04=A0am, "Don Culp" <dc...@krell-engineering.com> wrote: > Is there a way to click on a cell to cause a specified sheet to open? Wha= t I > want to do is put a thumbnail image in a cell and then have the full imag= e > in another sh...

F9 vs. Calculate Sheet
Folks: I know that I can configure my spreadsheet to manually "re-calculate" by doing: TOOLS >> Options >> Calculation Tab >>. * What is the difference between "Calculate Now(F9) " and "Calculate Sheet" ? Thanks, John. F9 (calculate now) will calculate the workbook while shift+f9 (calculate worksheet) just calculate the worksheet One extra note: suro is right, but if there are too many dependencies then trying to calculate a range or a sheet will be escalated by Excel to the workbook. There's an excellent MSDN artic...

Replace All within selection does all sheet in 2002
Is there a way to use the "Replace All" command on a selection of cells and not the whole sheet. I'm sure that this used to be possible on eralier versions of Excel. The "Find All" command give the correct cells in the results box. ...

Active Server Pages to Excel Sheet
I am generating a Report from my Access DB using Active Server Pages (thru ADO Connection) displaying on my browser (IE), Then i am sending the report to an Excel Spreadsheet (Using a Hyperlink). I have a column which is Numaric Datatype (like 4.95)and in the Excel spreadsheet it is showing as 5 (NOT 4.95) and i need to display as it is in my Access DB which is 4.95. How can i prevent "Rounding" here and i checked the "Precision as displayed" option but still it is NOT working. Help me Just a stab in the dark. Is your cell format set to 2 decimal places. In other w...

Charts in each sheet to Powerpoint
Hi everybody, I have created an Excel file with multiple charts, each of them is on a single sheet. I'm trying to create a macro to copy each slide, paste it on a powerpoint slide, get to the next sheet, to copy the slide, paste it on the next powerpoint slide, and so on till the end. Here's the code. I'm stuck when I try to copy the chart. Does anyone know a simple way to achieve that ? Thanks Sub GraphPowerpoint() Dim PPApp As PowerPoint.Application Dim PPPres As PowerPoint.Presentation Dim PPSlide As PowerPoint.Slide Dim PresentationFileName As Variant ...

interesting problem with property sheet
Hello! I have a interesting question with properety sheets. I have a Doc class inside my app. Inside this class I have a pointer to a database. I would like to have this pointer inside the property sheet as well. It's ok to have the pointer inside the property sheet, but not inside the pages. Why. What am i missing. Here is what I did. I included the header file to my doc class for the pages inside the property sheet. I created a function inside the first page. That received the pointer to the Doc class. What I figured out was that the pointer value changed from one hex value to anoth...

Add Button to a sheet
I would like to add a button to a sheet (not toolbar) to do a sort on a selection of data. I have created the macro I wish to use, I would like to attached the Marco to a button. Can this be done , if so how. PR view>toolbars>forms>click on the button>cursor becomes arrow>size in area desired>click x on forms menu. Better to put on your toolbar right click toolbar>customize>forms>grab button>drag to toolbar>use from there I usually use a shape for my macro buttons. -- Don Guillett SalesAid Software donaldb@281.com "PR" <paul.raeburn@ntlw...

Pivot table across 8 sheets
Afternoon, I have a spreadsheet which contains 8 sheets, the same layout however for different customers as describe in the sheet tab title. I want to create a pivot table which source's all of these 8 pages so it has the Sheet title in the Row, the Date etc in the column and then Sum of cost as the data. Anyone know a way of doing the above, I know I can if I copy and paste all sheets into one sheet and pivot from that but I thought there would be a more efficient way of doing this. Many Thanks Adam ...

Summary Sheet Totals
I have a workbook that tallys values from 30 or so separate sheets. I there an easy way to have a summary sheet that looks at specific cell on each sheet? Right now, I have to do a simple SUM function where manually click on each cell on each sheet I want in the total. I' thinking there is a way to do this with a formula/array. Thanks, Gar -- gwhit ----------------------------------------------------------------------- gwhite's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2507 View this thread: http://www.excelforum.com/showthread.php?threadid=38589 ...