Automate Gantt Chart Creation
Nov 7, 2006
I am trying to find a way to automate this crude gant chart. What I am trying to do is the following:
1: By entering a start date and due date, and by adding a percentage to 4 different goals, create a sort of gant chart for each job.
2: Have this chart update as the start and due dates change.
3: Be able to past other gant charts below this one, based on the leader assigned to each job. ie. if Dave is a leader, create a worksheet that will have all of dave's Gant charts in one workbook, on one worksheet, pasted each one below the next. The goal being we can give each leader 1 sheet that has all their jobs.
View 6 Replies
ADVERTISEMENT
Mar 29, 2013
Trying to automate footer creation, center header sets ok then errors out on left footer with
Runtime error 1004 : Unable to set LeftFooter property of the PageSetup Class
if I comment out the left footer line the center sets ok then stalls with the same error on the right footer
VB:
For Each WS In Worksheets
With WS.PageSetup
.CenterHeader = "&""Calibri""&22 &B" & msg9 & vbLf & "&14 &B" & msg1
.LeftFooter = "&""Calibri""&11" & msg2 & " " & msg3 & vbLf & "Owner: " & msg8
.CenterFooter = "&""Calibri""&11" & msg4 & vbLf & "&11 &B &K4a0c80" & msg5
.RightFooter = "&""Calibri""&11" & msg6 & vbLf & msg7
End With
Next
View 5 Replies
View Related
Feb 6, 2014
I need to create a lot of web queries to extract data from several webpages. The web pages all have the same url except the last string of numbers, e.g. ww.somewhere.com/lookup.html?type=[x] where [x] is just one or more integers that are in sequential order.
I want to create around 50 web queries (maybe more later) without doing this manually. Is there any way to put these urls in a column and have excel read through them and query the data?
View 10 Replies
View Related
Jan 2, 2009
Let's suppose that I have a serie of activities. All activities are done in an interval of time and there is not overlapping.
View 14 Replies
View Related
Jul 8, 2014
Gantt chart to calculate holidays, it now only calculation weekends only, file can be downloaded from here.
View 2 Replies
View Related
Dec 15, 2008
The below is a sample Gantt chart that I'm using, it's working fine, what I want to do is when the STATUS is completed (Column H) goes from a cross to a tick (I'm using the format of Wingdings 2 - cross is O and tick is P) is to change the Gantt chart colour from it's current colour of green to say orange. In conditional formatting (in cell I3) I have, formula is =AND(I$2>=$F3,I$2
View 9 Replies
View Related
Oct 25, 2009
Hi guys, I would like to be able to automatically gantt chart the following data table, my problem is that i am unaware of how to create the gantt chart when there are several entries for the one location - the table is a booking sheet and the gantt chart is just a graphical display of the activity occuring in each bay - if any could help me achieve the following gantt chart from the the following table it would be much appreciated: ...
View 9 Replies
View Related
Jul 1, 2008
I tried so long I finally gave up and drew this by hand in MS Word:
But I wonder if you guys could help me chart this automatical in Excel.
I want to chart the time period a ship (Vela, Serpentine, Rainbow etc) are contracted. I want a specific color for the firm contract, and another for the remaining optional contract. If a ship is under construction I also want a bar showing when it's done.
Each ship has a rate. I want this written on the bar. In "Vela's" case there are to rates for the same contract: The first 740 days the rate is 24,5 the rest of the period it is 29. I also would like to be able plot an additional contract for the same ship for example after "Syrena" is done in yr 2009.
View 9 Replies
View Related
Jan 22, 2014
Someone else built a this timeline using a date calculation that I am unable to modify. Fix date formula in row 7 to show FY14-FY18 and in row 7 show the quarters 1-4 along the years.
View 3 Replies
View Related
Dec 14, 2009
Hey Guys- I'm not sure if this is even possible but I need some help. I have attached a file below similar to a gantt chart. I need help with the conditional formatting, as I have manually changed the colors to match what I need automated.
Basically I have 6 tasks and I need to change dates for individual projects but I also need the corresponding color to also change.
View 9 Replies
View Related
May 28, 2014
I am developing a Excel gantt chat i have chosen to display the bars by the lenght of the cell because the data is also most real time so as the time reduces so will the bars however the problem comes when there is tasks being done under one above it as i cant have the cells with different widths
What would be the best way to get around it as i want to keep the almost and the appearance of the real time nature of the cell width equal to the value.
View 2 Replies
View Related
Sep 26, 2013
I have created a pseudo Gantt chart in Excel (a horizontal stacked bar chart). The chart is driven off data I have in a Power Pivot model (Auto Refreshed) and the Gantt chart displays data which spans a fixed 12 hour time frame.
The problem I am having is that in order for me to get the chart to display correctly I must manually (daily) get the excel decimal values for my Minimum DateTime and Maximum DateTime and then enter those numbers into the "AXIS Options" Bounds Minimum and Maximum value fields. I would like to automate that process so that the Axis Bounds are either driven from the data cells OR are always set to a specific 12 hour window 6pm->6am daily.
View 1 Replies
View Related
Sep 16, 2008
I am looking to create a spreadheet that will automatically shade blocks in a gannt chart according to a list of dates.
I.e B13 & B14 will have the start and end date of the particular task or activity (this will be on another sheet)
Row A3 - S3 will contain dates, iusing a lookup formulas excel will look up the activity name (or number) and look for the relevant date, if the date listed in row 3 is within or equal to that date it will return yes, if not a no.
this seemed straightforad, but I cant think of a consistent formula that will do this, without having to keep changing it ...
View 19 Replies
View Related
Feb 14, 2007
I'm trying to create an Employee Scheduler. I want my employees (1 technician per row for a total of 10 rows) in the 1st column then the monthly calendar to the right (1 cell/day). That is what you'll see when you open the worksheet. Then, down below I set up my Data Area where I input my project, start date, end date, technician (where each tech is assigned a unique number), and other data across a row of cells. This could be infinite (or at least up to the very last row).
I modified a free Project Scheduler I got from XL-EasyGantt to become my Employee Scheduler but am having some difficulties getting it to do what I want. The project Scheduler basically works by entering (on the same row) the project (or task), the start date and finish date and then the cells get automatically filled in across those dates. This allows for overlapping of tasks but I want to create an Employee Scheduler so it doesn't allow overlapping or schedule a technician to start a new project before he/she has completed the one their on.
The main function which is in each cell of the monthly calendar is as follows: =IF(OR(AND($G28>=K$4,$G28<L$4),AND($M28>=K$4,K$4>=$G28)),IF(AND($E11="x",$M28>=K$4,$M28<L$4),"x",IF($E11=".",".","..")),"")
My problem: I want to be able to look at the Main Schedule and see when each Technician is scheduled for a job (or multiple jobs) for any given month. I want the function to look in my Data Area and assign the start date and end date of a project and the corresponding technician number and fill in the date cells across from their name in the Schedule above.
View 6 Replies
View Related
Mar 19, 2012
I'm attempting to chart data obtained from our phone system to see when users are logged in/on rest.
I'm using Excel 2007.
The data we extract has:
person | status | start | finish | duration
Status is either "login" or "rest"
Start/finish is time (h:mm)
Duration is a formula (h:mm, finish - start)
I want to have a stacked bar chart that shows when a person was logged in/on rest; one bar for each person.
x-axis: person
y-axis: time (8am - 6pm)
legend: status
The problem I'm encountering is that each person logs in and out multiple times throughout the day (therefore, has multiple lines to their name in the data), and I can't get this information to appear on a single bar for each person.
I've sorted the data into a pivot table which gives me the information I want, but how to get the chart.
Row labels
a. Person
b. status
c. start
Values
Min of duration
Ideally, I would like to avoid using a Gantt chart within cells (as it would involve vlookup/if statements), but am beginning to think that's my only option...
View 3 Replies
View Related
Jun 16, 2014
I am looking for the best way to use conditional formatting in a Gantt chart. I want the cells to turn blue if they fall between two dates.
View 4 Replies
View Related
Dec 5, 2006
I have a list of task owners that are assigned tasks. The tasks are mapped out in Gantt chart form with time shown by an "X".
what I want to do is to colour the cells "X" with the associated cell colour of the task owner and should the owner change, the cell colour changes.
View 5 Replies
View Related
May 22, 2014
I'm using a stacked bar chart (in Excel 2010, running on Windows 7) to create a simple Gantt-type chart - with just four or five bars. I've got my chart looking most of the way I want it to, but one thing still eludes me: I'd like to set up the major axis ticks to be quarters of the year (from 1/1/2011 to 4/1/2013). Since quarters are not regular intervals (they are not exactly every 90 days), I can't do this using Excel's standard functionality for choosing axis tick marks.
View 3 Replies
View Related
Aug 12, 2014
I'm trying to create a simplified Gantt chart of sorts, and cannot figure out one piece of it. I'm not sure how to write out exactly what I need, but here goes.
I would like to have a formula that looks at the percent completed (which the user inputs) and multiplies it by the total duration for the task to give a total number of days completed. Then, under the corresponding dates, the color of the cell would change. I've attached a simplified version of what I'm talking about.
On the attached spreadsheet, the total duration (D2) is 5 days and the percent completed (C2) is 40%. This calculates to 2 days. With that said, I would like the cells under the first 2 days under the listed dates (E2 and F2) to change color. When the percentage complete reaches 60%, then G2 would change color, when it reaches 80%, then H2 would change, and when it reaches 100%, then I2 would change.
My guess is that the formula will need to result in a particular value, and then I'll use Conditional Formatting to do the actual changing of the cell color. Assuming this is correct, I still don't know what formula to use to accomplish this.
View 7 Replies
View Related
Jun 30, 2014
I need to create 69 charts with 4 different series in every chart displaying information like average stock growth and so on. Is there any smarter way i can do that than manually when i know that the range of these charts will be consistent?
View 14 Replies
View Related
Apr 15, 2007
I’m trying to take an existing bubble chart, paste new data into the sheet,
then march sequentially from row to row, adding specific cells within each as a new series in the chart until I run out of rows. Below is my latest attempt (not working of course).
Sub setseries1()
Range("A1").Select 'select upper left cell to start
Do Until ActiveCell.Value = "" 'stop when you run out of rows
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlBubble
With ActiveChart.SeriesCollection.NewSeries
'use these values for the next series
.Name = ActiveCell.Offset(1, 0).Select
.BubbleSizes = ActiveCell.Offset(0, 1).Select
.Values = ActiveCell.Offset(0, 1).Select
.XValues = ActiveCell.Offset(0, 9).Select
End With
'go to next row, and repeat
ActiveCell = ActiveCell.Offset(0, -1)
Loop
End Sub
View 7 Replies
View Related
Aug 30, 2007
I'm trying to change the source data for a worksheet for a series of charts. The source data appears in a series of tables. There are about 30 such tables and associated charts on the worksheet. (I need to duplicate this many times over, which is why I need to automate.). The chart names seem to increment by 2 (i.e. Chart 1, Chart 3, Chart 5) And the source data for the charts increment by 9: BM95:BP102, BM104:BP111, etc. How can I automate this? Also, I'm trying to automate changing the reference in the title of the chart. This reference increments rows by 9. When I use the macro recorder to do this, I get the following
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Range("BM95:BP102")
I'm unable to generate any code that allows me to change the reference of the chart title.
View 4 Replies
View Related
Feb 5, 2008
I am trying to automate grabbing the chart on this website, using Excel VB. Here is the URL: http://www2.barchart.com/sectors.asp?base=industry
View 2 Replies
View Related
Jul 9, 2008
Based on a Beginning Date Value and an Ending Date Value - I would like to color a range of Cells - the month (dates) are in the header row.
For example: Beginning Date = 01/01/09
Ending Date = 06/01/09
I want the range of cells in the same row colored orange under Jan '09 to June '09.
View 8 Replies
View Related
Jan 16, 2010
As part of my work i am starting to create a two weekly programme in teh form of a gantt chart. now i have had a little google and seen plenty of spreadsheets available but they all seem to be not in a range of dates i would like. Ans plus they seem to be read only and thus i cant add a column on the end i would like for displaying resources used for the task.
Now i am sure if i searched hard enough i could find this answer but I have another question.
Whereas all the one i have seen involve typing the date and it just displays the bar.
Is there a way I can make it so that i can just click on a cell and that cell becaomes highlighted. so for example of o want at ask to happen on a wednesday i click the cell in that row and t automatically fills. and if i click it again it dissapears?
View 6 Replies
View Related
Jan 18, 2007
I'm trying to create a chart that will display dates along the vertical (y) axis and time (on a 24-hour timeline) on the horizontal (x) axis. Ideally, I'd like the chart to show the various time entries on each date, perhaps represented by a point or other mark at the appropriate intervals corresponding to the time entries for each date. Failing that, a Gantt-style chart that shows a span, represented by a bar beginning at the earliest time and extending to the latest time entry for each date would be useful....
View 9 Replies
View Related
Sep 3, 2004
I've created a Gantt chart using the tip #58. Now how can I count all the cells that are colored?
View 9 Replies
View Related
Jul 19, 2007
I am wanting to make a macro into an add-in. An add-in is basically a workbook with macros that is opened when excel is correct? I know how to create an additional menu item under an existing one, but I'm wanting to create my own menu with this add-in item under it. I've used this code to try and create the new menu and placed it in the workbook open:
HelpIndex = CommandBars(1).Controls("Help").Index
Set NewMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, Before:=HelpIndex, Temporary:=True)
NewMenu.Caption = "&Separate Cashiers"
But ran into an error on the first line of code. I saved the workbook with the add-in macro in it as an .xla and used the addins under tools to have it open each time.
View 8 Replies
View Related
Feb 23, 2010
I want to create a pop up message showing count of cells turned red,each time the workbooked are opened.like column "I" has two dates in red colour,so the pop up will say "you have 2 contracts expired"
View 8 Replies
View Related
Nov 12, 2008
is it possible to write a macro that will create a folder labled "2009" then a subfolder for each month with four subfolders labled "Week 1" through "Week 4", and have the "March", "June", "September", and "December" folders contain subfolders labled "Week 1" through "Week 5"?
View 2 Replies
View Related
