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.

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

Thanks Pete_UK, hopefully I have attached it correctly.

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.