Dynamic Sum Function (number Of Sheets Can Vary From Time To Time)
I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.
Any idea about a dynamic sum function that will do this, in combination with VBA?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
IF Function To Compare Specific Time To Time In Cell
I am looking for a formula to change a condition based on the time of day. Essentially, before 1pm I would like the formula to be: =((I12/100)*25)/D12 After 1pm I would like the formula to be =((I12/100)*25)/B12 I tried various ways with "=if(now()> ..." formulas to no avail because of the way Excel handles Now().
View Replies!
View Related
Add This Number To The Start Time, Factor In Break Minutes And Get To The Projected Completion Time
I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!
View Replies!
View Related
Count Number Of Full Time Periods Within A Time Period
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...) I also have numerous set time periods that i'm interested in.. (about a dozen or so) for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00 What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed. So if my timestamps were Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2 and if my timestamps were Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps. and if my timestamps were: Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
View Replies!
View Related
Index Function (isolate One Number At A Time And Evaluate Usage)
The value that is returned is off by 2 rows everytime. When I evaluate the formula, it shows the correct row just before the indexing function does it's thing. I have a cell phone bill for 20 or so phones and am trying to isolate one number at a time and evaluate usage. The first sheet is my data, the second is sheet ("Breakdown") is where I enter the number in A2 that I want to look at. When I do, it misses the first 2 rows and picks up 2 extra from the following phone number.
View Replies!
View Related
Convert Number To Time & Decimal Time
I need to separate a list of times from a telecommunications bill. I have 170,000 line of data. The Times are represented as below. eg 46 46 Sec 59 59 Sec 100 1 Min 159 1 min 59 sec 200 2 mins 502 5 mins 2 sec 1256 12 mins 56 sec 3456 34 min 56 sec. How do I separate them to a decimalized per min value.
View Replies!
View Related
Range - Dynamic Use #rows & Cols Vary
i have data that will vary from item to item in the $ of rows associated and the # of columns to be dealt with. item1 Line 1 Line 2 Here i want to have a formula put and copied across X columns item2 Line 1 Line 2 other data going across in the columns Line 3 Line 4 Here i want to have a formula put and copied across X columns item3 Line 1 item4 Line 1 get my meaning?? columns involved will be known before i get to this bit of code !! as an example lets say it's 8 columns using the record macro i get this bit of code Range("E14:M14").Select Selection.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)" This is fine but i don't know what row or the columns are before i run the macro so the Range ("e14:M14").select wont really work. I've sent sometime going through MRexcell searching for tips but found nothing also googled for dynamically pasting across unknown columns this is close to my idea in my mind conceptually Range("C2:C" & LastRow) but it doesn't make the columns dynamic eg column 8 being = H and then P = 8th column on and the last with data.
View Replies!
View Related
"Time" Function To Find Time&date - ?hours
I m Trying to add some features to it and run into an issue. Here are the fields we are working with! A1= 13:30:00 17/12/2008 (in other words 1:30pm on date given) - Need to know proper cell number format!! B1= 23:00:00 (this represents 23 hours) - format for cell is [h]:mm:ss C1= Unknown function (This field is my problem!!) Answer here SHOULD be "14:30:00 16/12/2008" I need a function for C1 (using A1 and B1) that will give me the time and date 23 hours earlier or however many hours:mins:secs is specified in B1! Also need to know what number format to use for both A1 and C1
View Replies!
View Related
Display End Time Automatically Upon Entering Start Time And Time Usage
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View Replies!
View Related
Macro To Create Dynamic Time Stamp
I am have average excel knowledge with formulas and I am relatively new to the creation of Macros. I was told by my boss that he wants me to create a Macro that will palce a time stamp in the bottom left corner of the page (via the footer) that updates automatically every time a sheet is worked on. Please provide any help or assistance that can get me through this task.
View Replies!
View Related
Plot X Values At A Time In Dynamic Chart
I have over 5000 cells of data. I want to take 4 data points at a time, average them then plot them on a dynamic chart. I know how to do the dynamic chart but how can I take the average of the the first 4 numbers store it in a different cell, then the next 4 and so on...
View Replies!
View Related
Vary Columns Based On A Number
You can always add numbers together. however, how can you vary columns based on a number? for example, i can always do things like the following to change the number of rows selected in a column based on the variable numRows.
View Replies!
View Related
Time Sheets ...
I am tearing my hair out trying to find a solution to this problem. Can anyone help? I work 5 shifts each week, some are over night. What formula would I use in order to find, a) Hours worked each shift. b) Hours worked for the week. I have discovered how to calculate (eg.) 6am - 6pm, but over night (eg.) 6pm - 6am As for adding total weekly hours
View Replies!
View Related
Indirect And If Statements For Time Sheets
I am trying to do a time analysis by placing an X in a square based on the hours an employee worked during a day. Time is in 24 hour clock time in 30 minute increments. What I need to do is to populate the correct squares with an X based on the start and end times an employee worked each day. I have 20 different locations and will need to use the indirect function to point to the correct sheet and cell. For example I have store #5 and the time sheet for store number 5 is on the sheet and the information I need to return is in cells d4 and d5. Start time was 5.52 and ending time was 10.97. Based on this information I would like the closest start and ending time on the spreadsheet below to show an X.
View Replies!
View Related
Trying To Make A Time Schedual Between Two Sheets.
I am trying to make book to represent time frames from sheet 1 to sheet two. The times are at the bottom of the sheet represented by letters from the alphabet eg, A is 6:30 in the morning to 2:45 in the afternoon. B is 8:45 in the morning to 5:00 in the afternoon ect, ect . I need to have the time represented in color or maybe a diagonal line through the cell in sheet 2 where each letter and corrosponding time frames are. Eg, I have given an example of the time frame "A" in column C in sheet two. I will make a sheet for each day of the month, so all I need is an equation for row "C"(Monday the 16th) in sheet 1.
View Replies!
View Related
Time (clock) Times A Number= Very High Number?
I have time start and time end and the difference between the two times. In a third column I have profit and want to calculate the profit per hour. When I divide the profit with the time spent I get a very very high number. If I for instance type in 1 hour of work and profit 100, then I get profit per hour 2400. (profit/hours spent) On a side note I am trying to get the numbers as 24 hours and not use am or pm and in the spread sheet I get these numbers but in the box at the top where you see the formulas etc. it shows as am and pm. I have uploaded an example.
View Replies!
View Related
Sum Of Time Values
I've a field on my database table that is of "Short Time" format. I've some values like this: 0:14 0:06 0:05 I want to add the whole record, to update the record and have the value of the field to show "0:25"
View Replies!
View Related
On Time Function
I finally got an ontime code to work almost the way I want it. What it does not do correctly is necessarly copy paste information for the correct hour of the day. For example if the computer goes down it will start again copy/pasting for the next hour readings. Even if its three hours latter. The log should be blank for three hours since those hours wwere missed. My code is below, how can it be fixed to run "Capture" macro for the correct time of each day and leave the rest blank? Rich Public LastSoon Function Soon() 'As Date Dim tm As Variant tm = Now tm = tm + 1 / 24 'next hour tm = tm - Int(tm) 'time only (no date) tm = Int(tm * 24) 'on the hour tm = tm / 24 - 5 / 1440 '5 minutes before hour Soon = tm
View Replies!
View Related
Run A Macro In Multiple Sheets At The Same Time
I have to run the macro in over 75 sheets every month and they are divided in few workbooks. The workbooks have 2 summary sheets and then the sheets I need to run the macro. Can you help me to run the macro in all of these workbooks running it just once? Sub TelcoTicketsCleaning() ' ' TelcoTicketsCleaning Macro ' Macro recorded 6/13/2007 by EQUANT ' Dim lastrow As Long lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row For r = lastrow To 46 Step -1 'Remove rows with DSL, ISDN and PSTN services..................
View Replies!
View Related
Naming Sheets With Time-dependent Prefix
I have got this macro working OK but now I need to add a 2 letter prefix/suffix depending on what time it was created (am or pm). Detail: If the sheet is created between 0600hrs and 1800hrs then "DS" and likewise between 1800hrs and 0600hrs then "NS". Outcome: The sheet will then have a name like - "22 Mar DS"...code included below
View Replies!
View Related
Sum The Time Field And Vlookup
I need to sum the Time field, if the Temp Zone is = F. My problem is, as shown on the sheet, there are multiple days. As well, I need to pull the information for the totals (looking at 6 days worth of info) into another worksheet where the names (first column) are not in the same order, as some of the names are on this sheet, and some are not. I had a massively large vlookup statement, but of course, it's rather large and doesn't copy over well (when changing the days, it looses the last couple of lookups) ......
View Replies!
View Related
Graphing Time To A Sum Of Hits
I have a graph / time problem I have a program that is spitting out raw data to me that I would like to be able to put into a graph form in a efficient manner, but I’m not sure the best way to accomplish this. Here is what I’m working with: I am getting a list of time date stamps that appear as follows. 11/30/2006 6:02:25 AM 11/30/2006 6:02:36 AM 11/30/2006 6:02:40 AM and so forth. Each stamp represents a hit to a link. What I would like to be able to do is graph the number of hits per hour verse the time of day hit. Showing the time on the x & the number of hits per that hour on the y axis. I can convert the time to decimal, but I still need to find a way to get excel to graph the sum of hits per hour.
View Replies!
View Related
Sum Data For Time Intervals
I am trying to do is calculate (sum) results data for time intervals. For example, between 0500-0559hrs then 0600-0659hrs and so on. Data is collected at 1 minute intervals and may have a result of 0 or anywhere up to 30. Column A has all time data e.g. 0500,0501,0502 etc Column B has sum of instances e.g. 0,1,2,3 etc
View Replies!
View Related
Sum Range Of Time Cells
When I input time in the h:mm:ss format it add a am/pm to it. I tried formating it to remove it yet it wont go away. What I am trying to do is add up time in the G Column but all I get is 0:00:00.. I added the [h]:mm:ss format to that cell... Does this have to do with Execl adding am/pm to the time? If it is how do I stop it.
View Replies!
View Related
Time Series Function
I'm trying to calculate and average using a time frame in the day. I want to use a certain number "X" that correlates to a hour/minute in the current time. How do I write a statement to get me the correct "X" value? example: 8:00 .005 8:01 .006 8:02 .0075 8:03 .0077 8:04 .008 time now is 8:03 How do I get the .0077 in excel?
View Replies!
View Related
Adding Time Function
I want to add two times together. 14:00 (time) + 03:42 (duration) = 17:42 Is there vba code to be able to do this or an excel function? I have tried searching but strangely couldnt find anything?
View Replies!
View Related
Using Indirect Function To Sum Across Multiple Sheets
My sheets are all categorized by date, and I want to be sum individual cells on each worksheet on one final summary worksheet. For example, if cell A1 on each sheet was units sold and I wanted to see how many total units were sold between Jan-04-08 and Jan-16-08 my formula on the summary sheet would be: =SUM('Jan-04-08:Jan-16-08'!A1) But I want to be able to easily modify what dates my summary sheet shows so I tried using the function: =SUM(INDIRECT("'"&N14&":"&N15&"'!A1")) where N14 was the starting sheet and N15 was the ending sheet. It keeps giving me #REF! however and I can't figure out why and don't know if this is something I can fix.
View Replies!
View Related
Do Operation On All Sheets Except The First Sheet In The Book Every Time I Update It
I have a workbook that I would like the same operation on all sheets except the first sheet in the book every time I update it (which is once a week). I get data (currency format) for all of my employees on a weekly basis. Unfortunately this data is shown as a negative number and I need to invert these values. (So I use the formula below)... but I have to do this manually for about 35 sheets and I figure I could automate this somehow with a macro. Here's what I do usually and what I would like the macro to do automatically: I want to insert a column after column G. I would like to enter the following formula into H2: =IF(G4="u", "u", IF(G4
View Replies!
View Related
Export Multiple Sheets To Csv Based On Time
I have found numerous entries for Saving Based on Time (which I'm using) and others for Exporting to CSV. But I'm not sure how to combine them or if it will do what I really want. I have a workbook with 6 tabs (each one with a name like "CODE_MEETINGS" or "CODE_CONTACTS"), I would like to export a COPY of each of them to their own CSV file and overwrite it each time. Basically, this will get me away from using the Save Based Ontime VBA in my master workbook, as David says, "it's not a good idea and can save errors." The problems I think I would have are: 1. I want one sheet per saved CSV file (using the name of the sheet/tab) 2. I need the top row (title fields) of each sheet removed (or I need to be able to set a selection per sheet) 3. I need it to be automatic without user intervention (when I save out manually, I get prompts about multiple sheets, features not supported and overwriting). Right now my code for just autosaving is: workbook Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.OnTime dTime, "SaveMe", , False End Sub Private Sub Workbook_Open() Application.OnTime Now + TimeValue("00:15:00"), "SaveMe" End Sub..................
View Replies!
View Related
Copy Row If DATE & Time In Cell Is Between Time Span
I have a problem regarding sorting data having date and also time within a single cell. Example data (I have written it as code to preserve formatting) A B C D E F 12/5/2008 02:072/5/2008 06:0128804833363 22/5/2008 18:012/5/2008 18:0599271297 Column B is start date and time whereas Column C is end date and time. My aim is to cut and paste the whole row automatically to Sheet2 if the time is within 2AM to 8AM else leave as it is.Also I don't know anything about VBA Script.
View Replies!
View Related
Automatically Change Cell (With Time) Color After Time Period Has Passed
I have a protected worksheet. Users wish to be able to track changes in the input cells. The suggested approach for this is to temporarily disable sheet protection and allow them to change the font color, then protect afterwards. What I would like to do is: i) check whether they are in an input cell ii) if so, then prompt the user with the 'Font Color' dialog box iii) apply the font color selected to the input cell I'm struggling to find the dialog box I need. I can launch the one to change the interior color, no problem (Application.Dialogs(xlDialogPatterns).Show). But that's no use to me, I just want a color palette that specifically relates to the Font Color
View Replies!
View Related
VBA Code To See SUM And AVERAGE At The Same Time
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rng As Range Set rng = ActiveCell.CurrentRegion Application.StatusBar = _ "Current SUM is: " & _ Application.WorksheetFunction.Sum(rng) & " " & _ "Current AVERAGE is: " & _ Application.WorksheetFunction.Average(rng) End Sub I now have the following problems: I get a errot when i select cells with no number Run-time error 1004 Unable to get Average property of the WorksheetFunction class The sum and average stay in the status bar. The code gets confused after i selected different columns. I would like the code to work only when i select a range of cells. Is it possible to move the text in the status bar more to the right?
View Replies!
View Related
Sum By Time & Date Criteria
I have a spreadsheet with date/time in a column. Each date/time has a value associated with it in another column. What I am looking to do is to sum the values that occur in an hour or less and have that summed value populate a cell in the proceeding column. Not concerned about values that are associated with durations greater than 1 hour. Date/Time Value 7/27/07 5:33 PM 0.00 7/27/07 7:51 PM 0.04 7/27/07 8:28 PM 0.04 7/27/07 11:08 PM 0.00 7/27/07 11:57 PM 0.08 7/28/07 12:11 AM 0.04 7/28/07 12:23 AM 0.06 7/28/07 12:50 AM 0.04 7/28/07 2:46 AM 0.04 7/28/07 5:30 AM 0.04 7/28/07 5:27 PM 0.04
View Replies!
View Related
Skip Weekends Adding Time To Date & Time
I need a formula to add just the time to ' date and time', ignoring weekends. eg: Fri 24-Aug-07 10:52 is the date and time 28:48:00 is the time If I add the time to 'date and time', result is coming as Sat 25-Aug-07 15:40 But it should come as Mon 27-Aug-07 15:40 (hence ignoring weekend)
View Replies!
View Related
Delete Date & Time Cells Based On Time
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too. I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have. Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......
View Replies!
View Related
|
TRACKER
