Dynamic gantt chart for hourly/daily/weekly employee schedules - how?

[destrekor]

So here's what I'm hoping to achieve:

Sheet A has the option to select which workweek, and, ideally, one of three time zones.

On Sheet B, I'll have the weekly schedules drawn up with employee name in a column, and days of the week in a row. Each each is a new timecard-like section.
Under each day, there are four columns: In, Out, In, Out - to account for lunch break. So for an employee working 9-5, it might be In: 9am; Out: 12pm; In: 12:30pm; Out: 5pm.

Each section is labeled Week # and with a Start Date and End Date.


Goal:

For Sheet A, I'd like to be able to create a Gantt chart that, on the Y axis, it would show employee names, and on the X axis the hours of the day in half-hour increments from 9am to 12am ET.

Based on which Week # is chosen, in Sheet A you'd see seven charts in a vertical list, for each day of the week. And at a glance, along the X axis, you'd see a colored bar showing all the time the employee is scheduled on the clock.


It would fill in the charts based on which week is chosen in a drop down box, and pull the week's specific data from the appropriate section on Sheet B. I'd make them named ranges, I assume, but from there I'm sort of at a loss right now.
If it could be macro free, that would be really awesome.

Even if I have to create each chart and put them in another sheet, that's fine, just so long as they can be updated dynamically if any times have to change, and I can just display the specific week requested.

I've tried to look up some good templates but all the ones I've come across are rather different than what I hope to achieve.

 

[cbrunny]

Are you using Excel?

Sounds like this could be completed with some named ranges, validation cells, vlookups, and with a few charts. This shouldn't be complicated.

Basically, you'll have a validation table that contains each of the possible combinations of schedule. You'll give each combination a named range, and then create another table that vlookups the data in the validation table based on the value in the drop down list (Data validation, allow list, where list = named range that is the validation table's weekly options). Then point a chart at that new table that has the vlookups. If done correctly, you'll have a dropdown to select week 1 or 2 or 3 or whatever, and it will automatically fill in the table that feeds a chart with the correct data.

 

[destrekor]

Yes, Excel 2013.

And while that helps a little, I'm not terribly well-versed in Excel.

But I am trying to figure it out though.

 

[cbrunny]

well, I'd start with not using the term "gantt' in your searches. I'm guessing that's throwing off results somewhat.

Just remember that you can embed a cell reference inside a formula, and you can make that cell reference a dropdown that selects one option. That's the key to making your idea work.

 

[destrekor]

Well I'm even ignoring the chart aspect for the moment. I'm trying to fill in the table that the chart will reference, but I think I'm making it more difficult than it need be.

So I've got this:

=IF(Selected_Week="Week1", IFNA(VLOOKUP("Sunday",Week1,2,FALSE),""),"")

the intent for that is to fill in the appropriate row (the Sunday row) with each column's entry from the Week1 range, in the matching column on the chart-source table.
But trying to fill in the whole row the column number won't increment as desired.
And I'll have to key that in for each workweek row, and then multiple IF statements to catch the drop-down selection.

I can't even presume to know how to get the chart filled in, but one step at a time. I'm trying to not manually enter in every single cell's intended column in vlookup.

Thoughts?

 

[destrekor]

Alright! Got the source table for the chart figured out:

=IF(VLOOKUP($A3,INDIRECT(Selected_Week),MATCH(Table[[#Headers],[Column2]],
Table[#Headers],0),FALSE)="","",VLOOKUP($A3,INDIRECT(Selected_Week),
MATCH(Table[[#Headers],[Column2]],Table[#Headers],0),FALSE))

We're on the right track now! Now it's on to the graph...