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.
Bookmarks