Create Subgroups For The Weeks Within The Month Groups
Jan 8, 2009
I have a spreadsheet with many workbooks. Each workbook contains sale figures for all products for each product range over a period of time. There is one workbook that totals the figures from other workbooks.
This spreadsheet is updated by another department on a weekly basis with new figures added to each workbook (products within each product range are also likely to increase).
Currently, the data in each work book is grouped by months (with a row summarising the month's figures). I can create graphs to visualise monthly performance, but also need to have a weekly performance graph/chart. Thus, the data also needs to be grouped by weeks.
I have tried creating subgroups for the weeks within the month groups, but it seems impossible (?!) to do when a week subgroup comes right in the middle of a month summary (e.g. Dec 2008 Summary comes in between Week 29 Dec 2008 - 04 Jan 2009). I have attached an example spreadsheet in case my description was too convoluted!
I need a formula that will tell me how many weeks there are in a month. If cell A2 had the month and cell B2 had the year then I would like to return 4.....
I have a weekly forecast for what will be sold for the upcoming year and want to sum the quantity by months. For example, 1/6/07 sell 351 units, 1/13/07 sell 315, 1/20/07 sell 1,165, and 1/27/07 sell 328 units so Jan would return 2,159. To avoid future user error, I'd like to sum by month without the basic sum( range) formula and have tried the following SUMIF statement:
=SUMIF(Weeks,"=Jan*",Widget_Qty)
That returned zero. I formated my Weeks to look like "6-Jan-07" so I thought that the month name could be used as a text but this was also wrong. I also tried changing the month value from text, i.e. Jan to a date value displayed as a "mmm" but failed again.
I have a workbook that needs to display the relevent weeks in month, the dates would be the start and end dates of each wekk (monday to Friday) but also need to take into consideration, partial weeks i.e. only a wednesday, thursday and friday at the start of the month given where the first available date falss on.
see the attached sheet for reference, I am looking to do this with as few formulas.
It also needs to show a list of monday only dates for the selected month.
I am working on this "maintenance schedule" that is currently sorted out into tabs that are organized into the 12 months, jan, feb and so on.
I have to write either a command or a program that would be efficient enough to easily convert these monthly data in weekly ones.
So the command would have to convert those "jan, feb, mar... ...dec" tabs into "week 1, week 2, week 3... ...week 52". (prefably by linking them up some how)
I have a spreadsheet that I am using to try to track Project Man Hours. What happens is:
I enter in the amount of weeks a Project Phase is in and when the Project Starts. I also enter in how many "Men" we are going to need.
i.e. Project Start - January 2009 Phase 1 - 4 weeks with 1.5 Men Phase 2 - 20 weeks with 2 Men.
What I want to happen is: Excel then adds that data to a chart showing EACH MONTH and the amount of men needed for each phase.
Project 1 showing a line chart with January-December and the lines correlating with the amount of "Men" needed each week based on the Start Date and Sequential Phase Week Amounts.....
I have used sumif statement to set up a value by week and then used the drop down list cell reference to display what i want ie week 25 data
What I would like is to display the 8 previous weeks and then use this in a graph, so every time i change the drop down week I see all the data showing the current week and previous weeks.
Now the problem is which formula do i use to to identify previous weeks
I am trying to simplify a type of gannt chart bar across a spreadsheet. The spreadsheet has dates across row 3 that are calculated from the first cell F3 with =F3+7 to populate the rest of the row with dates. I want to be able to input a start date in D4, then all the other titles in column A. This start date will start the coloured bar at that start date in the chart, I will then copy/drag that start date cell for however number of weeks for the duration and I want the finish date to auto populate E4 with the finish date and also auto populate the numbers of weeks in C4 for that bar.
Excel 2007ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANA OAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZ CACBCCCDCECFCG1D42342McArthur River Mine Power
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
I regularly build a pivot table using VBA. I now want to group countries into regions within the pivot table, and I want to automate that using vba.
For simplicity, assume I have the countries Germany, Netherlands, Belgium, Luxembourg, Norway and Sweden; and I want to create the Groups BeNeLux and Scandinavia. (In reality, I have 150 countries, one group of 10 countries, 2 countries I want to show individually, and the rest of the world I want to group together)
I know I could select the cells using something like cells(d2:f2).group, but that seems wrong....
I have attached a file showing how far I can get with vba and where i want to get.
I have also tried things like ".DataRange.cells(2).group by:=3" but that never led to anything....
I have a workbook that lists system analysts and information on the systems they're responsible for. I would like to have an Excel macro that will:
1. Create and name a new worksheet for each UNIQUE value in the 'Name' column (new worksheet for each analyst) 2. Copy their system info to each respective worksheet
I need to make a macro that creates a specified number of graphs depending on the file's number of data sets. I know the number of sets that are in the data, and I know the number of data points that were taken. Here is what I have:
Sub Graphs() Dim Startpoint As Integer Dim Endpoint As Integer Dim count As Integer Dim xStart As String Dim xEnd As String Dim NumberSets As Integer Dim yStart As String Dim yEnd As String Dim DataSet As Integer Dim Data Startpoint = 11 'The first set always starts in row 11 Endpoint = Range("L4").Value + 10 'Thefirst set always ends after the value of L4+10 NumberSets = Range("L7").Value 'number of times I need the loop to work count = 1..........................
I need to create a function that numbers rows with respect to data groups in a column (column labeled "Type" in this example). The result would be that shown in column A in this table.
How do I write the function? #TypeName Date 1CarsJohn1/12/2008 2CarsJane11/10/2007 3CarsMary11/2/2004 4CarsBob12/7/2003 1TrucksMike12/12/2007 2TrucksSandy1/3/2007 3TrucksDale12/14/2006 4TrucksVince4/8/2005
In sheet1, (B2 & C2) are the numbers entered by the user. After they entered, it will create rows of table according to the number of groups they entered on the respective sheets.
I have one main Excel file with information (in example file - columns A & B). I want to create 4 other Excel files that will draw from the main one (in example file - columns D, F, H, & J). I want the information in Excel file A, B, C, and D to be continuous information that matches the main file. For example, the letter A matches up with numbers 1,3,4,10,11,13,15,17, and 19 in the main file. When I open Excel file A, I want a continuous list of these numbers. And I want this for all 4 Excel files which draw from the main one.
Formula which can calculate how many days in a month and create a column for each day. Columns for day 28, 29, 30 or 31 also need the formulas in columns 1 to 27 copied over automatically. [sheet name "LARSheet1 to 1" ]
I'm trying to build "Leave, Attendance Management" system using Excel. I am about 40% there so far.
The basic structure of the workbook is as follows:
12x main monthly "Leave Attendance Record" worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets asLARSheet1 to LARSheet12 ]. 1 x worksheet which holds "data validation" list andvlook up info. ["FormulaListSheet"] 1 x worksheet acting as a database of staff details andpay numbers. ["StaffdBaseSheet"] 1 x summary page which shows staff reaming leaveallocation. ["SummaryLeaveSheet"]
What I'm having problems with at the moment is as follows:
A formula or a macro which works out how many days are present in a given month [starting date of month located in CELL B10].
Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).
I would like it to work like this:
When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.
I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.
I'm trying to make a simple program to allow me to total the amount of air filters I need to purchase for a given month. I am entering the amount of filters for each property and the frequency of replacement ie. monthly, quarterly, one month out of the year, etc. I would like to enter a month, ie. 2 for february and have the program list the quanty of each filter that I need to purchase for the given month. See sample attachment.
I have to generate daily report in which data is fiscal week format. We need to show Fiscal week in actual month and day format. create a macro so that for every fiscal week anywhere in the sheet, it automatically converts fiscal week to month and days of the week
For example: WK 1 OCT FY2013 get converted to Oct 1-7
I have written two macros to create a pivot table that displays data by month. I created a column that is a flag that indicates if the if an instance occurred in the last 4 months(later used as a filter). The issue is how to handle defining the last 4 months. If the current date is prior to the 16th, I want to define the last 4 months as not including the current month. If it is after the 15th, I want define the last 4 months as including the current month. Currently I have two different macros and I make the decision on which to run. I would like the macro to handle this for me. Below is the cell formula that I use to set the flag if it is in the first half of the month.
I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %
I'm trying to achieve is to write a macro that can search a column of dates then open new worksheets according to the months that are present in the column of dates. So, for example, if the column has dates ranging from January to June, I need the macro to open 6 new worksheets and label them January, February, March, April, May and June.
I have a large table of data covering 3 different companies and need to create a second table of data counting how many entries there are for each month in each company. This second table is to be used to create bar graphs and I am not using pivot tables as I cannot work out how to get the pivot table to insert months where nothing has happened. The attachment should make it clear
I have a variable list of items in date order, there could be one, more than one, or, no items for any one date Each item has it's own line. I need a formula in a separate column (column F) to total each day's items. Column A includes the Date. Column F is a cumulative total (as in Column C) for items on the same date. Column C=D+E. I am looking for a formula which will add up items in column C if they have the same date in column A, and put the answer in the last cell in column F for that date. Example........................
I have been asked to create a report that rank top sales agent for the month with the following variables:
Total # of sales-35% Total $ of sales-45% Number of calls made-20%
Given that there is more than one variable they have placed percentage weight on each. Thus my task is to cull said percentage and add their total thus getting a figure which would represent their ranking depending on how higj or low the total is.
In column A of a database I've calculated workdays for all year (excluding weekends and public holidays).
Every day, I update the numerical data of the database, with the facts of previous day.
In another sheet I've made some monthly, quarterly etc., reports for that data.
What I want, is to have:
1) a report every day, with the sum of specific data (say that in column D), for a period starting the first workday of current month and ending the previous workday of today.
What I'm basically trying to do is make a code that looks at where the "x" values are in my range and if the month matches the current month, open a pop up that says "Service Required: Equipment Name" when the file is opened.
If it's any easier, an actual date could be written instead of an x.
An alert system for service required if you will. I have attached a file.
I am trying to create a Chart from a Pivot Table. I want the Chart to show my projects and present each months amounts side-by-side comparing (Plan, Actual, Forecast) data each month for the calendar year.
I am trying to figure out how to generate the pivot table showing multiple projects so that the data is grouped by month comparing (Plan, Actual, Forecast) data. Projects are listed down the Rows while months Jan-Dec are along the columns. Each month has 3 columns because the second row contains header for the data sets of Plan, Actual & Forecast. (see sample below) When I try to create a Pivot Table, It shows multiple month fields (Jan, Jan2, Jan3, Feb, Feb2, Feb3) instead of a single month.
