+ Reply to Thread
Results 1 to 5 of 5

Inputting data onto a Gantt chart/Calendar

  1. #1
    Registered User
    Join Date
    01-23-2020
    Location
    Essex, England
    MS-Off Ver
    16
    Posts
    5

    Inputting data onto a Gantt chart/Calendar

    Hi,

    I'm trying to get data from a table to plot automatically onto a Gantt chart/calendar in excel.

    The Gantt chart/Calendar I have already created, so no issues there.

    It's difficult to explain exactly what I need so I'll try and explain as best I can what is required.

    I work for a construction company and we have many different construction projects on the go at any one time. This requires a lot of organising. One of the aspects that needs to be organised are allocations of the staff/team members. In our company we refer to these groups of workers as "gangs". So we have several gangs working over multiple projects on different days. This is what I need to plot.

    As I say, I have created the Gantt chart already which uses conditional formatting, using the start and end date of a project as reference, to plot on the chart. This gives a nice visual representation of all our projects start/end dates and duration. I now need to add to this chart where our gangs are allocated.

    I've created a table, using the VLOOKUP function to assign gangs to each project. So if Gang 1 is on Project 1 I click the dropdown box, select project 1 and it auto populates the table. I then select the gang and it auto generates the gang reference number (G1 in this example). I then input the start and end dates of when those gangs are on site.

    As I say we can have multiple gangs on the same site so we may have a situation on the table that looks like this:

    Project 1, Gang 1, 01/01/20, 05/01/20, G1
    Project 1, Gang 2, 06/01/20, 15/01/20, G2
    Project 2, Gang 1, 06/01/20, 12/01/20, G1

    So each row on the table will have different gangs, dates and projects. However the Gantt chart will remain in order, as below:

    Project 1
    Project 2
    Project 3....and so on.

    So I need the formula in the Gantt chart to be able to look at the table as a whole rather than going row by row as the table rows/cells won't match the Gantt chart rows. I have been experimenting with the VLOOKUP function and coming up short.

    So the Gantt chart needs to look at the table, pick out gangs that are associated with the relevant project and plot the gang reference number on the Gantt chart in the correct corresponding dates. It would also be useful if it could display multiple gang references for when there are more than one gang on a project on the same day.

    Is there a formula, preferably just one, that can be used to do this?

    I came close with the below formula, however this uses both row and column values rather than searching the table as a whole. So I was able to plot some of the data in the correct location but not all.

    =IF(AND(NOT(ISBLANK('Gang Allocations'!$F5)),'Gang Allocations'!$F5<=G$20,'Gang Allocations'!$G5>=G$20,'Gang Allocations'!$A5='Start Date Schedule (2)'!$A24),'Gang Allocations'!$H5,"")

    'Gang Allocations'!$F5 is the start date of the gangs on site
    'Gang Allocations'!$G5 is the end date of the gangs on site
    G$20 is the reference to the date on the Gantt chart
    'Gang Allocations'!$A5 is the project number
    'Start Date Schedule (2)'!$A24 is the Gantt chart project number
    'Gang Allocations'!$H5 is the gang reference number

    So to simplify the above I've used a formula that's looking up the project number in the gang allocation table (cell A5) and checking it matches the project number in the Gantt chart (cell A24) and plotting the gang reference number (cell H5) into the date where the start date (cell F5) and end date (Cell G5) match the dates on the Gantt chart (cell G20).

    Not sure if that has simplified the above.

    The issue I'm having is that its trying to use row values which I don't want. The table could have data in multiple rows relating to a single project, which is where I thought the VLOOKUP function could work, however this has now gone past my knowledge of formulas.

    Unfortunately I was unable to upload photos to help explain this better.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: Inputting data onto a Gantt chart/Calendar

    There is not much point in uploading photos or screenshots, as there is little we could do with them. It would be better if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    01-23-2020
    Location
    Essex, England
    MS-Off Ver
    16
    Posts
    5

    Re: Inputting data onto a Gantt chart/Calendar

    Thanks Pete_UK, hopefully I have attached it correctly.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    Re: Inputting data onto a Gantt chart/Calendar

    Perhaps the following will help.
    On the sheet cells G21:AK23 are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that I only filled those rows for demonstration purposes and keep the file size within a reasonable range for attachment. Once you enable editing you may fill down as far as needed.
    Three conditional formatting rules are added for G1, G2 and G8. Each group is assigned a different fill color. The formula utilized as a conditional formatting rule in each case is similar to: =G21="G8"
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    01-23-2020
    Location
    Essex, England
    MS-Off Ver
    16
    Posts
    5

    Re: Inputting data onto a Gantt chart/Calendar

    Hi JeteMc,

    Thank you so much for taking the time to do that, that does exactly what I need it to do.

    I had been working on it and got as far as:
    =IF(SUMPRODUCT(('Gang Allocations'!$A$1:$A$181=$A21)*('Gang Allocations'!$F$1:$F$181<=G$20)*('Gang Allocations'!$G$1:$G$181>=G$20))>0,VLOOKUP($A21,Table1,8,FALSE),"")
    which was plotting the right job and dates but not the right gang reference.

    I've been given another criteria now...urgh. There may be the odd occasion when two or more gangs will be on site at the same time. Is there a way that it can add 2 or more (probably no more than 6 needed to be displayed at any one time) gang references at a time to the formula?

    If not I have an idea of how to get around this with the drop down boxes in the table. Essentially just remove the drop down box, type in the gangs and overwrite the gang ref with a unique one, but if the formula can do this too that would be fantastic.

    Thanks again for your help, I really appreciate it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Have the ability to view a daily Gantt chart as a monthly gantt chart
    By adam_d_john in forum Excel General
    Replies: 3
    Last Post: 03-13-2018, 09:11 PM
  2. Excel Gantt chart to Calendar view
    By kriminaal in forum Excel General
    Replies: 1
    Last Post: 01-26-2017, 03:11 PM
  3. auto fill calendar form excel Gantt chart
    By kriminaal in forum Excel General
    Replies: 5
    Last Post: 10-30-2016, 05:19 PM
  4. Merge Cells in Gantt Chart to form calendar view
    By jayjacko in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-03-2013, 09:07 AM
  5. [SOLVED] Gantt Chart: unable to set calendar month as x axis ?
    By niciwhite in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-09-2013, 03:53 AM
  6. Replies: 5
    Last Post: 10-04-2012, 07:01 AM
  7. Replies: 0
    Last Post: 07-13-2012, 06:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1