#### Calculating Month to Date on a Summary Sheet

```Good Morning.  I have a summary sheet for an entire month of data, and 31 seperate tabbed sheets for each day of the month.  Is there a quick way to set up the month to date summary 'totals' in each cell?  Currently, I am holding control and going into each cell on 31 sheets after =sum
I'm sure there is a quick way to calculate, and I hope so, as I have about 15 cells that I would like to calculate on the summary page
Any help would be greatly appreciated!!
Thank you

```
 0
anonymous (74722)
5/23/2004 2:51:02 PM
excel.misc 78881 articles. 5 followers.

4 Replies
399 Views

Similar Articles

[PageSpeed] 15

```If the cells are always the same, type =sum(
select the first sheet, hold down shift
and click the last sheet tab, then select the cell(s), finish by pressing
enter
will return for instance

=SUM('1:31'!A1:A6)

--

For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

news:34064E75-59AA-419C-90EB-405AB7D7B8D0@microsoft.com...
> Good Morning.  I have a summary sheet for an entire month of data, and 31
seperate tabbed sheets for each day of the month.  Is there a quick way to
set up the month to date summary 'totals' in each cell?  Currently, I am
holding control and going into each cell on 31 sheets after =sum(
> I'm sure there is a quick way to calculate, and I hope so, as I have about
15 cells that I would like to calculate on the summary page.
> Any help would be greatly appreciated!!!
> Thank you,
>

```
 0
terre081 (3244)
5/23/2004 3:03:54 PM
```Bradley,

Is it not as simple as

=SUM('Day 1:Day 31'!A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

news:34064E75-59AA-419C-90EB-405AB7D7B8D0@microsoft.com...
> Good Morning.  I have a summary sheet for an entire month of data, and 31
seperate tabbed sheets for each day of the month.  Is there a quick way to
set up the month to date summary 'totals' in each cell?  Currently, I am
holding control and going into each cell on 31 sheets after =sum(
> I'm sure there is a quick way to calculate, and I hope so, as I have about
15 cells that I would like to calculate on the summary page.
> Any help would be greatly appreciated!!!
> Thank you,
>

```
 0
bob.phillips1 (6510)
5/23/2004 3:03:55 PM
```Hi
try
=SUM('sheet1:sheet31'!A1:A100)

--
Regards
Frank Kabel
Frankfurt, Germany

> Good Morning.  I have a summary sheet for an entire month of data,
> and 31 seperate tabbed sheets for each day of the month.  Is there a
> quick way to set up the month to date summary 'totals' in each cell?
> Currently, I am holding control and going into each cell on 31 sheets
> after =sum( I'm sure there is a quick way to calculate, and I hope
> so, as I have about 15 cells that I would like to calculate on the
> summary page.
> Any help would be greatly appreciated!!!
> Thank you,

```
 0
frank.kabel (11126)
5/23/2004 3:07:36 PM
```On top of all the other responses, you may want to insert two "dummy"
worksheets--one to the right of all the worksheets to be included and one to the
left.

Call the Start and End.  Then you can use:

=sum('start:end'!a1)

And you can drag sheets in between these two (or out of) and not have to worry

>
> Good Morning.  I have a summary sheet for an entire month of data, and 31 seperate tabbed sheets for each day of the month.  Is there a quick way to set up the month to date summary 'totals' in each cell?  Currently, I am holding control and going into each cell on 31 sheets after =sum(
> I'm sure there is a quick way to calculate, and I hope so, as I have about 15 cells that I would like to calculate on the summary page.
> Any help would be greatly appreciated!!!
> Thank you,

--

Dave Peterson
ec35720@msn.com
```
 0
ec35720 (10082)
5/23/2004 4:23:48 PM

Similar Artilces:

Cannot set task start or end date!!!
For some reason I can no longer set up task start or end dates. When I open the drop down list, it will not stay open when I release the left click. I can hold down left click and choose a date, but if I want to choose the "today" or "none" radio buttons, that will not work. Any ideas?? I am also using the Xobni program with Outlook, but do not have this problem with the same config. at home. I can set the status, priority and % complete fields with absolutely no problem. Thanks Howard Does it work in Outlook Safe Mode? Start-> Run; outlook.ex...

quick charting question for similar charts on multiple sheets
Hi, just wondering if there is a quick solution to my problem. I have several charts, all virtually identical except for the data values in them, and i have one chart per sheet, and about 100 sheets. The problem is that some charts are positioned slightly different on their respective sheets then others on other sheets, although some are identical from sheet to sheet aswell. Just wondering if there is a quick way to select them all, line them up identically on each sheet, with all the same size/formating etc.? Thanks. -- Boon8888 -----------------------------------------------------...

Calculate home equity in Money?
I have an asset account for my house. It is tied to a mortgage loan account. I also made a down payment from my checking account. How do I calculate the equity in the house using Money since it has appreciated and I have paid down some of the mortgage? Job #1: figure out what your house is worth. Nothing internal to Money will provide much help with that. Job #2, with a calculator, subtract sum of the current balance of all outstanding loans from the value determined in step 1. The remainder is the equity. "Taylor" <123@456.com> wrote in message news:Yp76g.184\$Qq.73@...

Formatting a date in client report
How do I format a date field in a client side Microsoft report (rdlc)? I'm using VS2008, 9.0.30729.1 sp Windows forms project. Unformatted, the dates in my report are displayed like this: 11/19/09 00:00:00 I want to display just the date, not the zeroes. If I enter an expression in the value property for the report textbox like this: =Format(Fields!StartDate.Value, "D") (as shown in http://msdn.microsoft.com/en-us/library/ms251668.aspx) the report will display "D" instead of the formatted date. The same thing happens with other format string...

date format conversion
I have a date of 1/5/2006, it needs to be 01/05/2006. I ma having a hard time getting hte leading 0's where needed. Appreciate any help! -- Buck That sounds like a matter of FORMAT. The date itself, as 'numerical' value sounds fine. You can force a format (to display a STRING that represents a date, but *is* a string, not a date_time value) with the use of Format function: ? Format( #1/2/3# , "mm\/dd\/yyyy") 01/02/2003 Hoping it may help, Vanderghast, Access MVP "buckpeace" <buckpeace@discussions.microsoft.com> wrote in message news:01C568...

How do I get rid of the PERSONAL.XLS sheet from a workbook?
Everytime I want to "X" out of excel, I always have to also close the PERSONAL.XLS sheet Normally the Personal file is hidden If you use Windows | Hide then you will never need to close it again OR you could locate it in the XLSTART folder and just delete it - hope it has no macros that you need to use best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Arencia" <Arencia@discussions.microsoft.com> wrote in message news:9E91F069-881E-4D83-A224-8B4E78365949@microsoft.com... > Everytime I want to "X" out of...

how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet.
how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet. In VBA Editor, create a module. Paste this there Option Explicit Dim X As New EventClass Public Sub Auto_Open() Set X.App = Application End Sub Then, insert a Class Module, rename it to EventClass, and paste this there: Option Explicit Public WithEvents App As Application Private Sub App_SheetActivate(ByVal Sh As Object) Sheets(Sh.Name).Select ActiveCell.SpecialCells(xlLastCell).Select End Sub It should work (XL2000). "Daniel" <...

calculation
Hi, I work with 3 large excel files (5000 KB). File A is my summary and is linked with the 2 others. I use a lot of reference cells and I discovered that some reference cells does not work. Some cells do not calculate automatically. I saved everything on the C drive in order to minimize the lenght of the formula but I still have problems. Any idea what else I could do? Thanks in advance. Can you share some examples of the cells that don't calculate? What is the formula in one of them? What's different between that cell and one that does recalculate? -- Regards, Tu...

Date when I last change cell in a row?
Please, Help. In my database, every row represents a separate client. I wonder if it is possible to input the date when I last changed any value in a particular row. This way I will know when exactly I last updated the information for client in row 4, 5 etc. I don't need the date when the document was last saved. Please, let me know if it can be done in Excel. Thank you, Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Row = 5 Then Me.Range("H1").Value = Date ...

Calculate height of listbox?
Is there a forumla to calculate the needed height of a listbox in order to contain a given number of lines of a given point size? Not without a bunch of GDI API work. The AutoSizeTextBox solution on my site shows you how. A far simpler solution, that is almost as accurate would be to use code/logic like: Assumes ListBox control named lbHeight Assumes TestBox control named Text11 Assumes CommandButton named CmdSize Enter a desired number of rows value in Text11 and then click on the CommandButton. Private Sub cmdSize_Click() On Error GoTo Err_cmdSize_Click Dim x As Integer ' conv...

Calculate Formulas in Highlighted Cells Only--not whole sheet
In office 97 I was able to recalc only the cells highlighted (control + L) as opposed to the entire sheet or workbook. In Excel 2002, how do I recalc only the cells I have highlighted? Thanks I don't recall this shortcut in xl (any version). I'm guessing that you had a macro that did something special. In xl2002, you could use a macro like this (assigned to ctrl-shift-l) Option Explicit Sub testme() Selection.Calculate End Sub But I think I've read posts that calculating a single range can be bad--it can screw up the calculation dependencies. I think that Charles Will...

Excel Sheet
Good morning, Need help with: Code to delete all rows in Column A below active cell Turn off warnings in a macro, specifically I am copying a spreadsheet to a new one, and close original, don't want pop up box to ask if file needs to be saved etc. Thanks Cristina Sub DeleteRows() Application.DisplayAlerts = False Range(ActiveCell.Offset(1, 0), "A65536").EntireRow.Delete Application.DisplayAlerts = True End Sub -- Best Regards, Luke M "Christina" <Christina@discussions.microsoft.com> wrote in message news:D5D0E04F-8261-4809-B93...

Time calculation #3
How can I calculate average time using hundredths of a second? For example: 1:43:23,88 is 1 hour, 43 minutes, 23 seconds and 88 hundredths of a second. If I want to divide this with, say 20, it's not so difficult to do by excluding the hundredths. But how is it done including it? (I want to use it to calculate average lap time running tracks). // Roger (Sorry for my poor english...) Roger, If you haven't done so, format it to include hundredths. Format - Cells - Number - Custom: hh:mm:ss,00 hh:mm:ss,000 -- Earl Kiosterud mvpearl omitthisword at verizon period net --------...

if meet critertia then countif(a1:a10, between dates)
I hv a table of info Col A = location Col B = Due Dates if meet location, then count the number of due dates that fall in Oct or nov etc.... =SUMPRODUCT(--(A1:A10="location"),--(COUNTIF(b1:b10,DATEDIF("2005/10/1","2005/10/31","yd")))) Col A Col B Location Due Dates USA 10/10/05 Canada 5/1/06 UK 31/12/05 Canada 2/1/06 S. America 30/10/05 Result: oct 05 nov 05 dec 05 jan 06 canada 0 0 ...

trying to return data with only the latest date
when i run this i get data for the last day. i'm trying to get it to return only for the latest date. for example the instance name column contains all the drive letters. i would like it to return one row for each machinename and drive letter. tried adding distinct but didn't work with free_hd_space_cte as ( select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, b.countervalue, convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead from counterdetails a inner join counterdata b on a.counterid = b.counter...

How to automaticaly populate another date
I know how to do this in excel but can't figure it out in access. I want it so that when a users inputs data into one field the date (of when he put in the data) populates another field.Please helpThanks! Hi GregThe control that the user is entering data into has an AfterUpdate event which means that you can tell your system to do "something" after the field is updated.Open the form in design view and select the field that will have data entered into it.Right click and open the properties box.In the event column select AfterUpdateClic build option (...) and select codeYou will s...

Need an Through/End date for the Historical Stock Status Report
Our customer wants to print a report for a prior month that shows the beginning quantity on hand for an item, all transactions for the month and the ending QTY balance. Put another way, the customer says "I want to know what inventory I started the month with, what happenned to it, and what I ended with." This could be accomplished by adding an end date option to the Historical Stock Status Report so we could run the As Of Date for the beginning of a prior month and the end date as the end of the prior month. ---------------- This post is a suggestion for Microsoft, and Micro...

Converting Text Values to Dates
Is there a way to convert a column of text, date values (entered as 81096, for example) to valid date format, 8/10/96? I could not get the DATEVALUE function to do this. Hi! Select the range of cells in question. Goto Data>Text to Columns Click NEXT twice Select Column Data Format: DATE. Then choose the format from the drop down Finish Biff "Frank Winston" <FrankWinston@discussions.microsoft.com> wrote in message news:7CAF0A61-E8A7-4D18-B035-0D475E9A858C@microsoft.com... > Is there a way to convert a column of text, date values (entered as 81096, > for example...

Summary Report
I have a summary report where I need a count of patients with their first visit date within a specific date range, but I also need a count of those same patients with one of four different outcomes during that same date range (delivered, miscarriage, transferred out, or no outcome withing the date range). Is this possible? Yes! Create a query that includes PatientID, FirstVisitDate and Outcome. Use the Between ... And construct in the FirstVisitDate field criteria. Click on the Sigma (looks like capital E) button in the menu at the top of the screen. Under PatientID, change ...

Excel Date Format of MMM/YY
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I enter a date in the format of MMM/YY it saves ok but if you then look in tools and calculator I have found that it creates a formula of 2004 and when you copy this over to a Windows based PC it displays incorrect dates. How do I turn off this apparent formula creation. I live in UK and have settings to UK Don't let the info in the Calculator mislead you. It's simply confirming that the workbook you're in is employing the 1904 Date System which is the default in Mac Excel. Dates in Mac Exce...

SumProduct over multiple sheets
Hello The below formula does one sheet. I have ten. Is there a way to have it sum all ten for me that does not require a long formula Thanks for your help =SUMPRODUCT(('Line7'!\$C\$15:\$C\$114=C7)*('Line7'!\$G\$15:\$G\$114='Numbers'!\$S\$9)*('Line7'!\$AF\$15:\$AF\$114)) I'd use 10 different formulas. Then sum them in an eleventh formula. In fact, if you put each of those formulas in a dedicated cell (say A1 of each individual sheet), you could use a formula like: =sum('line1:line10!a1) As long as all the other sheets are between line1 and line10. Jack wr...

Changing the calculation order
Can the order that Excel recalculates be changed to start fro the lower right? Excel automatically adjusts its final calculation order depending on the dependency chain in such a way as to get the correct result regardless of formula sequence. Why would you want to change this? Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "A Process Modeler" <A Process Modeler@discussions.microsoft.com> wrote in message news:4D17FF7C-D763-4FAB-980C-463DB8A75FCA@microsoft.com... > Can the order that Excel recalculates be changed to ...

Creating Data Tables off-sheet
Does anyone know how to create data tables in a sheet other than the sheet containing the input data? Someone showed me the trick a long time ago and it has escaped my mind. Thanks in advance for any clues or complete solution. Cheers! Atem Do you mean that your data is in sheet1 and you want to put the data table containing some of that data in sheet2? If so, you can just reference sheet1 in your formulas in sheet2. =sheet2!A50 =sheet2!A50-sheet2!B50 etc etc Lacty wrote: > Does anyone know how to create data tables in a sheet other than the > sheet containing the input data? Some...

If statements that look for a date range
Help!!! I am trying to look at an ActiveCell and determine if it is in a certain date range from the current date to 180 days prior and then complete an action. Any suggestions? Brian, You cannot look at the activecell with a formula as that would be circular. You could use this in say B1 =IF(AND(A1>=TODAY()-180,A1<=TODAY()), "do something", "do something else") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Brian Roberson" <Brian.Roberson@bellsouth.com>...

Help needed on date matching and cell reference.
Hi, I need a formula to return the value of a cell from a nearby column, based on a matching of the months and years within a range of dates to the months and years within a given date. Here is the setup: Cell BA58 contains the given date. Column AP, starting in Cell AP59 and going down to AP2000 contains the array of dates that need to be evaluated to find which date matches the given date in Cell BA58 Column AR, starting in Cell AR59 and going down to AR2000 contains the array of numbers from which the result must be displayed. If a match is found between the given date in...