Gantt Style Schedule

**a-w** · 03-20-2011, 05:55 AM

Hi Guys,

Hoping someone can help as I have no idea where to start with this one...

Each day, I get a file from one of our systems that lists each of our consultants along with their upcoming work assignments. It looks similar to the "data" tab in the attached example.

What I would like to do, it display this data in a more intuitive fashion so we can easily see where a consultant will be and what assignment they are working on. I have mocked up my desired layout on the "schedule" tab but I have no idea how to go about automating updates to this sheet.

Ideally, I would just copy the new data I receive each day to the "data" section and my schedule would automatically update.

Appreciate any assistance.

Thanks,

a-w

**Andy Pope** · 03-20-2011, 08:27 AM

In B2 use the formula

=IFERROR(INDEX(LOC_ASSIGNS,SUMPRODUCT((CONSULTANTS=$A9)*(STARTDATES<=B$1)*(ENDDATES>=B$1)*ROW(CONSULTANTS))-1,1),"")

I have defined some dynamic ranges.

CONSULTANTS: =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A)-1,1)
STARTDATES: =OFFSET(CONSULTANTS,0,3)
ENDDATES: =OFFSET(CONSULTANTS,0,4)
LOC_ASSIGNS: =OFFSET(CONSULTANTS,0,5)

On the data sheet is an new columns to build Loc/Assign #s
Data!F2: =B2 & "/" & C2

**a-w** · 03-20-2011, 07:41 PM

Thanks Andy - this is great!

Any chance you could help to expand the functionality to detect conflicts? For example, if a consultant is scheduled on two engagements on the same day, it will display something like "conflict".

**MarvinP** · 03-20-2011, 08:04 PM

Hi a-w,

I was reading a site today that may help as it looks similar to what you have at:
http://www.bettersolutions.com/excel...N923316332.htm

I was wondering if you could have each Location a different color format to allow easy viewing of information.

**Andy Pope** · 03-21-2011, 04:36 AM

You can use a revision of the formula to report days.

In the example file I have added a grid below the table so you can see the day count.

The same formula has then been used to via conditional formatting to colour cells green or red.

=SUMPRODUCT((CONSULTANTS=$A14)*(STARTDATES<=B$1)*(ENDDATES>=B$1)*1)

I added a overlapping record to the data table.