Generate A Monthly Job Chart
Oct 7, 2006
I want to have my monthly job chart automated. I got a leave chart marked with workers' leaves on a month basis. Each month, I got to consolidate a job roster containing names of workers who will be working at the counter. It takes me almost 2-3 hours to have the roster completed. I attached here with an excel file for your information. Can it be done automatically by vba if data of workers' leaves are available? The names assigned will be at random in seqence each month(i.e. Peter will be at counter work on 3/10/2006 if not on leave but may be on 08/11/2006). That's why it takes me so long to compile the roster.
View 9 Replies
ADVERTISEMENT
Feb 2, 2007
I am working in a Lotus notes application that enables me to add an Excel report and chart. I have no problem adding a report using their template creater. however it says for me to add a chart I have to create an excel template that must contain the vbscript needed to generate a chart.
I tried using the macro editor to create the report but i get an error "Microsoft Excel Select method of range failed".
View 3 Replies
View Related
Nov 19, 2006
I am trying to write a macro that will automatically change the source data for a graph. This is an existing graph that is on tab GraphYTD and the data for the graph comes from DataYTD. The number of rows of data will change monthly, but I would like to use the same graph template every month. Here is my
Sub SortYTD()
Dim myBottom As Long
myBottom = Sheets("DataYTD"). Range("B65536").End(xlUp).Row
ActiveChart.SetSourceData Source:=Sheets("DataYTD").Range("F2:I" & myBottom)
ActiveChart.Location Where:=xlLocationAsObject, Name:="GraphYTD"
End Sub
I am getting Runtime error 91 - object variable or with block variable not set.
View 3 Replies
View Related
Mar 6, 2013
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
View 1 Replies
View Related
Aug 3, 2009
I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
View 9 Replies
View Related
Apr 28, 2009
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies
View Related
Jul 12, 2013
How do I paste the first chart into the second chart but maintain the format of the second chart?
View 2 Replies
View Related
Jun 10, 2009
I have a two column A and B at Sheet1. Col A is a Date wise column and Col B is of the Values. I need formula for getting sum monthwise in sheet2. i.e the sum of jan, feb, mar etc
View 9 Replies
View Related
Jan 14, 2014
I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
View 5 Replies
View Related
Feb 24, 2007
I know this has been discussed a number of times, but here is my problem
I have three charts in my workbook. I want to attach a macro so that when the chart is clicked it returns to Sheet - Home. I have using the following:
worksheets("Home").activate. But after I protect each chart and the workbook, and save and exit. When I reload the Workbook it has forgotten the assigned macros and nothing happens.
View 7 Replies
View Related
Nov 27, 2011
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
View 1 Replies
View Related
Dec 29, 2011
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
View 1 Replies
View Related
Jul 14, 2012
I have created a population pyramid and want to add another chart on the secondary axis which I can do. The part which I seem not to be able to complete is getting the line chart secondary axis to read from the primary y axis. It seems to add a new axis, even when I delete this the line chart does not read correctly.
View 1 Replies
View Related
Apr 18, 2013
How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
View 1 Replies
View Related
Mar 26, 2009
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
View 4 Replies
View Related
Jun 30, 2009
I would like combine (overlay) a bubble chart with a connected point scatter chart. I understand that, without VBA, this is not possible. However, I understand that, by using VBA, the markers of a scatter chart can be configured as circles with their size proportional to values in a specified column. This pseudo-bubble chart can then easily be combined with a connected point scatter chart.
My question is: does anyone have any VBA code to share that shows how to configure a scatter chart as the type of pseudo-bubble chart described above.
View 2 Replies
View Related
May 6, 2008
I have the following situation. I work off-site most of the time in another office. I would like to have a macro on my main office PC to run on a monthly basis at a set time.
For instance on the 15th of every month at 9:00 am the macro would go into a shared drive and pull an updated copy of a report, redo the report and send it out to various offices. I have VB code that performs all the tasks except for setting a timer to tell it too run on the 15th of every month at 9:00 am.
View 9 Replies
View Related
Sep 20, 2009
I wish to create a Vlookup & summing qty by month.
In sheet1 is date
Column A = Date
Column B = Code
Column C = Qty
In Sheet2 I wish to create Vlookup.
I have the code in Column A4 going down AND wish to create Vlookup in column C4 for month Apr-09(c3) display total qty.
In sort look up code - for the month and sum qty .
code Apr-09 May-09 Jun-09 etc
SW L 60 15 55
SW M 10 35 45
DD L 30 30 25.
View 9 Replies
View Related
Feb 15, 2012
I am trying to overlay a line chart on top of a stacked column chart. The stacked column chart is a chart where the x axis is dates and the y axis is amount. The line chart is a projected amount that I will reach in few years; the x axis is the date while the y axis is an amount. I can get a chart that has a line and stacked columns, but ever time, the dates get messed up. The line has an extremely wide date range while the columns only have about a month of data.
View 9 Replies
View Related
Jan 22, 2008
Is there a way to display only the current month and the past 11 months in a Line Chart in Excel? So if I was to print Jan 2008 Excel graph, it would only display Feb 2007 - Jan 2008 data charting.
The spreadsheet of the data contains data for Jan 2005 - Jan 2008 so far, but only the current month with the past 11 months should display in the line chart depending on what is the current month.
View 13 Replies
View Related
Jan 7, 2014
I have a 12 month budget spreedsheet. Some expenses' are fixed every month. I would like those cells to be automatically filled with that fixed amount. Say on the 5th of every month a particular cell would have $50 automatically entered so i don't have to do it manually.
View 2 Replies
View Related
Jan 27, 2014
I am trying to convert quarterly data to monthly
The quarterly data columns go: Mar-14, Jun-14 etc
The monthly data columns goes Jan-14, Feb-14 etc
I am trying to use a vlookup and match formula but as you will see in the attached file it is only working for those months that are labelled in both data e.g. March 14, June 14
Is there a formula that will pick up for example that January and February numbers should be drawn from the March 14 quarter, April and May from June quarter etc?
View 2 Replies
View Related
Jan 31, 2014
I have a table which has the following columns:
Date - Data1 - Data2 - OtherData1 - OtherData2
I came up with some formulas to count my data monthly. I have 12 tables with this kind of formula in it:
[Code] ....
Where B12 is the year and A213 is my month number. My first try on the "date filter" looked like that:
[Code] .........
And it wasn't working so I thought it was because the 31 wasn't a good idea for non-31-days-months but none of the formulas above are working.
(BTW, IDK why it's not working but I have data in my table for months 10, 11 and 12 and the only calculation tables that are calculating data are the ones for months 9 and 10. The results are the same in these two tables and are counting all my Table1[Data1] and [Data2] (the count is not monthly))
View 7 Replies
View Related
Aug 14, 2014
Find the attached excel sheet.
I have, every month data like for one Year, every day in a month we will update the sales information in to excel sheets like jan, feb, march, ...December
In the Main sheet i want total, i have bunch of data and wide range of items, how to get monthly consumption into main sheet.
excel.xls.xlsx‎
View 1 Replies
View Related
Apr 28, 2007
I have a list of dates with respective balances. I am wondering how to get Excel to determine if the end of the month has passed after a certain date and then calculate the return for that month. What is the most efficient way in your opinion?
Here is a small data sample:
27/12/2005 06:23 0.3%
27/12/2005 05:47 -0.6%
29/12/2005 06:53 1.3%
04/01/2006 17:55 -0.1%
09/01/2006 15:35 3.99%
09/01/2006 15:46 2.54%
09/01/2006 17:07 1.8%
12/01/2006 07:12 -2%
12/01/2006 13:37 1.5%
12/01/2006 13:39 0.8%
12/01/2006 13:58 0.01%
View 14 Replies
View Related
May 20, 2009
I need to create a running monthly total formula for a worksheet. This is something i need in the data, so i can manipulate it without using a pivot table.
I think I would use the SUM, MONTH, and IF formulas, and maybe EOMONTH.
I guess where I'm stuck is figuring out how to make conditions for my SUM formula. So as the dates go down the page, I would like a running total in a column to the right, that will also restart with every new month.
Attached is my example.
View 6 Replies
View Related
Sep 10, 2013
How to translate dates which i already have in the US format, to the UK format.
i.e. mm/dd/yy becomes dd/mm/yy
View 3 Replies
View Related
May 21, 2014
where to start with automating a monthly report that I put together.
My report is generated from two or three tables of data - roughly 25,000 records.
Currently, I copy and paste this table of data into one worksheet, and then have pivot tables that are written to take data from this table (this is a typical marketing sales funnel - calls, raw leads, qualified leads, customers, revenue). Then I have another worksheet that is the display to the client, with the proper formatting, mathematical calculations, totals etc. On this client facing worksheet, I pull data from the pivot table (using =GETPIVOTDATA formulas).
It seems that I have constructed this report in a very inefficient way because I have a ton of manual work to do every month to make the client view presentable as changes come about in the raw data. For instance I segregate the client facing report by lead sources, but when new lead sources get added in month by month, I have to edit the client facing report at length.
where to start, or what steps to make this more automated
View 1 Replies
View Related
Apr 7, 2014
How to write a macro that will sum daily figures into monthly figures? On the attached spreadsheet I would like to take the daily figures on the amounts on the VRU DAILY worksheet and sum them in the appropriate month on the VRU # sheet.
View 14 Replies
View Related
Jul 14, 2008
I want to do is set up a macro to autofill into a new column every month. My data is linked to another excel document so that is where it is pulling the data from. Currently I have data in columns monthly from Dec 2001 until June 2008. I just want a macro that will automatically add in the next month, so in this case July then August the following month ect.
View 10 Replies
View Related
