Personnel Tracker

Good evening,

I'm looking for some help as someone with a relatively basic working knowledge of excel.

I have been tasked with producing a weekly commitments tracker for 2017 for personnel by my boss and have created a basic template that he has approved (see attached file - the actual tracker will have many more categories of task with the numbers of personnel feeding in from another sheet. This is already set-up and is within my knowledge.).

What I want to do now, is present this data graphically (which I can do easily) but using dynamic graphs (stacked and 100% stacked graphs by choice) that will show 4 weeks ahead of the current date based on the with each individual task and then a separate graph showing 12 weeks ahead with pure committed vs uncommitted personnel.

I'm looking for two things if anyone is able to help:

1) Any pointers to guides that might be able to help me achieve this
2) A worked example of how I can do this - once I understand the basics I will adjust to fit my actual tracker.

I've been looking a pivot tables and slicers as a way to do it but don't have the indepth knowledge to know exactly what is possible.

Thank you in advance for any help!

D

Hi there,

When faced with the job of moving a graphical window across a data range, I always find it easier to "point" the graph at a fixed range and then link the required data subset to that range.

The attached workbook contains three worksheets: All Data, Graph Data - Four-week, and Graph Data - Twelve-week.

As the names suggest, the "All Data" worksheet contains the data for the entire period under consideration, while the other worksheets contain four-column and twelve-column ranges, each of which is linked to its own graph.

Data in the above ranges are linked to the appropriate ranges in the "All Data" worksheet by means of HLOOKUP functions. The appropriate offset from the first column (week) of data is determined by the value in Cell A2 on each "Graph Data" worksheet. Each of the graphs is provided with "Previous" and "Next" buttons so that data for the period of interest can be selected - these buttons alter the value contained in Cell A2.

Hope the above and the attached workbook get you moving in the right direction - please let me know how you get on.

Regards,

Greg M