Creating Stacked Bargraphs to display Shift Times

Hi guys
I was wanting to know if anyone could help me create some bar graphs...

I have attached the sample document I am currently working with. I would like to make it so that it is one graph/day on seperate sheets... It's a bit difficult (to me anyway) because of the Big Black cell's (e.g. "Management") but unfortunately they have to stay.

If possible I would like to make it so that only those that are working appear on the graph automatically. I know its a lot to ask, but with 20 employees and not everyone working each day, it would make things a lot easier.

Any help you can give I will really appreciate it!

Thanks in advance!
Regan

Hi,

look what you can do with the attached.

A few notes: Avoid merging cells. They lead to all kinds of trouble with formulas using ranges that cross merged cells, and more. Use "Center across Selection" instead.
I unmerged the divider rows between departments. Also, look at the new definition of the weekday in the roster sheet. I changed Monday and Tuesday. Compare with the other days and see what you prefer, i.e. manually editing every weekday each week, or just entering one date and seeing the other dates updated automatically.

There is a Chart tab in your workbok now. Select a weekday from the dropdown box above the chart. The chart will change to show just the people on roster that day. Due to that dynamic nature, the bars will be fatter or thinner, depending on how many people are shown, because the chart area does not change in size, so the bars are fitted into the available space. If you have roughly the same number of people working every day, that should'nt really be a big issue.

Now that chart does not just appear by magic.

The calculations for sorting and filtering out the staff on duty happens on Sheet1. When you change the names in the roster, they will automatically update in that sheet. As long as you only change names, you should be fine, but if you need to add names to the roster below row 30, you will have to reflect these changes by also adding rows (and copy formulas) in the respective areas of the prep sheet.

Everything needed to create charts like these (and more) can be found on www.peltiertech.com

I saved in XL 2003 format, coz that's the machine I'm currently on. But everything should work just as well when saved back as .xlsx.

hth