Macro To Create Gaant Chart Style Table From Leave Data?
Jun 11, 2014
I have a leave history report with data for leave taken for the 2014 year. The sheet contains the following headings:
Name; Leave Start Date; Leave End Date; Date Paid; Days Taken; Roundup; Offset
The roundup column rounds the days taken up, for example if someone took 0.4 days off it would round it to 1.The offset column just subtracts the Roundup value by 1 (=Roundup-1) I think we will need this for what I am trying to do.
I have created a new spreadsheet labelled gaant chart, this shows the name of the employee with the working dates for the year and has the following data:
Name
1/01/2014
2/01/2014
3/01/2014
6/01/2014
7/01/2014
8/01/2014
9/01/2014
10/01/2014
[code].....
What I would like for the macro to do is lookup Employee 1 from my gaant chart table and search the leave history data if it finds a relevant start date for the employee it marks that date with a 1, it then looks in the offset column and marks the number of offsets to the right. For example if employee 2 took leave from 1/1/14 to 3/1/14 the macro would mark a 1 on the 1/1/14, and search the offset column which would have 2, it will then mark a 1 in the 2 cells to the right of 1/1/14 also (these being 2/1/14 and 3/1/14)
View 1 Replies
ADVERTISEMENT
Mar 29, 2007
I have a data table and want to create six charts. In theory pivotcharts would be the way to go. However every time I refresh the charts they revert to excel's default chart and all my colour scheme and nice layouts are lost.
View 3 Replies
View Related
Jun 19, 2014
Trying to create a chart from a table of data, however my column of data has zeros throughout. How do I insert a chart with just the data that are not zeros?
View 1 Replies
View Related
Apr 14, 2009
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
View 3 Replies
View Related
Oct 11, 2006
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.
Sub ConsDiscChart()
ActiveCell.Offset(29, 11).Range("A1").Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(0, -1).Range("A1:C24").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub
View 5 Replies
View Related
Aug 26, 2009
I’m trying to do is create two tables from data evenly spaced throughout columns A to I. To make the screenshot easier to view I moved the second table under the first. The data is arranged so that each “Subject” has 42 columns and is arranged by “Type” and then by “Subject.”
The tricky part is that the number of subjects will vary so everything has to be done in some sort of loop. From the little I know about VBA I was able to record and edit a macro to add spaces every six rows and average the “MaxResponse.” (that’s what stage the screenshot was taken at) To make the table I think I need to write some sort of Dim statement but that is way beyond my comprehension.
Here is a link to a photo of the table http://yfrog.com/77excelshot3j
Excel 2003
VBA 6.5
Win XP Pro
View 9 Replies
View Related
Jul 11, 2014
How can I replace the border style with a macro across my entire data?
I would have assumed something like this would work:
[Code] .......
View 2 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
Feb 25, 2014
When I add a table in Excel, I can choose from Table Styles, different styles that set one color for even rows and another color for odd rows.
Is there a way to say to this Table Style that assign one color for rows that have the same string in column A in consecutive rows and another color when the string in colum A changes?
I mean,
If A1= XYZ, A2=XYZ assign blue to row 1 and 2.
If A3:A9=FTG assign green to rows 3 to 9.
If A10:A13=LLKF assign blue again to rows 10 to 13.
If A14:A22=WUR882 assign green again to rows 14 to 22.
and so on.
View 2 Replies
View Related
Apr 17, 2014
I have a table of company data, and I want to be able to select different companies data and create a chart automatically perhaps by pressing a 'create' button.
The table data contains three values I want to populate in the chart: red & green sales which I would like shown as a stacked bar, and red as a percentage of total sales which I would like on a different axis as a line.
I tried to create a macro but my VBA skills are almost non-existent.
View 2 Replies
View Related
Jul 2, 2009
Attached is five months of dummy sales data for eight products. My objective is to filter this data with date ranges (using Column A in Data sheet), and have the corresponding pie slices (shown in Pie Chart Sheet) be the summation of the Products grouped by their designated letter. Currently, the result I am getting is 6 pie slices for "Product A" and this is wrong. I want to see "Product A" show up only once in the LEGEND and only as one aggregated pie slice. The same holds true for the rest of the Product Letters.
I want to avoid using Pivot Tables. My objective is to keep my layout clean and simple. I would like to know if anyone out there could help me figure out a formula that will automatically catagorize all of the resulting filtered data by the Product Letter they share. Once the formula categorizes or groups together all of the resulting filtered data by Product Letter, I would like the formula to calculate a summation for each Product Letter category. After the formula finishes the summation task I would like the formula to feed these results to a Pie Chart. The resulting chart I would like to see will show individual pie slices for each Product Letter resulting from the date range filtering operation performed on Column A. The resulting letters can be anywhere from "A" to "H". My goal is to only have one individual pie slice representing each of the Product Letters resulting from a Date range filter. I thought such a task would be as easy as baking a pie, but it's turned out to be beyond my Excel skills.
View 9 Replies
View Related
Nov 28, 2006
The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....
View 9 Replies
View Related
Dec 24, 2009
What i want to do is clear some data within a merged cell but leave the rest.
I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......
View 7 Replies
View Related
Nov 21, 2006
Instead of treating cells with a blank or a text value as zero in a line graph, how can I create a gap in the line?
View 6 Replies
View Related
Oct 26, 2012
Is there any way to modify the 1st style under the Light category? I like it because it's the most simple but it won't allow me to Modify or Duplicate. Is there a work around to duplicating/copying it? I've tried manually creating a new style but I can't get it to get the lines fills correctly.
View 2 Replies
View Related
Jun 27, 2012
I am trying to create simple leave data base using Excel. Is it easy or complicated?
View 2 Replies
View Related
Mar 20, 2012
I am trying to write a simple macro to create a chart over the used range in a worksheet. The first part of the macro correctly selects the used range which in this case is A1 to F19 when I single step through the macro. A listing of the macro is shown below:
Code:
Sub AddChartObject()
'
Sheets("Sheet1").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
[Code]...
When I place the formula given in MyArea as an argument for the source data, I receive a syntax error, so obviously it is the wrong argument. Numerous attempts to correct it also failed. As a sanity check, I placed the used range in an argument for the source data and was able to create the expected chart. fix the syntax error in the macro
View 4 Replies
View Related
Feb 12, 2014
I have 6 columns. The 5th and 6th columns are the most important. The 5th contains a figure and the 6th contains a type.
Bascially I am trying to do a basic chart showing the following 2 things. A. Do a percentage chart of column 6 (service) but not of percentage of hoem many lines the product is in. Not based on percentage of how many times Apples are shown compared to other products. I need it to add up everything, then do a percentage of total amounts of oranges and total of for Apples from the figures on column 5. Also, if possible, the pie chart will even show teh actual total figure per product in each (or next to) each pie chart slice.
know the easiest way to do this (step by step) in Excel 2010 and then I can perhaps write a macro for the future?
View 1 Replies
View Related
Jan 24, 2012
I'm trying to create a macro to add a fill effect on the bars of my chart.
However, I don't want it to change the bar's current color. Just to do the effect on the current color.
Code:
Sub fill()
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
[code].....
View 2 Replies
View Related
Jan 31, 2014
I have a data set where the rows contain monthly sales data (from January to December) and the columns contain the names of employees (joe, bob, sue, etc.) and a single "group average" column:
I would like to create a chart for each employee showing the employee's monthly sales vs. group average. For example, the first chart would be for joe...
Then the next chart will be for bob and so forth...
View 3 Replies
View Related
Nov 18, 2008
I am trying to have a list box that when clicked will change the chart data to whatever is selected. There are 2 options you can choose from. Order Cost (Net) and Order Total (Gross) The problem I'm running into is that the data is in 2 different rows. How can I create the drop down box to display the one that is selected? ...
View 9 Replies
View Related
Sep 1, 2012
Trying to create a macro to run through the following steps when I select a chart and run the macro:
Paste>As Picture>Copy As Picture>As Shown When Printed>OK
I used the macro record feature and when I enter the shortcut it always returns the same chart. How do I get it to run on whatever chart I have selected instead?
I am constantly using this function to copy charts from Excel into PowerPoint presentations and am frustrated with all the extra steps it takes (versus just Ctrl-C).
View 9 Replies
View Related
May 28, 2008
I want to write a macro to add a surface chart for a Data Range which includes 6000 data points. But the series selected for the chart don't cover all Data Ranges, only part of them.
Public Sub AddChart2(LastRow As Integer, LastCol As Integer)
Dim cht As ChartObject, currentSheet As Worksheet
Dim rng As Range, newRow As Range, srcRange As Range
Dim colIdx As Integer
colIdx = 5
View 7 Replies
View Related
May 28, 2013
I want to create the chart below. I have data that is coded with a binary flag (0 = red, 1 = blue) that is also ordered. For example, the data I would use to hypothetically create the left hand column below looks like this
Binary Flag
Rank
0
1
0
2
1
3
0
4
1
5
1
6
View 1 Replies
View Related
Oct 17, 2008
Here is my scenario, I have the following data:
Data Series 1
a=3
b=3
c=4
Tot 10
Data Series 2
d=2
e=2
f=2
g=1
h=1
i=1
j=1
Total 10
Data series 3
k=4 (d+e)
l=3 (f+g)
m=2 (h+i)
n=1 (j)
I want the three stack bars together, for the series 2 & 3 I want them without any gap so I can see how data series 3 is created.
I can't find a way to create a three stack bars chart that don't have the same number of data series, I don't want percentages.
View 9 Replies
View Related
Jun 10, 2013
I'm a fan of the Excel 2010 table styles, but can't figure one thing out. When I create a table in excel (Ctrl+T), I like to reformat it with a new defined style. When I save the style, I can use it while I'm in that instance of excel, but when I close and reopen excel, the style is gone. How to save the custom formats to they are always available?
View 3 Replies
View Related
Apr 8, 2008
I am looking to create a number of series on a chart (xyscattersmooth) from a datatable. Below is my data Table....
View 9 Replies
View Related
May 20, 2013
I need drop-down combo box to change a graph based on the option selected in the drop-down
Challenge: my Combo box resides on the First sheet only, but the items that populate in the drop-down list are dynamic and change depending on how many sheets exist for this document. The options in the list are Overall, Week 1, Week 2, ... Week N. Overall needs to sum up the data (or I can pull just from the summary page if I am lazy) and the Week N corresponds to a specific sheet added.
I currently am able to get my drop down to populate items correctly, and my macros to create the sheets dynamically with templates just fine (not bad for newbie).
So what I need to figure out is how to tie my drop down to make a scatter plot/bar graph based on the selected option and action. I can't find an attachment option here but here is some code:
Sheet 1 Code:
Code:
Private Sub Worksheet_Activate()
Me.ComboBox1.Clear
Me.ComboBox1.AddItem "Overall"
[Code].....
View 2 Replies
View Related
Feb 27, 2008
I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
View 5 Replies
View Related
Jun 3, 2014
Can record the all those actions which we need to do on a regular basis. In my work i have to create the pivot table to a data which gets changed every day but the data sequence remains the same.
I have tried to record the macro for the pivot table but when i tried to run the macro again then the below error message comes.
"run time error 5" "Invalid procedure call or argument" when i click on debug then the below macro program is highlighted in yellow color.
View 1 Replies
View Related
